代码之家 › 专栏 › 技术社区 › Josh K

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

mysql-error-1064 join sql mysql php

Josh K · 技术社区 · 15 年前

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

上面写着

您的SQL语法有错误;请查看与您的MySQL服务器版本相对应的手册,以获得在“外部联接”附近使用的正确语法。 airports 在airlines.iaco_code=airports.iaco_code上第4行的完全外部连接

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

4 回复 | 直到 7 年前

cletus 15 年前

没有 FULL OUTER JOIN 在MySQL中。见 7.2.12. Outer Join Simplification 和 12.2.8.1. JOIN Syntax :

你可以模仿 完全外部联接 使用联合(从MySQL4.0.0开始):

有两张表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

有三张表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

EmDash 14 年前

克莱特斯的回答不太正确。 UNION 将删除 FULL OUTER JOIN 包括。如果需要重复,请使用以下方法:

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;

Song Zhengyi 7 年前

当你需要的时候补充一下。 FULL OUTER JOIN 三张表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

user5728636 8 年前

我刚刚做了个小把戏:

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

重点是,dual的查询是一个固定点,mysql可以将另外两个表外部连接到这个固定点。