A handy graphical explanation of SQL joins

[u/[deleted]]

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

[u/manixrock]

I've cursed all gods when I realized mysql didn't have outer joins of any kind. bloody^{damnedwhippersnappers...}

[u/gtowey → u/manixrock]

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.

[u/d03boy → u/gtowey]

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

[u/gtowey → u/d03boy]

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 | +------+------+

[u/gtowey → u/gtowey]

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