为什么MySQL在FULL OUTER JOIN上报语法错误?

2021-11-20 00:00:00 join sql mysql mysql-error-1064

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 服务器版本相对应的手册，了解在airlines.iaco_code = airports.iaco_code 上的'outer join airports 附近使用的正确语法第 4 行的全外连接

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

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

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.

推荐答案

MySQL 中没有 FULL OUTER JOIN.请参阅 7.2.12.外连接简化和12.2.8.1.JOIN 语法:

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

你可以模拟 FULL OUTER JOIN 使用UNION(从 MySQL 4.0.0 开始):

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

有两个表 t1, t2:

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.id

三个表 t1、t2、t3:

with 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

相关文章