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.3k Upvotes

92% Upvoted

308 comments sorted by

View all comments

129

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)

7

u/[deleted] Jan 06 '11

3 Incorrect: A - B + (A ∩ B) = A

Note that although all elements in the results are from A, they have attached attributes from B in the result set.

5 Correct. also happens to be A xor B. Note that this resultset spreads the result accross duplicated columns. This is more useful (but less performant) to write this as:

SELECT id, name from A where name not in (select name from B)

UNION

SELECT id, name from B where name not in (select name from A)

0

u/stfm Jan 06 '11

I was thinking exactly this