FULL OUTER JOIN in MySQL

MySQL doesn’t support the FULL OUTER JOIN, so I did some googleing and found this:

Method 1 worked great for me; a UNION between a LEFT OUTER JOIN and a RIGHT OUTER JOIN.

 SELECT * FROM `left` L
 LEFT OUTER JOIN `right` R on L.sku = R.sku
 UNION
 SELECT * FROM `left` L
 RIGHT OUTER JOIN `right` R on L.sku = R.sku

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.

 Answers

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

---

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

FULL OUTER JOIN does not work as expected

I am trying to join 3 tables to get a list of stores/codes but I want all the codes to show up even if it doesn't appear under a store (as a NULL). Here is what I have so far:

Select
    pl.Store_Number
    ,CAST(s.Store_Number as varchar) + ' - ' + s.Store_Name as Store
    ,pc.Plan_Desc
    ,pc.Plan_Cd
    ,pl.Size
    ,pl.Position_Cd
From
    Plan pl
    INNER JOIN Store s ON s.Store_Number = pl.Store_Number
    FULL OUTER JOIN Plan_Code pc ON pc.Plan_Cd = pl.Plan_Cd
Where
    s.End_Date IS NULL
    and pl.Plan_ID <> 0
    and pc.Plan_Cd IN (1,2,3,4,5,6,31,7,8,9,10,11,13,14,36,37
        ,35,17,19,29,23,27,30,15,16,21,32,25,26,42,51,40,44,50,47,41,39)

But as a result I am only getting what matches in each table (like a normal INNER JOIN). Where am I going wrong?

EDIT:

I got a work buddie to help me with this and here is what we came up with:

Select
    pc.Store_Number
    ,CAST(pc.Store_Number as varchar) + ' - ' + pc.Store_Name as Store
    ,pc.Plan_Desc
    ,pc.Plan_Cd
    ,pl.Size
    ,pl.Position_Cd
From
    (
    Select
        Plan_Desc
        ,Plan_Cd
        ,Store_Number
        ,Store_Name
    From
        Plan_Code pc
        cross join STORE s
    Where
        Plan_Cd IN (1,2,3,4,5,6,31,7,8,9,10,11,13,14,36,37
            ,35,17,19,29,23,27,30,15,16,21,32,25,26,42,51,40,44,50,47,41,39)
        and s.End_Date is null
        and Store_Number <> 0

    ) pc
    LEFT OUTER JOIN (
        Select
            pl.Store_Number
            ,CAST(s.Store_Number as varchar) + ' - ' + s.Store_Name as Store
            ,pl.Plan_Cd
            ,pl.Size
            ,pl.Position_Cd
        From
            Plan pl
            INNER JOIN Store s ON s.Store_Number = pl.Store_Number
        Where
            s.End_Date IS NULL
            and pl.Plan_ID <> 0
    ) pl ON pc.Plan_Cd = pl.Plan_Cd and pc.Store_Number = pl.Store_Number
Where
    pc.Store_Number in (Select DISTINCT Store_Number From Plan)
Order By
    Store_Number
    ,pc.Planogram_Cd

---

The problem is the fact that you are referencing items from each side of the full join in the WHERE clause. For example in this line:

and pl.Plan_ID <> 0

If pl.PLan_ID is null because of the full join, you have NULL <> 0 which is not true, so any rows where pl.Plan_ID is NULL are not returned.

The same is true for pc.Plan_Cd, when this is NULL, the following line does not evaulation to true:

pc.Plan_Cd IN (1,2...

Therefore no rows will be returned where pc.Plan_Cd is NULL

I think you would need to move your where clauses inside subqueries:

SELECT  pl.Store_Number,
        pl.Store
        pc.Plan_Desc,
        pc.Plan_Cd,
        pl.Size,
        pl.Position_Cd
FROM    (   SELECT  pl.Store_Number,
                    CAST(s.Store_Number as varchar) + ' - ' + s.Store_Name AS Store,
                    pl.Size,
                    pl.Position_Cd,
                    pc.Plan_Cd
            FROM    Plan pl
                    INNER JOIN Store s
                        ON s.Store_Number = pl.Store_Number
            WHERE   s.End_Date IS NULL
            AND     pl.Plan_ID <> 0
        ) pl
        FULL OUTER JOIN
        (   SELECT  *
            FROM    Plan_Code pc
            WHERE   pc.Plan_Cd IN (1,2,3,4,5,6,31,7,8,9,10,11,13,14,36,37
                        ,35,17,19,29,23,27,30,15,16,21,32,25,26,42,51,40,44,50,47,41,39)
        ) PC
            ON pc.Plan_Cd = pl.Plan_Cd;