MySQL 完全连接？

Question

以下是我的查询，我希望得到如下所示的结果。我怎样才能在 mysql 中做到这一点？

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

结果集需要如下所示:

LastName      FirstName     OrderNo
Hansen        Ola          22456
Hansen        Ola          24562
Pettersen     Kari         77895
Pettersen     Kari         44678
Svendson      Tove   
                           34764

Answer 1

MySQL 不支持 FULL OUTER JOIN。

因此，如果您想在 MySQL 上模拟完全连接，请查看 here .

通常建议的解决方法如下所示:

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