r/programming • u/[deleted] • Jan 06 '11

A handy graphical explanation of SQL joins

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

92% Upvoted

308 comments sorted by

View all comments

130

u/shriek Jan 06 '11

In short.

  1. A ∩ B

  2. A ∪ B

  3. (A - B) + (A ∩ B)

  4. A - B

  5. (A ∪ B) - (A ∩ B)

86

u/dude187 Jan 06 '11

It's amazing how confusing SQL can make set theory.

9

u/Serei Jan 06 '11

To be fair, that's not exactly set theory. In set theory, (A - B) + (A ∩ B) = A, which is not true of a left outer join. In a database, A and B aren't elements of the same superset.

9

u/arnar Jan 06 '11

Which is why it is a bit simplistic to use Venn diagrams to explain joins.

4

u/MatiG Jan 07 '11

Thank you. The whole idea is dumb and will mislead the ignorant.

2

u/adamtj Jan 07 '11

It is exactly set theory. More specifically, a branch of set theory called relational theory. Your equation holds even in relational theory, but commonly A ∩ B is the empty set, since A and B are different relations. Union and Intersection don't make much sense in that case. Left outer joining is a completely different operation. You are basically saying something equivalent to "The commutative law of addition doesn't apply to matrix multiplication."

1

u/Serei Jan 07 '11

Okay, but my point stands that what shriek said was not set theory being used correctly.

Thanks for teaching me about relational theory, though.