What JOIN do I need?

[dlite922]

I know what a left join, right join and regular join-keyword-less joins do. But what if I want to display null on both tables/sides?

What i'm going to do if there's no full outer join in MySQL is take a union of inner and left joins.

Is this the only way?




Dan

[Atli]

Hi.

The only way I know to simulate a FULL OUTER JOIN is to take a UNION of LEFT and RIGHT joins.

Like, if I had these tables:
[code=mysql]CREATE TABLE jA ( value int );
CREATE TABLE jB ( value int );
INSERT INTO jA VALUES (1), (2), (3);
INSERT INTO jB VALUES (2), (3), (4);[/code]

A FULL OUTER JOIN could be simulated like so:
[code=mysql]mysql> SELECT * FROM jA
-> LEFT JOIN jB
-> ON jA.value = jB.value
-> UNION
-> SELECT * FROM jA
-> RIGHT JOIN jB
-> ON jA.value = jB.value;
+-------+-------+
| value | value |
+-------+-------+
| 1 | NULL |
| 2 | 2 |
| 3 | 3 |
| NULL | 4 |
+-------+-------+
4 rows in set (0.00 sec)
[/code]