主页 / coding / 问题 / 78013913
Accepted
Ilya Ordin
Asked: 2024-02-18 06:19:48 +0800 CST

为什么在使用 USING/IN/EXISTS 时，如果其中一列为 NULL，Postgres 不会删除行？[复制]

  • 772

假设我们有一张桌子：

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);

为什么 Postgres 不使用以下查询删除该行？

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

当每列不是 时，前 3 个查询成功删除行NULL。

所以有2个问题：

  1. 为什么这些 SQL 查询不删除其中一列所在的行NULL？
  2. 怎么解决呢？我使用 CTE 接收应删除的行。这些行中的一列始终是NULL。
sql

1 个回答

  1. Best Answer

    NULL仅当您使用时才能被检测到IS

    喜欢

    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);

    CREATE TABLE

    INSERT 0 3

    DELETE FROM rows WHERE a= 1 AND  b =  1 AND  C IS NULL;

    DELETE 1

    INSERT INTO rows(a, b, c) VALUES (1, 1, NULL)

    INSERT 0 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) OR (rows.a IS NULL AND to_delete.a IS NULL)) AND
    ((rows.b = to_delete.b) OR (rows.b IS NULL AND to_delete.b IS NULL)) AND
   ((rows.c = to_delete.c) OR (rows.c IS NULL AND to_delete.c IS NULL));

    DELETE 2

    小提琴

    • 2

相关问题