Unioning two tables with different number of columns

Question

I have two tables  Table A and Table B    These have different number of columns - Say Table A has more columns   How can I union these two table and get null for the columns that Table B does not have

User · Accepted Answer

Add extra columns as null for the table having less columns like  Select Col1  Col2  Col3  Col4  Col5 from Table1 Union Select Col1  Col2  Col3  Null as Col4  Null as Col5 from Table2

User · Answer

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   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  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   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

User · Answer

Normally you need to have the same number of columns when you re using set based operators so Kangkan s answer is correct  SAS SQL has specific operator to handle that scenario   SAS R  9 3 SQL Procedure User s Guide CORRESPONDING  CORR  Keyword The CORRESPONDING keyword is used only when a set operator is specified  CORR causes PROC SQL to match the columns in table expressions by name and not by ordinal position  Columns that do not match by name are excluded from the result table  except for the OUTER UNION operator   SELECT   FROM tabA OUTER UNION CORR SELECT   FROM tabB   For   --- ---    a   b    --- ---    1   X     2   Y    --- ---   OUTER UNION CORR   --- ---    b   d    --- ---    U   1    --- ---    lt   gt    ---- ---- ---    a    b    d    ---- ---- ---     1   X           2   Y               U    1    ---- ---- ---    U-SQL supports similar concept   OUTER UNION BY NAME ON     OUTER requires the BY NAME clause and the ON list  As opposed to the other set expressions  the output schema of the OUTER UNION includes both the matching columns and the non-matching columns from both sides  This creates a situation where each row coming from one of the sides has  quot missing columns quot  that are present only on the other side  For such columns  default values are supplied for the  quot missing cells quot   The default values are null for nullable types and the  Net default value for the non-nullable types  e g   0 for int   BY NAME is required when used with OUTER  The clause indicates that the union is matching up values not based on position but by name of the columns  If the BY NAME clause is not specified  the matching is done positionally  If the ON clause includes the         symbol  it may be specified as the last or the only member of the list   then extra name matches beyond those in the ON clause are allowed  and the result   s columns include all matching columns in the order they are present in the left argument   And code   result           SELECT   FROM  left     OUTER UNION BY NAME ON          SELECT   FROM  right    EDIT  The concept of outer union is supported by KQL   kind  inner - The result has the subset of columns that are common to all of the input tables  outer - The result has all the columns that occur in any of the inputs  Cells that were not defined by an input row are set to null   Example  let t1   datatable col1 long  col2 string     1   quot a quot     2   quot b quot   3   quot c quot    let t2   datatable col3 long   1 3   t1   union kind outer t2   Output   ------ ------ ------    col1   col2   col3    ------ ------ ------       1   a                  2   b                  3   c                                1                      3    ------ ------ ------   demo

User · Answer

for any extra column if there is no mapping then map it to null like the following SQL query Select Col1  Col2  Col3  Col4  Col5 from Table1 Union Select Col1  Col2  Col3  Null as Col4  Null as Col5 from Table2

User · Answer

if only 1 row  you can use join  Select t1 Col1  t1 Col2  t1 Col3  t2 Col4  t2 Col5 from Table1 t1 join Table2 t2

[sql] Unioning two tables with different number of columns

Examples related to sql

Examples related to mysql