How to union multiple tables combining columns with same name

user_5274291 0
Viewed 505

Given the following tables:

Table B                       Table C
ID    PROP_A    PROB_B        ID    PROP_A    PROP_C
-----------------------       -----------------------
1     V1        V1            1     V2        V2
2     V2        V2            2     V3        V3
3     V3        V3            3     V4        V4

I would like to unioun those tables to obtain the following result:

Table B+C
ID    TABLE_NAME    PROP_A    PROP_B    PROP_C
-----------------------------------------------------
1     B             V1        V1        null
2     B             V2        V2        null
3     B             V3        V3        null
1     C             V2        null      V2
2     C             V3        null      V3
3     C             V4        null      V4

I'm looking for a solution that is agnostic to the database technology (standard SQL). However, in case of technology dependent SQL, I would to like known how to do it in Oracle.

oracle sql
3 Answers

This sounds like union all:

select id, 'B' as table_name, prop_a, prop_b, null as prop_c
from b
union all
select id, 'C' as table_name, prop_a, null as prop_b, prop_c
from c;
user_11440350

It is odd that you want the TABLE_NAME column in the output as the second column, not the first. Is that important?

If it's not - if having it as the first column is OK - then there is a solution that does not require you to know the number and name of the columns of the two tables in advance. It uses natural full outer join which is in the standard.

I don't have your tables, so I tested with the EMP and DEPT tables in the SCOTT schema. Change the table names as needed. The only constraint is that the table names must be different; that is what causes the natural full outer join to become the union you desire.

select *
from   (select 'EMP'  as table_name, e.* from scott.emp  e)
       natural full outer join
       (select 'DEPT' as table_name, d.* from scott.dept d)
;
Admin1

Just make sure to have all columns in both Selects. You can use static values, like this:

SELECT b.ID, 'B' AS TABLE_NAME, b.PROP_A, b.PROP_B, NULL AS PROP_C
FROM b
UNION ALL
SELECT c.ID, 'C' AS TABLE_NAME, c.PROP_A, NULL AS PROP_B, c.PROP_C
FROM c
user_47125330
Related