Azure Synapse中EXCEPT与WHERE NOT IN结果差异问题排查

• NULL值的影响：这是导致两个查询结果差异的核心原因。当table_b的[value]列存在NULL值时，NOT IN子查询会受SQL三值逻辑（TRUE/FALSE/UNKNOWN）影响。因为value NOT IN (...)会与子查询的每一个值做比较，只要其中有一个NULL，整个比较结果就会变成UNKNOWN，而WHERE子句仅返回条件判定为TRUE的行，最终导致第一个查询无数据返回。
• EXCEPT的行为差异：EXCEPT运算符会将NULL视为相等的值来处理，不会因为NULL的存在导致整个结果集被过滤。另外EXCEPT默认会对结果去重，而NOT IN会保留table_a中的重复值（除非手动添加DISTINCT），不过你的场景里主要问题还是NULL的影响。

验证方法 #

可以执行以下查询确认table_b中是否存在NULL值：

SELECT COUNT(*) FROM table_b WHERE [value] IS NULL

解决方案 #

• 给NOT IN子查询添加NULL过滤条件：

SELECT [value] FROM table_a
WHERE [value] NOT IN (SELECT [value] FROM table_b WHERE [value] IS NOT NULL)

• 改用NOT EXISTS，它对NULL的处理逻辑更直观，不会出现类似NOT IN的问题：

SELECT [value] FROM table_a a
WHERE NOT EXISTS (SELECT 1 FROM table_b b WHERE a.[value] = b.[value])

• 若保留EXCEPT的使用，需注意其去重特性；如果需要保留table_a中的重复值，可以使用EXCEPT ALL：

SELECT [value] FROM table_a
EXCEPT ALL (SELECT [value] FROM table_b)

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