SQL Full Join语法错误 [英] SQL Full Join syntax error

问题描述

我要使用具有不同列和不同行数的表

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

和:

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

我想合并表以获得类似的内容，两个表的行都用NULL填充.

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      |

我尝试了FULL JOIN:

I tried FULL JOIN:

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

但是它返回:

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

我该如何实现? 谢谢

推荐答案

MySQL不支持FULL OUTER JOIN

MySQL doesn't support FULL OUTER JOIN

您可以执行此操作.

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

这篇关于SQL Full Join语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！