SQL: cascade UNION and JOIN

Question

Hi

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

now I want to join the result of this JOIN operation with another one table or even with all TABLE_1.

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

for ex 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.

thank

Answer 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.

Answer 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