php 为什么MySQL在FULL OUTER JOIN上报语法错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2384298/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why does MySQL report a syntax error on FULL OUTER JOIN?
提问by Josh K
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
airportson airlines.iaco_code = airports.iaco_code full outer join' at line 4
您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取
airports在第 4 行的“airlines.iaco_code = airports.iaco_code full outer join”附近使用的正确语法
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 交叉引用。
回答by cletus
There is no FULL OUTER JOINin MySQL. See 7.2.12. Outer Join Simplificationand 12.2.8.1. JOIN Syntax:
FULL OUTER JOINMySQL 中没有。见7.2.12。外连接简化和12.2.8.1。加入语法：
You can emulate
FULL OUTER JOINusing UNION (from MySQL 4.0.0 on):with two tables t1, t2:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id UNION SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.idwith three tables 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
您可以
FULL OUTER JOIN使用 UNION进行模拟（从 MySQL 4.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
回答by EmDash
cletus's answer isn't quite right. UNIONwill remove duplicate records that a FULL OUTER JOINwould include. If you need duplicates using something like:
cletus 的回答不太正确。UNION将删除 aFULL 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;
回答by Song Zhengyi
Just supplement the case when you need to FULL OUTER JOINthree tables t1, t2, t3. You could make t1, t2, t3, in turn, left joins the rest two tables, then union.
只需要补充FULL OUTER JOINt1、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
回答by Song Zhengyi
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 可以将其他 2 个表外部连接到那个
