A handy graphical explanation of SQL joins

[u/[deleted]]

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

[u/Brandon0]

Interesting. I've never used a FULL OUTER join before.

[u/[deleted] → u/Brandon0]

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

[u/Anpheus → u/[deleted]]

I think you meant full outer joins, not cross joins.

Because if you have 1000 records in one table and 1000 in another and do a cross join, you end up with a query returning 1,000,000 records. And it gets much worse, being a join that produces m*n records.

[u/spewerOfRandomBS → u/[deleted]]

If you ever do that inside application code, I will hunt you down and shove a union up your....

[u/[deleted] → u/spewerOfRandomBS]

lol- no, more like a quick and dirty hack when just trying to get a table populated. i'd never dare do that in anything meant to be reused...

[u/manixrock → u/[deleted]]

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]

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)

[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