News for hackers. Stuff that matters.
Re:A Visual Explanation of SQL Joins (2007) by Tycho on Monday May 27, 2013 @12:41PM (#5775273)
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.

(although modern databases may do something more complicated, I'm not sure)

Re:A Visual Explanation of SQL Joins (2007) by jandrewrogers on Monday May 27, 2013 @02:05PM (#5775780)
Databases turn the big Cartesian product into a large number of smaller Cartesian products by inexpensively grouping values such that keys not in the group are guaranteed to not meet the filter operation criterion.

Instead of n^2 operations it is more like m * ((n/m)^2) that can be evaluated in parallel across the m groups.

The simplest example of this is hash joins and equality filters. Keys that match on equality will also be grouped into the same hash bucket, reducing the search space. Keys that hash to different buckets will never be equal. The join is therefore the union of the cartesian product on each hash bucket, which requires fewer operations than filtering the cartesian product of the entire key set.

Re:A Visual Explanation of SQL Joins (2007) by tome on Monday May 27, 2013 @01:23PM (#5775541)
This was the realisation that made me finally understand joins (although they're certainly not generally implemented like that in practice!).
Re:A Visual Explanation of SQL Joins (2007) by mcintyre1994 on Monday May 27, 2013 @02:44PM (#5775934)
It's probably worth noting that a relation is a subset of the cartesian product too. For me at least, it makes the concepts make a lot more sense to think about it in these terms.
Re:A Visual Explanation of SQL Joins (2007) by Tloewald on Monday May 27, 2013 @05:47PM (#5776775)
Agreed. Indeed the example is quite misleading as it pointedly does not illustrate the most common case, which os of a small lookup table appearing in multiple result rows.