SQL Full Join syntax error [duplicate]

0
votes

I've got to tables with different columns and different numbers of rows

+----+----------+--------+------+-------+---------+-------+--------+--------+------+
| ID | Name     | SpT    | Teff | RAdeg | DEdeg   | Dist  | n_Dist | pm     | Ref  |
+----+----------+--------+------+-------+---------+-------+--------+--------+------+
|  1 | 0000-345 | DCP8.1 | 6643 | 0.667 | -34.222 | 13.21 | p      | 0.7578 | L20  |
|  2 | 0008+424 | DA6.8  | 7380 | 2.843 |  42.678 | 22.00 | sp     | 0.2328 | L20  |
|  3 | 0009+501 | DAH7.6 | 6502 | 3.061 |  50.422 | 11.03 | p      | 0.7150 | L20  |
|  4 | 0011-134 | DAH8.4 | 5992 | 3.553 | -13.183 | 19.49 | p      | 0.8990 | L20  |
|  5 | 0011-721 | DA7.8  | 6325 | 3.457 | -71.831 |  NULL | sp     | 0.3260 | GBD  |
+----+----------+--------+------+-------+---------+-------+--------+--------+------+

and:

+----+----------+--------+-------+-------+-------+------+
| ID | Name     | SpT    | Uvel  | Vvel  | Wvel  | Vtot |
+----+----------+--------+-------+-------+-------+------+
|  1 | 0000-345 | DCP9   | -11.9 | -43.7 |   3.2 | 45.4 |
|  2 | 0008+424 | DA6.8  | -10.0 |  -1.9 | -17.4 | 20.2 |
|  3 | 0009+501 | DAH7.7 | -28.1 |   8.6 | -24.1 | 38.0 |
|  4 | 0011-134 | DCH8.4 | -75.5 | -31.1 |  -9.8 | 82.3 |
|  5 | 0011-721 | DA8.0  |  10.3 | -23.2 |  12.2 | 28.2 |
+----+----------+--------+-------+-------+-------+------+

I would like to merge the table to get something like this, with rows of both tables and fill empty columns with NULL.

+------------+-----+------------+---------+---------+-------+--------+--------+-------+--------+--------+--------+-------+---------+
| Name       | ID  | SpT        | RAdeg   | DEdeg   | Dist  | n_Dist | pm     | Teff  | Uvel   | Vvel   | Wvel   | Vtot  | Ref     |
+------------+-----+------------+---------+---------+-------+--------+--------+-------+--------+--------+--------+-------+---------+
| 0000-345   |   1 | DCP8.1     |   0.667 | -34.222 | 13.21 | p      | 0.7578 |  6643 |  -11.9 |  -43.7 |    3.2 |  45.4 | L20     |
| 0008+424   |   2 | DA6.8      |   2.843 |  42.678 | 22.00 | sp     | 0.2328 |  7380 |  -10.0 |   -1.9 |  -17.4 |  20.2 | L20     |
| 0009+501   |   3 | DAH7.6     |   3.061 |  50.422 | 11.03 | p      | 0.7150 |  6502 |  -28.1 |    8.6 |  -24.1 |  38.0 | L20     |
| 0011-134   |   4 | DAH8.4     |   3.553 | -13.183 | 19.49 | p      | 0.8990 |  5992 |  -75.5 |  -31.1 |   -9.8 |  82.3 | L20     |
| 0011-721   |   5 | DA7.8      |   3.457 | -71.831 |  NULL | sp     | 0.3260 |  6325 |   10.3 |  -23.2 |   12.2 |  28.2 | GBD     |
| 0029-031   |   6 | DA11.3     |   8.041 |  -2.900 | 23.47 | p      | 0.6505 |  4470 |   68.3 |  -23.0 |    2.6 |  72.1 | Pi      |

I tried FULL JOIN:

SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.Name = t2.Name;

But it returns:

ERROR 1064 (42000): You have an error in your SQL syntax

How can I achieve this ? Thanks

2
mysqlsqljoin
MySQL doesn't have a FULL OUTER JOIN – Gurwinder Singh
You have to replace full join with union of left join and right join results. – vlasiak
Although this question has been asked, the pointed-to duplicate does not have the correct answer (it is unfortunate that a so-upvoted answer is not correct). I have added the correct answer. – Gordon Linoff

2 Answers

0
votes

MySQL doesn't support FULL OUTER JOIN

You can do this though.

SELECT * FROM t1 LEFT JOIN t2 ON t1.Name = t2.Name
UNION
SELECT * FROM t1 RIGHT JOIN t2 ON t1.Name = t2.Name
;
1
votes

The correct equivalent is:

SELECT t1.*, t2.*
FROM (SELECT t1.name FROM t1 UNION  -- not UNION ALL!
      SELECT t2.name FROM t2
     ) n LEFT JOIN
     t1
     ON t1.name = n.name LEFT JOIN
     t2
     ON t2.name = n.name;

The first subquery produces the list of names in the two tables. The LEFT JOINs will always match. So, this produces the same output as a FULL JOIN, including (unwanted) duplicates.