SQL左连接Table1与Table2时如何用CASE/IF识别未匹配ID生成Missing列

报错原因 #

IF(Table1.ID NOT IN Table2.ID, 1, 0) As Missing执行失败的核心原因是：NOT IN语法后必须跟显式值集合或者独立子查询结果，不能直接引用已关联表的列名。在左连接的查询上下文中直接写Table2.ID，数据库会将其识别为待解嵌的嵌套结构，因此触发unnest相关报错。

最简实现逻辑 #

完成两表按ID左连接的前提下，不需要额外做全表ID存在性比对：左连接未命中Table2匹配记录的行，所有来自Table2的字段都会返回NULL，直接判断Table2的关联主键是否为NULL即可生成标记列，该写法性能最优。

注意：判断Table2.ID IS NULL比判断业务字段（如示例中的Years）更严谨，可避免Table2中本身存在业务字段为NULL的匹配记录导致的误判。

写法1：IF函数（适用于MySQL、Hive、SparkSQL等支持IF函数的引擎） #

SELECT
  Table1.ID,
  Table1.Region,
  Table2.Years,
  IF(Table2.ID IS NULL, 1, 0) AS Missing
FROM Table1
LEFT JOIN Table2
  ON Table1.ID = Table2.ID;

写法2：CASE语句（兼容所有SQL标准引擎，通用性最强） #

如果使用PostgreSQL、SQL Server等不支持IF函数的数据库，用标准CASE写法即可：

SELECT
  Table1.ID,
  Table1.Region,
  Table2.Years,
  CASE WHEN Table2.ID IS NULL THEN 1 ELSE 0 END AS Missing
FROM Table1
LEFT JOIN Table2
  ON Table1.ID = Table2.ID;

示例运行结果 #

基于给出的测试数据，查询返回结果完全符合预期：

若坚持使用NOT IN逻辑实现，需要调整为子查询写法，该写法数据量大时性能远差于左连接判空方案，不推荐使用：

SELECT
  ID,
  Region,
  IF(ID NOT IN (SELECT DISTINCT ID FROM Table2), 1, 0) AS Missing
FROM Table1

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