SQL: cascade UNION and JOIN

问题:

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 UNION operation with another table or even with all TABLE_1.

How can I handle this new table result of the UNION.

for example after the previous 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.

回答1:

Use a derived table like "foo" here, and then JOIN again however you wish:

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.

回答2:

You need to provide a join in both 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

回答3:

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

回答4:

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.

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