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:

CREATE TABLE jA ( value int );
CREATE TABLE jB ( value int );
INSERT INTO jA VALUES (1), (2), (3);
INSERT INTO jB VALUES (2), (3), (4);

A FULL OUTER JOIN could be simulated like so:

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)