MySQL FULL JOIN?

MySQL lacks support for FULL OUTER JOIN.

So if you want to emulate a Full join on MySQL take a look here .

A commonly suggested workaround looks like this:

SELECT  t_13.value AS val13, t_17.value AS val17FROM    t_13LEFT JOIN        t_17ON      t_13.value = t_17.valueUNION ALLSELECT  t_13.value AS val13, t_17.value AS val17FROM    t_13RIGHT JOIN        t_17ON      t_13.value = t_17.valueWHERE   t_13.value IS NULLORDER BY        COALESCE(val13, val17)LIMIT 30

There are a couple of methods for full mysql FULL [OUTER] JOIN.

1. UNION a left join and right join. UNION will remove duplicates by performing an ORDER BY operation. So depending on your data, it may not be performant.

    SELECT * FROM A    LEFT JOIN B ON A.key = B.key  UNION  SELECT * FROM A    RIGHT JOIN B ON A.key = B.key

2. UNION ALL a left join and right EXCLUDING join (that's the lower right figure in the diagram). UNION ALL will not remove duplicates. Sometimes this might be the behaviour that you want. You also want to use RIGHT EXCLUDING to avoid duplicating common records from selection A and selection B - i.e Left join has already included common records from selection B, lets not repeat that again with the right join.

    SELECT * FROM A    LEFT JOIN B ON A.key = B.key  UNION ALL SELECT * FROM A    RIGHT JOIN B ON A.key = B.key   WHERE A.key IS NULL

SELECT  p.LastName, p.FirstName, o.OrderNoFROM    persons AS pLEFT JOIN        orders AS oON      o.orderNo = p.p_idUNION ALLSELECT  NULL, NULL, orderNoFROM    ordersWHERE   orderNo NOT IN        (        SELECT  p_id        FROM    persons        )