SQL: Cascading UNION and JOIN

Question

I have a combined operation 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 operation UNIONwith another table or even with all TABLE_1.

How can I process this new table result from UNION.

for example, after the previous one UNION:

RIGHT JOIN TABLE_3
        ON TABLE_3.ID_3 = XXXXXXXXXXXXXXXXXXXX.ID_2

I really do not know what I need to put in place of XXXXXXXXXXXXXXXX to create a new table generated UNION.

Answer 1

Use a view like "foo" here, and then join it again, but you want:

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 comment said
    SELECT ID_2, name_2, surname_2 FROM TABLE_2
    ) foo  ON TABLE_3.ID_3 = foo.ID_1

PS. Use LEFT connections: less messy, and then RIGHT.

Answer 2

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

Answer 3

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 UNION eliminates duplicates, so if you want duplicates to be included, uyse UNION ALL