Visualising the join

Throughout my life I always found it much more simple to remember ideas if I could represent them as some kind of visual entity. For a long time the varied world of SQL joins never quite sat perfectly in my mind until I found the below useful article:

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Admittedly it there is still the slightly awkward case of the Cartesian join which doesn't quite lend itself to the Venn diagram construct, however, I am happy to leave that unique character in a corner on it's own for now. Since it took me a while to figure out why I might ever use one... until I found this rather interesting content on Stack Overflow:

"If you have a "grid" that you want to populate completely, like size and color information for a particular article of clothing:

select
   size,
   color
from
   sizes CROSS JOIN colors

Maybe you want a table that contains a row for every minute in the day, and you want to use it to verify that a procedure has executed each minute, so you might cross three tables:

select
   hour,
   minute,
   second
from
   hours CROSS JOIN minutes CROSS JOIN seconds

Or you have a set of standard report specs that you want to apply to every month in the year:

select
   specId,
   month
from
   reports CROSS JOIN months

The problem with maintaining these as views is that in most cases, you don't want a complete product, particularly with respect to clothes. You can add MINUS logic to the query to remove certain combinations that you don't carry, but you might find it easier to populate a table some other way and not use a Cartesian product.

Also, you might end up trying the cross join on tables that have perhaps a few more rows than you thought, or perhaps your WHERE clause was partially or completely missing. In that case, your DBA will notify you promptly of the omission. Usually he or she will not be happy."

To be honest though I am still not carried away by the concept of the Cartesian/Cross join as there often seem much better and more elegant solutions.