Muhamed Al Khalil Muhamed Al Khalil - 19 days ago 5
SQL Question

Concatenate tables (UNION ALL) where one of the tables lacks one of the columns

I am trying to combine three tables in an SQLite database into one new combined table. The three tables have the same column names, but the third table is missing one of the columns. Here is how I am trying to do it:

CREATE TABLE cobmined
AS
SELECT col1, col2, col3
FROM
(
SELECT col1, col2, col3 from table1
UNION ALL
SELECT col1, col2, col3 from table2
UNION ALL
SELECT col1, col2 from table3
) s
;


This works when doing this only on the first two tables, when adding the third table I get the message:

SELECTs to the left and right of UNION do not have the same number of result columns


Is there a way to let SQL ignore the missing column and leave it with NULLs if needed?

Answer

Add a NULL value to the third table

CREATE TABLE cobmined
AS
SELECT col1, col2, col3
FROM
   (
     SELECT col1, col2, col3 from table1
     UNION ALL
     SELECT col1, col2, col3 from table2
     UNION ALL
     SELECT col1, col2, null from table3
    ) s
;

Also, no need for sub-query

CREATE TABLE cobmined
AS
SELECT col1, col2, col3 from table1
UNION ALL
SELECT col1, col2, col3 from table2
UNION ALL
SELECT col1, col2, null from table3