为什么MySQL在完整的外部连接上报告语法错误?

SELECT airline, airports.icao_code, continent, country, province, city, website 

FROM airlines 
FULL OUTER JOIN airports ON airlines.iaco_code = airports.iaco_code
FULL OUTER JOIN cities ON airports.city_id = cities.city_id
FULL OUTER JOIN provinces ON cities.province_id = provinces.province_id
FULL OUTER JOIN countries ON cities.country_id = countries.country_id
FULL OUTER JOIN continents ON countries.continent_id = continents.continent_id

It says that

它说

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer join airports on airlines.iaco_code = airports.iaco_code full outer join' at line 4

您的SQL语法有错误;检查与MySQL服务器版本对应的手册，找到正确的语法，以便在航空公司的“外部连接机场”附近使用。iaco_code =机场。iaco_code完整的外部连接在第4行

The syntax looks right to me. I've never done a lot of joins before, but I need those columns in a table which is cross referenced by various id's.

语法对我来说是正确的。我以前从来没有做过很多连接，但是我需要表中的这些列，这些列被各种id交叉引用。

4 个解决方案

#1

There is no FULL OUTER JOIN in MySQL. See 7.2.12. Outer Join Simplification and 12.2.8.1. JOIN Syntax:

MySQL没有完整的外部连接。看到7.2.12。外接简化和12.2.8.1。加入的语法:

You can emulate FULL OUTER JOIN using UNION (from MySQL 4.0.0 on):

您可以使用UNION(从MySQL 4.0.0开始)模拟完整的外部连接:

with two tables t1, t2:

有两个表t1, t2:
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
with three tables t1, t2, t3:

有三个表t1、t2、t3:
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
RIGHT JOIN t3 ON t2.id = t3.id

#2

cletus's answer isn't quite right. UNION will remove duplicate records that a FULL OUTER JOIN would include. If you need duplicates using something like:

克莱图斯的回答并不完全正确。UNION将删除完整外部连接将包含的重复记录。如果您需要重复使用以下内容:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
LEFT JOIN t4 ON t3.id = t4.id
UNION ALL
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
LEFT JOIN t4 ON t3.id = t4.id
WHERE t1.id IS NULL
UNION ALL
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
RIGHT JOIN t3 ON t2.id = t3.id
LEFT JOIN t4 ON t3.id = t4.id
WHERE t2.id IS NULL
UNION ALL
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
RIGHT JOIN t3 ON t2.id = t3.id
RIGHT JOIN t4 ON t3.id = t4.id
WHERE t3.id IS NULL;

#3

Just supplement the case when you need to FULL OUTER JOIN three tables t1, t2, t3. You could make t1, t2, t3, in turn, left joins the rest two tables, then union.

当你需要完整的外部连接3个表t1, t2, t3时，补充这个例子。可以让t1、t2、t3依次左连接其余两个表，然后合并。

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t1.id = t3.id
UNION
SELECT * FROM t2
LEFT JOIN t1 ON t2.id = t1.id
LEFT JOIN t3 ON t2.id = t3.id
UNION
SELECT * FROM t3
LEFT JOIN t1 ON t3.id = t1.id
LEFT JOIN t2 ON t3.id = t2.id

#4

I have just made a trick for this:

我为此开了个玩笑:

(select 1 from DUAL) d
LEFT OUTER JOIN t1 ON t1.id = t2.id
LEFT OUTER JOIN t2 ON t1.id = t2.id

the point is, that the query from dual makes a fix point, and mysql can outer join the 2 other tables to that

重点是，来自dual的查询会产生一个修复点，mysql可以将另外两个表连接到这个点

#1