为什么MySQL在FULL OUTER JOIN上报告语法错误? [英] Why does MySQL report a syntax error on FULL OUTER JOIN?

问题描述

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服务器版本相对应的手册以获取正确的语法，以在Airliness.iaco_code = airport.iaco_code上的外部联接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:

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

这篇关于为什么MySQL在FULL OUTER JOIN上报告语法错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！