Awesome explanation, thanks!
Nice Visuals…
thanks for the explanations…
7 or so years after this was originally posted, I still refer to it all of the time & refer others to it. Nicely done!
This post is hugely misleading. There is some relationship between a join and an intersection, sure. But they are not the same thing.
In particular, an intersection is a join in the lattice of sets. But tables/relations aren’t just sets. They’re sets of tuples, and they have a richer notion of a join, based on quotients and other algebraic constructs. The join of a pair of relations is the smallest relation that is bigger than them both. The inner join is a closure operator. The (left) outer join is an adjoint operator. Etc.
The funny thing is, the blog post gave you half the story in 10 times as many words. Don’t fight math. It will always win.
Great article! According to one of the post about other articles about sql joins I recommend another with similar approach (venn diagram, the example table and query with result for each case): http://www.vertabelo.com/blog/technical-articles/sql-joins
2015 and STILL your example is the very BEST on the web!
I have been looking for this explanation for years, thank you.
If you’ve been using SQL, but not primarily (in other words, as a means to an end), for many years, you know when you need to use a JOIN. Most programmers don’t care enough about SQL or databases or set theory or discrete math to learn this theory in detail and pages like this are critical for us to get our jobs done.
That’s mathematical rigor and 99% of programmers don’t need it. Programming is about getting the math good enough, or else no programs would ever get written. We already know where the math matters, for example, in encryption code.
You missed the point. The rigor makes things easier, not harder. It takes 10 times as many words to explain things the wrong way. And then you often need to know how to un-roll the wrong explanation into the right one.
How do you figure out a query’s computational complexity with the “set” explanation? You have to unroll it into the real thing (i.e., tuples) and then do a counting argument. If you start with the real thing, the counting argument takes less than a minute. If you start with the wrong explanation, you might never even reach a valid estimate.
How about something like this for the cross join diagram?
2 Likes
Hey thanks for making JOINS visually familiar with the notion of Venn diagrams. 
I want to thank my colleague Hengineer for pointing me to this reference too. 
But I have a question:
I’m getting more comfortable with SQL syntax. I use this as a resource. In your examples, your functions are:
- INNER JOIN
- FULL OUTER JOIN
- LEFT OUTER JOIN
On the site I mentioned, these are the JOIN functions:
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
- SELF JOIN
- CARTESIAN JOIN
I see a couple matches and similarities, but I don’t want to assume. Also, the wiki page you mentioned in your intro kinda messes me up too 
Can you or anyone walk me through the syntax (maybe semantics?) of these function names, so I can be clear about what really does what?
Thanks!
It is a nice explanation, but why you use tables with only names?
A real scenario would be better.
Where ‘possible table contents’ is the cross product given A and B.
Cross product gives us everything outside the diagram as well as what is inside. …Within the scope of possible results that is. This is all in a bigger circle. As far as I can see, the model holds. And if it doesn’t? Then we just need another circle to contain the instances where it fails. 
@codinghorror your work has been replicated in es6 
BTW, I can’t count the number of times I’ve referred back to this page over the years. Thanks.
2 Likes
Good post, very clear!
Note: the image for inner join is returning 404.
It’s a Ghost Pro hosting issue… their CDN isn’t returning the image. I’ve written them about it.
1 Like
Helped me a LOT !!
Thank you for sharing 
1 Like
I think this has been in my bookmarks for about 6-7 years now and it’s where I go everytime I need my memory jogged
2 Likes
Welcome to the community, Ewan! Everyone’s memory needs to be jogged every once in a while. That’s why I’ve been reading the old topics for some time. I even get a few good laughs from some of the remarks. Refreshing in more than one way. 
1 Like