CODEWITHSUNDEEP
sqldatabasesqlite
Muhamed Al Khalil
Muhamed Al Khalil

Reputation: 357

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?

Upvotes: 1

Views: 631

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270201

I want to note that you don't need the subquery:

    CREATE TABLE combined AS
         SELECT col1, col2, col3 from table1
         UNION ALL
         SELECT col1, col2, col3 from table2
         UNION ALL
         SELECT col1, col2, NULL from table3;

In addition, you may find that a view is more suitable for your purposes than an actual table.

Upvotes: 0

David דודו Markovitz
David דודו Markovitz

Reputation: 44951

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

Upvotes: 2

Related Questions