Union two tables with different number of columns

Question

I have two tables (Table A and Table B).

The two tables have different number of columns - assuming table A has more columns.

How can I merge these two tables and get null values ​​on columns that table B doesn't have?

Answer 1

I came here and followed the answer above. But the order mismatch of data types resulted in an error. The description below from another answer will be helpful.

Are the above results the same as the column order in your table? Because Oracle is very strict on column order. The following example will generate an error:

create table test1_1790 (
col_a varchar2(30),
col_b number,
col_c date);

create table test2_1790 (
col_a varchar2(30),
col_c date,
col_b number);

select * from test1_1790
union all
select * from test2_1790;

ORA-01790: expression must have the same data type as the corresponding expression

As you can see, the root cause of the error is a column order mismatch implied by using * as the column list specifier. This type of error can be easily avoided by entering the column list explicitly:

select col_a, col_b, col_c from test1_1790 union all select col_a, col_b, col_c from test2_1790; A more common situation for this error is when you inadvertently swap (or move) two or more columns in a SELECT list:

select col_a, col_b, col_c from test1_1790
union all
select col_a, col_c, col_b from test2_1790;

Alternatively, if the above doesn't solve your problem, how about creating an alias in the column , like this: (The query is different from yours, but the point here is how to create an alias in the column Add an alias in.)

SELECT id_table_a, 
       desc_table_a, 
       table_b.id_user as iUserID, 
       table_c.field as iField
UNION
SELECT id_table_a, 
       desc_table_a, 
       table_c.id_user as iUserID, 
       table_c.field as iField

Answer 2

For tables with fewer columns, you can add additional empty columns, for example:

Select Col1, Col2, Col3, Col4, Col5 from Table1
Union
Select Col1, Col2, Col3, Null as Col4, Null as Col5 from Table2