Error de syntax de combinación completa de SQL

Llegué a tablas con diferentes columnas y diferentes numbers de filas

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

y:

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

Me gustaría fusionar la tabla para get algo como esto, con filas de ambas tablas y llenar columnas vacías con 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 | 

Intenté FULL JOIN:

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

Pero vuelve:

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

Cómo puedo conseguir esto ? Gracias

MySQL no es compatible con FULL OUTER JOIN

Puedes hacer esto sin embargo.

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

El equivalente correcto es:

 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; 

La primera subconsulta produce la list de nombres en las dos tablas. LEFT JOIN s siempre coincidirá. Por lo tanto, esto produce el mismo resultado que un FULL JOIN , incluidos los duplicates (no deseados).