Alex Rivera | Logout

MySQL FULL JOIN?

Asked 2011-11-02T07:48:43.737
45

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
mysql
Edit
Report

1 Answer

42

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
answered 2011-11-02T08:57:37.863

Your Answer