MySQL 完全加入？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7978663/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL FULL JOIN?
提问by
Following are my query and I want the result as given below. How can I do this in mysql ?
以下是我的查询，我想要如下所示的结果。我怎样才能在 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
回答by aleroot
MySQL lacks support for FULL OUTER JOIN.
MySQL 缺乏对 FULL OUTER JOIN 的支持。
So if you want to emulate a Full join on MySQL take a look here.
因此，如果您想在 MySQL 上模拟完整连接，请查看此处。
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
回答by a20
There are a couple of methods for full mysql FULL [OUTER] JOIN.
完整的 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.keyUNION 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
UNION 左连接和右连接。UNION 将通过执行 ORDER BY 操作删除重复项。因此，根据您的数据，它可能性能不佳。
SELECT * FROM A LEFT JOIN B ON A.key = B.key UNION SELECT * FROM A RIGHT JOIN B ON A.key = B.keyUNION ALL 左连接和右 EXCLUDING 连接（这是图中右下角的图）。UNION ALL 不会删除重复项。有时这可能是您想要的行为。您还希望使用 RIGHT EXCLUDING 来避免复制选择 A 和选择 B 中的公共记录 - 即左连接已经包含了选择 B 中的公共记录，不要用右连接再次重复。
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
回答by Quassnoi
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
)
回答by Shashi Singh
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 |
+----------+-----------+---------+
回答by Marko Ivan?i?
Hm, combining LEFT and RIGHT JOIN with UNION could do this:
嗯，将 LEFT 和 RIGHT JOIN 与 UNION 结合可以做到这一点：
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
回答by yousef alipour
Full join in mysql :(left union right) or (right unoin left)
完全加入 mysql :(left union right) 或 (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
回答by Shubham Singh
Everyone is correct here but writing same query twice is not a good way of programming ... so I have another way to do the full join in mysql which is as follows
这里每个人都是正确的，但是两次编写相同的查询并不是一种好的编程方式......所以我有另一种方法可以在 mysql 中进行完全连接，如下所示
SELECT
user_id , user_name, user_department
FROM
(SELECT
user_id , user_name , NULL as user_department
FROM
tb_users
UNION
SELECT
user_id ,NULL as user_name , user_department
FROM
tb_departments
) as t group by user_id
