PostgreSQL使用IN/USING/EXISTS时含NULL列无法删除行的原因与解决方法

我们先创建并初始化一张表：

CREATE TABLE rows(
  a int NOT NULL,
  b int,
  c int
);
INSERT INTO rows(a, b, c) VALUES (1, 1, NULL), (2, NULL, 1), (3, 1, 1);

执行下面的删除语句时，无法删除目标行：

DELETE FROM rows WHERE (a, b, c) IN ((1, 1, NULL));
-- 执行结果：DELETE 0

但当匹配的列都没有NULL值时，这条查询能正常工作：

DELETE FROM rows WHERE (a, b, c) IN ((3, 1, 1));
-- 执行结果：DELETE 1

另外，下面这几个查询也遇到了同样的问题，执行后都没有删除任何行：

WITH to_delete AS (
    SELECT * FROM UNNEST (
        ARRAY[1, 2],
        ARRAY[1, NULL],
        ARRAY[NULL, 1]
    ) data(a, b, c)
)
DELETE FROM rows 
USING to_delete
WHERE
    rows.a = to_delete.a AND
    rows.b = to_delete.b AND
    rows.c = to_delete.c;
-- 执行结果：DELETE 0

WITH to_delete AS (
    SELECT * FROM UNNEST (
        ARRAY[1, 2],
        ARRAY[1, NULL],
        ARRAY[NULL, 1]
    ) data(a, b, c)
)
DELETE FROM rows 
WHERE EXISTS (
    SELECT 1 FROM to_delete
    WHERE
        to_delete.a = rows.a AND
        to_delete.b = rows.b AND
        to_delete.c = rows.c
)
-- 执行结果：DELETE 0

WITH to_delete AS (
    SELECT * FROM UNNEST (
        ARRAY[1, 2],
        ARRAY[1, NULL],
        ARRAY[NULL, 1]
    ) data(a, b, c)
)
DELETE FROM rows 
WHERE (a, b, c) IN (SELECT * FROM to_delete)
-- 执行结果：DELETE 0

只有当所有列都不含NULL值时，上述3个查询才能成功删除对应的行。

现在有两个问题：

1. 为什么这些SQL查询无法删除包含NULL列的行？
2. 如何解决这个问题？我的场景是用CTE获取需要删除的行，且这些行里总有一列值为NULL。

1. 无法删除含NULL列的行的原因 #

SQL里的NULL代表未知值，它遵循特殊的比较规则：

• 任何与NULL直接用=进行比较的操作，结果都不是TRUE，而是UNKNOWN
• IN、EXISTS这类条件判断，只会在结果为TRUE时才会匹配，UNKNOWN会被视为不匹配

拿第一个查询举例：(a, b, c) IN ((1, 1, NULL))本质上是比较(1,1,NULL) = (1,1,NULL)，其中最后一列的NULL = NULL结果是UNKNOWN，整个行比较的结果也是UNKNOWN，所以不会匹配到任何行，自然删除不了。

后面几个查询里的rows.c = to_delete.c这类条件，当其中一方是NULL时，比较结果同样是UNKNOWN，导致整个WHERE条件不成立，所以没有行被删除。

2. 解决方法 #

要处理NULL的比较，需要用IS NOT DISTINCT FROM替代=，这个运算符会把NULL和NULL视为相等。另外，对于行级比较，也可以用行级的IS NOT DISTINCT FROM。

方法一：修改行级比较条件 #

针对第一个查询，可以改成：

DELETE FROM rows WHERE (a, b, c) IS NOT DISTINCT FROM (1, 1, NULL);
-- 执行结果：DELETE 1

方法二：适配CTE的场景 #

对于用CTE获取待删除行的情况，把所有的=替换成IS NOT DISTINCT FROM即可：

方式1（USING子句）

WITH to_delete AS (
    SELECT * FROM UNNEST (
        ARRAY[1, 2],
        ARRAY[1, NULL],
        ARRAY[NULL, 1]
    ) data(a, b, c)
)
DELETE FROM rows 
USING to_delete
WHERE
    rows.a IS NOT DISTINCT FROM to_delete.a AND
    rows.b IS NOT DISTINCT FROM to_delete.b AND
    rows.c IS NOT DISTINCT FROM to_delete.c;
-- 执行结果：DELETE 2

方式2（EXISTS子句）

WITH to_delete AS (
    SELECT * FROM UNNEST (
        ARRAY[1, 2],
        ARRAY[1, NULL],
        ARRAY[NULL, 1]
    ) data(a, b, c)
)
DELETE FROM rows 
WHERE EXISTS (
    SELECT 1 FROM to_delete
    WHERE
        to_delete.a IS NOT DISTINCT FROM rows.a AND
        to_delete.b IS NOT DISTINCT FROM rows.b AND
        to_delete.c IS NOT DISTINCT FROM rows.c
)
-- 执行结果：DELETE 2

方式3（IN子句改IS NOT DISTINCT FROM）

或者把IN改成行级的IS NOT DISTINCT FROM结合ANY：

WITH to_delete AS (
    SELECT * FROM UNNEST (
        ARRAY[1, 2],
        ARRAY[1, NULL],
        ARRAY[NULL, 1]
    ) data(a, b, c)
)
DELETE FROM rows 
WHERE (a, b, c) IS NOT DISTINCT FROM ANY (SELECT (a,b,c) FROM to_delete);
-- 执行结果：DELETE 2

如果你的数据库不支持行级的IS NOT DISTINCT FROM，也可以手动针对每个列处理NULL：
比如把rows.b = to_delete.b改成(rows.b = to_delete.b OR (rows.b IS NULL AND to_delete.b IS NULL))，不过这种写法不如IS NOT DISTINCT FROM简洁。

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