CODEWITHSUNDEEP
mysqlsqljoinmysql-error-1064
Josh K
Josh K

Reputation: 28883

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

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.

Upvotes: 71

Views: 97907

Answers (6)

rog_SARTHAK
rog_SARTHAK

Reputation: 1

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

Upvotes: -1

ivanleoncz
ivanleoncz

Reputation: 10015

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;

Upvotes: 0

Song Zhengyi
Song Zhengyi

Reputation: 339

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

Upvotes: 1

user5728636
user5728636

Reputation:

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

Upvotes: 1

EmDash
EmDash

Reputation: 420

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;

Upvotes: 18

cletus
cletus

Reputation: 625077

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

Upvotes: 110

Related Questions