A Visual Explanation of SQL Joins (2007)

I think it's best just to read what a cartesian product is, then consider that all joins are just simple filter operations after you've already made a cartesian product.<p>(although modern databases may do something more complicated, I'm not sure)

sigh... this is totally wrong... as Jeff himself kinda realized with his comment "There's also a cartesian product or cross join, which as far as I can tell, can't be expressed as a Venn diagram"<p>SQL does have set operations, and the Venn diagram treatment is much less terrible [though still not quite accurate] at explaining SQL set semantics.<p>The biggest flaw with the Venn diagram thing is that it doesn't capture the productive aspect of joins.<p>A inner join B on (some-condition) will produce ALL combinations of A records pasted to B records where some-condition holds.<p>The Venn diagram approach can't distinguish between a single A-pasted-to-B record and 100 thousand combinations of A-pasted-to-different-Bs. All it can say is that the resulting combinations consist of some set of A and B records.<p>Venn diagrams don't accurately describe the records resulting from a join at all. At best, they rule out records that can't occur.<p>So they're not so great as an explanatory device.

I made something a while back that compares lists/tables and visually shows results:<p><a href="http://skratchdot.github.io/list-tool/" rel="nofollow">http://skratchdot.github.io/list-tool/</a><p>Some of the algorithms aren't optimized (as to keep results in the order they were processed).<p>It was more of an experiment to play with web workers and responsive design.

I liked the article a lot. For more detail its worth looking at:<p>Join Processing in Relational Databases, Priti Mishra and Margaret H. Eich, ACM Computing Surveys (CSUR) Surveys, Volume 24 Issue 1, March 1992, Pages 63 - 113<p>via <a href="http://www.allthingsdistributed.com/2013/04/join-processing-relational-databases.html" rel="nofollow">http://www.allthingsdistributed.com/2013/04/join-processing-...</a>

I like that this only covers left outer joins as opposed to left and right outer joins.<p>The difference between the two is sometimes confusing for people, so I've always suggested that people learn one and stick with it, since you can accomplish the exact same thing with either.