Join two tables with different number of columns

Question

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

They have a different number of columns — say, table A has more columns.

How can we combine these two tables and get a NULL value for columns that are not in table B?

Answer 1

Add extra columns as null for a table with fewer columns, e.g.

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

Answer 2

I came here and followed him. But the inconsistency in the "Data Order" type caused an error. Below is a description from another answer.

Are the results higher than the sequence of columns in your table? because the oracle is strict in column orders. this example below results in 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 main cause of the error is to streamline the column mismatch, which is implied by using * as the column list specifier. This type of error can be easily avoided by explicitly entering a list of columns:

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 scenario for this error is when you accidentally swap (or shift) 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; 

OR if the above does not solve your problem, how about creating ALIAS in columns like this: (the request does not match yours, but in this case it is about how to add an alias to 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 

Answer 3

if only 1 line, you can use join

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

Answer 4

Usually you need to have the same number of columns when you use set-based operators so that Kangkan's answer is correct.

SAS SQL has a special statement to handle this scenario:

SAS (R) 9.3 SQL User Guide

CORRESPONDING (CORR) Keyword

The CORRESPONDING keyword is used only when the set statement is specified. CORR forces PROC SQL to match columns in table expressions by name rather than ordinal. Columns that do not match in name are excluded from the result table, with the exception of the OUTER UNION statement.

 SELECT * FROM tabA OUTER UNION CORR SELECT * FROM tabB; 

Behind:

 +---+---+ | a | b | +---+---+ | 1 | X | | 2 | Y | +---+---+ OUTER UNION CORR +---+---+ | b | d | +---+---+ | U | 1 | +---+---+ <=> +----+----+---+ | a | b | d | +----+----+---+ | 1 | X | | | 2 | Y | | | | U | 1 | +----+----+---+ 

---

U-SQL supports a similar concept:

OUR UNION BY NAME ON (*)

EXTERNAL

Requires BY NAME clause and ON list. Unlike other set expressions, the OUTER UNION output schema includes both matching columns and mismatched columns on both sides. This creates a situation where in each row coming from one of the sides there are “missing columns” that are present only on the other side. For such columns, default values ​​are provided for "missing cells." The default values ​​are zero for types that allow nulls, and the default values ​​.Net for types that do not allow nulls (for example, 0 for type int).

BY NAME

required when used with OUTER. The sentence indicates that the join does not match the values ​​based on the position, but by the name of the columns. If the BY NAME clause is not specified, the matching is performed positionally.

If the ON clause includes the character "*" (it can be specified as the last or only element of the list), then additional names are allowed to match except those specified in the ON clause, and the result columns include all the corresponding columns in the Order of their presence in the left argument .

And the code:

 @result = SELECT * FROM @left OUTER UNION BY NAME ON (*) SELECT * FROM @right; 

---

EDIT:

The concept of an outer join is supported by KQL :

Kind:

inner - the result has a subset of the columns that are common to all input tables.

external - the result has all the columns that occur in any of the input data. Cells that were not defined by the input string are set to zero.

Example:

 let t1 = datatable(col1:long, col2:string) [1, "a", 2, "b", 3, "c"]; let t2 = datatable(col3:long) [1,3]; t1 | union kind=outer t2; 

demonstration