MySQL FULL JOIN?
Following are my query and I want the result as given below. How can I do this in mysql ?
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
The result-set need to look like this:
LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678
Svendson Tove
34764
Solution 1:
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
Solution 2:
There are a couple of methods for full mysql FULL [OUTER] JOIN.
-
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
-
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
Solution 3:
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
)
Solution 4:
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 |
+----------+-----------+---------+
Solution 5:
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