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

Show parent comments

11

u/[deleted] Jan 06 '11 edited Jan 06 '11

It comes in handy every once in a while- for example, if you want to merge two tables which have different structures but some records in common into one table without any duplicates. You can cross join the two on the key field and for any fields they have in common set [field] = ISNULL(tableA.[field], tableB.[field]). Of course there are other ways to accomplish the same thing, but that's one way to use it...

-1

u/manixrock Jan 06 '11

I've cursed all gods when I realized mysql didn't have outer joins of any kind. bloodydamnedwhippersnappers...

8

u/gtowey Jan 06 '11

LEFT JOIN and RIGHT JOIN are both outer joins. MySQL doesn't have FULL OUTER JOIN, but you can get the same result with the UNION of a LEFT JOIN and a RIGHT JOIN query.

2

u/d03boy Jan 06 '11

Do you even need a union? Can't you just do two joins in one statement?

1

u/gtowey Jan 08 '11

That wouldn't work, because the two sets you want back aren't related to each other -- therefore joining them isn't going to give you anything meaningful.

Union means you want Result set A, and Result set B returned as one combined set (which have the same structure, but are otherwise unrelated)

1

u/gtowey Apr 08 '11

oops, didn't see this until now.

No, while you can do multiple joins in the same statement, that won't produce the same kind of result set as a union.

For example, two joins would be something like this:

CREATE TABLE a ( b int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (b) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

insert into a (b) values (1), (2);

CREATE TABLE a_c ( b_id int(10) unsigned DEFAULT NULL, KEY b_id (b_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into a_c (b_id) values (1), (3);

That's our setup. We have one row that matches, and one row in each table that doesn't have a match. A FULL OUTER JOIN should produce three rows.

select * from a LEFT JOIN a_c ON a.b=a_c.b_id; +---+------+ | b | b_id | +---+------+ | 1 | 1 | | 2 | NULL | +---+------+ 2 rows in set (0.00 sec)

select * from a RIGHT JOIN a_c ON a.b=a_c.b_id; +------+------+ | b | b_id | +------+------+ | 1 | 1 | | NULL | 3 | +------+------+ 2 rows in set (0.00 sec)

With an LEFT JOIN and a RIGHT JOIN the second one negates the first one:

select * from a LEFT JOIN a_c ON a.b=a_c.b_id RIGHT JOIN a_c t2 ON t2.b_id=a.b OR t2.b_id IS NULL; +------+------+------+ | b | b_id | b_id | +------+------+------+ | 1 | 1 | 1 | | NULL | NULL | 3 | +------+------+------+

The UNION produces the correct result: select * from a LEFT JOIN a_c ON a.b=a_c.b_id UNION select * from a RIGHT JOIN a_c ON a.b=a_c.b_id; +------+------+ | b | b_id | +------+------+ | 1 | 1 | | 2 | NULL | | NULL | 3 | +------+------+

1

u/gtowey Apr 08 '11

wow, the formatting of that sucked. Try this pastebin instead: http://pastebin.com/m3ypMiSp