sql - 连接表 (UNION ALL) 其中一个表缺少其中一列

Question

我正在尝试将 SQLite 数据库中的三个表组合成一个新的组合表。这三个表具有相同的列名，但第三个表缺少其中一列。这是我正在尝试的方式:

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
;

这仅在前两个表上执行此操作时有效，在添加第三个表时我收到消息:

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

有没有办法让 SQL 忽略丢失的列并在需要时将其保留为 NULL？

Answer 1

给第三张表添加一个NULL值

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
;

另外，不需要子查询

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