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.

Riker

There is no FULL OUTER JOIN syntax: dev.mysql.com/doc/refman/5.0/en/join.html – Santoro

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

Lancey

Is there a tutorial you'd recommend on joins? – Riker

Your answer is not really correct, see Xelrach's answer, or this comment. – Mixer

@Cletus, I keep getting 'Duplicate column name' error on the PK column they share... – Backrest

This solution is wrong. The second part should be an anti-join. – Leery

The first of the answer works for me. Haven't tried the second part though, but still gets an upvote – Elect

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;

Mccord

Note that - if you're sufficiently pedantic and theoretical - this isn't a perfect solution either. This approach (using UNION ALL but checking if one a column in one of the JOINed tables is NULL to identify that it can't be matched to any rows in the other tables) relies upon the tables actually having at least one NOT NULL column. If there's no column that is guaranteed not to actually contain a NULL, this approach won't work. – Abampere

@MarkAmery: You raise a good point. For the anti-join to work correctly, the predicate in the WHERE clause needs to reference a column or expression that we know (that we are guaranteed) will not be NULL if a matching row is found. In the special case of the join predicate using an equality comparison, that equality comparison guarantees us that the value of that column will not be NULL for matched rows. It doesn't have to an equality comparison to give us that guarantee. If the join does allow NULL values for "matched" rows, we have to find/use a non-NULL expression. – Craal

@MarkAmery: This query isn't relying on the tables having a NOT NULL column. Rather, this query relies on the join predicate (the condition in the ON clause) to guarantee a non-NULL value for a column in a "matched" row. This relies on the behavior of the equality comparison operator when used with NULL values. (We know that foo = NULL will not return TRUE, even when foo IS NULL.) – Craal

I wish mysql can add this. Probably just 5 lines of C code. Everyone of us have to write this 5 extra line of code for some many times. Someone working on mysql seeing this should consider this as a feature request. – Touter

Answer 3

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

Hardihood

But t2 is not declared at this point: JOIN t1 ON t1.id = t2.id. – Girandole

Answer 4

Just supplement the case when you need to FULL OUTER JOIN three tables t1, t2, t3. You could make t1, t2, t3, in turn, left joins the rest two tables, then union.

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

Gaff

Answer 5

Just to complement the answers provided here, I wanted to share this example of joining 4 tables, using LEFT JOIN on all SELECT statements, with a more pictorial example.

SELECT doctor.name, professor.name, singer.name, actor.name
FROM doctor
LEFT JOIN professor ON professor.id = doctor.id
LEFT JOIN singer ON singer.id = doctor.id
LEFT JOIN actor ON actor.id = doctor.id
UNION
SELECT doctor.name, professor.name, singer.name, actor.name
FROM professor
LEFT JOIN doctor ON doctor.id = professor.id
LEFT JOIN singer ON singer.id = professor.id
LEFT JOIN actor ON actor.id = professor.id
UNION
SELECT doctor.name, professor.name, singer.name, actor.name
FROM singer
LEFT JOIN doctor ON doctor.id = singer.id
LEFT JOIN professor ON professor.id = singer.id
LEFT JOIN actor ON actor.id = singer.id
UNION
SELECT doctor.name, professor.name, singer.name, actor.name
FROM actor
LEFT JOIN doctor ON doctor.id = actor.id
LEFT JOIN professor ON professor.id = actor.id
LEFT JOIN singer ON singer.id = actor.id;

Filterable

Answer 6

SELECT * gives all columns from t1 and t2. What if we want specific columns from t1 and t2 ?

I am looking for a MySQL alternative of SELECT Customer.C_name, Orders.amt FROM Customer FULL OUTER JOIN Orders ON Customer.C_id = Orders.cus_id;

Customer table has C_id and C_name and Orders table has order_id, cus_id and amt WHERE C_id = cus_id is the relation

Crewelwork

This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review – Scribner

This does not address the main part of the question and the given answer is wrong in the context of MySQL – Elect