联合使用不同列数的两个表

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

我有两张桌子(桌子A和桌子B)。

These have different number of columns - Say Table A has more columns.

它们有不同的列数——比如表A有更多的列。

How can I union these two table and get null for the columns that Table B does not have?

如何将这两个表合并并为表B没有的列获取null ?

3 个解决方案

#1

142

Add extra columns as null for the table having less columns like

为列较少的表添加额外的列作为null

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

#2

I came here and followed above answer. But mismatch in the Order of data type caused an error. The below description from another answer will come handy.

我来到这里，按照上面的回答做。但是数据类型的顺序不匹配导致了错误。下面的说明将派上用场。

Are the results above the same as the sequence of columns in your table? because oracle is strict in column orders. this example below produces an error:

上面的结果是否与表中的列序列相同?因为oracle在列顺序上是严格的。下面这个例子产生了一个错误:

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 same datatype as corresponding expression

ORA-01790:表达式必须具有与相应表达式相同的数据类型

As you see the root cause of the error is in the mismatching column ordering that is implied by the use of * as column list specifier. This type of errors 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 frequent scenario for this error is when you inadvertently swap (or shift) two or more columns in the SELECT list:

从test1_1790 union中选择col_a、col_b、col_c，从test2_1790中选择col_a、col_b、col_c;这个错误更常见的情况是当您不经意地交换(或转移)SELECT列表中的两个或多个列时:

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

OR if the above does not solve your problem, how about creating an ALIAS in the columns like this: (the query is not the same as yours but the point here is how to add alias in the column.)

或者，如果上面的方法不能解决您的问题，那么如何在列中创建一个别名呢?

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

#3

if only 1 row, you can use join

如果只有一行，可以使用join

Select t1.Col1, t1.Col2, t1.Col3, t2.Col4, t2.Col5 from Table1 t1 join Table2 t2;

#1

142