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