Why does MySQL report a syntax error on FULL OUTER JOIN?

Question

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

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.

Answer 1

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

You can emulate FULL OUTER JOIN using 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.id 

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 

Answer 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:

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;