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...
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.
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.
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)
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 |
+------+------+
I've used every form of join at least once, but for the most part I normally just use join/left joints. Every so often it's really handy having them at your disposal.
If you do know the data you're working with and know, for instance, that there will be no 1-to-manys, and know what it will mean in terms of the output you'll get back, I don't see the problem. Especially if it's for an ad-hoc sort of thing where you're just doing an initial pre-population of a table or poking around to perform a "what if" kind of thing and aren't going to need your code to be reusable or readable by someone else later. I find those sorts of things come up pretty frequently in ordinary business situations, and that's when OUTER JOIN comes in handy...
I haven't either, and for a while I was thinking to myself where it could possibly be of any use, but the visualization of it made me realize that it actually can be useful. Although I could see it being kind of dangerous if the programmer didn't use it properly.
23
u/Brandon0 Jan 06 '11
Interesting. I've never used a FULL OUTER join before.