SQL：级联 UNION 和 JOIN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1418431/
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
SQL: cascade UNION and JOIN
提问by Lopoc
I have a union opertaion between two tables
我在两个表之间有一个联合操作
SELECT ID_1,
name_1,
surname_1,
FROM T_ONE
UNION
SELECT ID_2,
name_2,
surname_2
FROM TABLE_2
I want to join the result of this UNIONoperation with another table or even with all TABLE_1.
我想将此UNION操作的结果与另一个表甚至所有TABLE_1.
How can I handle this new table result of the UNION.
我该如何处理UNION.
for example after the previous UNION:
例如在前一个之后UNION：
RIGHT JOIN TABLE_3
ON TABLE_3.ID_3 = XXXXXXXXXXXXXXXXXXXX.ID_2
I really do not know what I need to put instead of the XXXXXXXXXXXXXXXX to andle the new table generated by the UNION.
我真的不知道我需要用什么来代替 XXXXXXXXXXXXXXXX 来处理由UNION.
回答by gbn
Use a derived table like "foo" here, and then JOIN again however you wish:
在这里使用像“foo”这样的派生表，然后根据需要再次加入：
SELECT
*
FROM
TABLE_3
LEFT JOIN
(
SELECT ID_1, name_1, surname_1, FROM T_ONE
UNION --ALL would be more efficient if results do not overlap, as van's comment said
SELECT ID_2, name_2, surname_2 FROM TABLE_2
) foo ON TABLE_3.ID_3 = foo.ID_1
PS. Use LEFT joins: less confusing then RIGHT joins.
附注。使用 LEFT 连接：比 RIGHT 连接更容易混淆。
回答by MaxiWheat
You need to provide a join in both SELECT :
您需要在两个 SELECT 中提供连接：
SELECT ID_1, name_1, surname_1, FROM T_ONE
RIGHT JOIN TABLE_3 ON TABLE_3.ID_3 = T_ONE.ID_1
UNION
SELECT ID_2, name_2, surname_2 FROM TABLE_2
RIGHT JOIN TABLE_3 ON TABLE_3.ID_3 = TABLE_2.ID_2
Or something like that. Don't forget that a UNION eliminates the duplicates, so if you want duplicates to be included, uyse UNION ALL
或类似的东西。不要忘记 UNION 会消除重复项，因此如果您希望包含重复项，请使用 UNION ALL
回答by SQL
SELECT ID_1, name_1, surname_1, FROM T_ONE
from
(SELECT ID_1, name_1, surname_1, FROM T_ONE
UNION
SELECT ID_2, name_2, surname_2 FROM TABLE_2 ) foo
left join TABLE_3
ON TABLE_3.ID_3 =foo.ID_2
回答by wandmdave
To expand on gbn's answer above for those who have asked. Here is how to do it with the union as the first part of the left join.
为那些提出问题的人扩展上述 gbn 的回答。这是将联合作为左连接的第一部分的方法。
SELECT table3.phone, foo.fname, foo.lname, foo.email
FROM
(SELECT table1.fname,table1.lname,table1.email
FROM table1
UNION
SELECT table2.fname,table2.lname,table2.email
FROM table2
) foo
LEFT JOIN table3
ON foo.lname = table3.lname
