In a series of posts on both Coding Horror and the linked Me Talking Out Loud, comments have arisen that suggest Venn diagrams are insufficient to properly represent SQL Joins.
I totally disagree, Venn diagrams fit perfectly. The biggest problem is that people creating the diagram assume that the Table is one of the sets. This is apparently the problem again with Jeff’s and Ligaya’s diagrams.
Here’s one of Jeff’s awesome images:
We’ve seen this as the classical starting point to explain joins in relational databases. Once you get over the mechanics of what the JOIN operations do, you need to abandon this thinking quickly and focus on just what are those sets, otherwise you keep thinking about tables, not conditions.
For instance, how are these SQL statements different?
SELECT *
FROM customer
INNER JOIN order ON order.id = customer.id
WHERE
order.total > 100
SELECT *
FROM customer
INNER JOIN order ON order.id = customer.id
AND order.total > 100
They aren’t. In a Venn Diagram, this could be represented as:
Next time you’re working up some SQL code, start first with what you are trying to get, and then focus on how to they relate with a JOIN.
What about the CROSS JOIN you say? Instead of this (from Ligaya):
Draw a large circle with the product of the areas of your original sets… 
#1 by mikhael on October 12, 2010 - 3:40 pm
Quote
That’s one way to get at what’s going on, but if you loose focus on your original sets, the original digram has failed to describe your JOIN and you have to come up with another. So I guess “perfect” is not the word I would choose.
If two tables are in a one-to-one relationship with each other, the original two-circle diagrams happily describe a given join. It’s when you introduce 1-to-many relationships that we are forced to create a new set that neither of the original circles represent even partially. In this scenario we are not simply identifying singular objects that share a given characteristic, we are creating new objects/records that fit our description that did not exist previously.
In other words, when we say “Give me the LEFT OUTER JOIN” between two tables that have a one-to-many relationship, we are saying “The Records from the LEFT table are not good enough to satisfy what I want. The records in the Right table are not what I want either. Instead give me something completely different based on these Left and Right components.”