Problem of total Join

[MAHESA]

Here is my problem: either 2 different tables with a column "Common"

Table1
X | Y 
--+--
1 | A
2 | B
 
Table2
X | Z
--+--
2 | C
3 | D

I would like to find the SELECT query that gives

X | Y    | Z
--+------+--
1 | A    | NULL
2 | B    | C
3 | NULL | D

the query

SELECT * FROM Table1 LEFT JOIN Table2 USING (X);

gives only

X | Y    | Z
--+------+--
1 | A    | NULL
2 | B    | C

I tried many things without finding solutions. What is the problem with my total Join in my SQL query?

[void]

MySQL does not include "total" join (FULL JOIN), but you can simulate it with 2 outer joins and UNION like this:

SELECT x, y, z
FROM table1
  NATURAL LEFT JOIN table2
UNION
SELECT x, y, z
FROM table1
  NATURAL RIGHT JOIN table2

[!const]

You have not tried FULL OUTER JOIN:

WITH T1 (X, Y) AS (SELECT 1, 'A' union ALL SELECT 2, 'B')
   , T2 (X, Z) AS (SELECT 2, 'C' union ALL SELECT 3, 'D')
SELECT
    coalesce(T1.X, T2.X) AS X,
    T1.Y,
    T2.Z
FROM
    T1 full OUTER JOIN T2
       ON T2.X = T1.X
ORDER BY
    X ASC;
 
X           Y    Z
----------- ---- ----
1           A    NULL
2           B    C
3           NULL D

[void]

It does not fail to coalesce (), at least not on the X column, since it serves as "pivotal" to the joints. All values in column X are back and are never null, right?

[MAHESA]

In fact, the join column is not null, I had not specified in the hypothesis. I am using MySQL 5.0.83. I thought that we do not have a "FULL JOIN" in MySQL. Thank you for the double join with union, it was not super difficult, however, I think of it!

[fellah]

MySQL allows left outer joins (LEFT [OUTER] JOIN) and right (RIGHT [OUTER] JOIN), but not the full outer join. This join returns all values of the joined tables, filling the columns of a NULL when there is no correlation with the other table.