How do I full join in Mysql?

Question

I have two tables:
T1
1,a
2,b

T2
2,ggg
3,hhh

I want the join between them to give me all fields:

1,a,null,null
2,b,2,ggg
null,null,3,hhh

Answer 1

MySQL doesn't have FULL OUTER JOIN, but you can emulate it e.g.:

SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.id = T2.id
UNION ALL
SELECT * FROM T1 RIGHT OUTER JOIN T2 ON T1.id = T2.id 
    WHERE T1.id IS NULL;

In general:

FULL OUTER JOIN = LEFT OUTER JOIN ∪ (RIGHT OUTER JOIN ∖ INNER JOIN)

You need to cut one inner join (in here from right join, but IMHO doesn't matter which one you choose), because both returns same inner joins. In here you have:

T1::

T2::

LEFT OUTER JOIN::

RIGHT OUTER JOIN::

INNER JOIN::

FULL OUTER JOIN::

Answer 2

If the tables have a (combination of) columns that is unique, you can build a list of ids in a subquery. Then you can use two outer joins to simulate a full outer join:

select  *
from    (
        select  col1
        from    t1
        union
        select  col1 
        from    t2
        ) ids
left join
        t1
on      ids.col1 = t1.col1
left join
        t2
on      ids.col1 = t2.col1