MySQL FULL JOIN?

Question

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 val17
FROM    t_13
LEFT JOIN
        t_17
ON      t_13.value = t_17.value
UNION ALL
SELECT  t_13.value AS val13, t_17.value AS val17
FROM    t_13
RIGHT JOIN
        t_17
ON      t_13.value = t_17.value
WHERE   t_13.value IS NULL
ORDER 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.OrderNo
FROM    persons AS p
LEFT JOIN
        orders AS o
ON      o.orderNo = p.p_id
UNION ALL
SELECT  NULL, NULL, orderNo
FROM    orders
WHERE   orderNo NOT IN
        (
        SELECT  p_id
        FROM    persons
        )

Try This:

(SELECT p.LastName, p.FirstName, o.OrderNo
 FROM   Persons p
   LEFT JOIN Orders o
   ON o.OrderNo = p.P_id
)
UNION
(SELECT p.LastName, p.FirstName, o.OrderNo
 FROM   Persons p
   RIGHT JOIN Orders o
   ON o.OrderNo = p.P_id
);

+----------+-----------+---------+
| LastName | FirstName | OrderNo |
+----------+-----------+---------+
| Singh    | Shashi    |       1 |
| Yadav    | Sunil     |    NULL |
| Singh    | Satya     |    NULL |
| Jain     | Ankit     |    NULL |
| NULL     | NULL      |      11 |
| NULL     | NULL      |      12 |
| NULL     | NULL      |      13 |
+----------+-----------+---------+

Hm, combining LEFT and RIGHT JOIN with UNION could do this:

SELECT  p.LastName, p.FirstName, o.OrderNo
FROM    persons AS p
LEFT JOIN
        orders AS o
ON      p.P_Id = Orders.P_Id
UNION ALL
SELECT  p.LastName, p.FirstName, o.OrderNo
FROM    persons AS p 
RIGHT JOIN
        orders AS o
ON      p.P_Id = Orders.P_Id
WHERE   p.P_Id IS NULL

Full join in mysql :(left union right) or (right unoin left)

 SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
    FROM Persons
    left JOIN Orders
    ON Persons.P_Id=Orders.P_Id
    ORDER BY Persons.LastName

    Union

    SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
    FROM Persons
    Right JOIN Orders
    ON Persons.P_Id=Orders.P_Id
    ORDER BY Persons.LastName