UNION合并两张数据表后如何新增布尔字段标识数据所属源表

实现方案 #

你可以在两个查询语句中分别构造对应的布尔标识字段，合并后直接写入第三张表即可，这里更推荐用UNION ALL替代你原先的UNION，避免不必要的去重操作消耗性能，同时也能保留同一条数据同时存在于两张表时的来源差异。

情况1：表3尚未创建，直接创建并写入数据

适配MySQL/PostgreSQL/SQLite等支持CREATE TABLE AS语法的数据库：

CREATE TABLE table3 AS
SELECT 
  resto_name,
  city,
  TRUE AS on_table_one,
  FALSE AS on_table_two
FROM table1
UNION ALL
SELECT 
  resto_name,
  city,
  FALSE AS on_table_one,
  TRUE AS on_table_two
FROM table2;

适配SQL Server等支持SELECT INTO语法的数据库：

SELECT 
  resto_name,
  city,
  CAST(1 AS BIT) AS on_table_one,
  CAST(0 AS BIT) AS on_table_two
INTO table3
FROM table1
UNION ALL
SELECT 
  resto_name,
  city,
  CAST(0 AS BIT) AS on_table_one,
  CAST(1 AS BIT) AS on_table_two
FROM table2;

情况2：表3已经提前创建完成，直接插入数据

INSERT INTO table3 (resto_name, city, on_table_one, on_table_two)
SELECT 
  resto_name,
  city,
  TRUE,
  FALSE
FROM table1
UNION ALL
SELECT 
  resto_name,
  city,
  FALSE,
  TRUE
FROM table2;

内容的提问来源于stack exchange，提问作者Tenserflu