我遇到了一个奇怪的问题，使用 NOT IN 对 PostgreSQL 数据库运行查询，不明白为什么它不起作用

Question

我有一个带有id和parent_id列的分层表，parent_id有一个id列的外键。每一行只能有一个父行，但多行可以绑定到同一个父级。我想检索所有没有子项的行。

我尝试使用以下查询

SELECT *
FROM table
WHERE id NOT IN (
SELECT DISTINCT(parent_id)
FROM table
)

这返回了0行如果我将NOT IN更改为IN，它将正确返回具有子行的行(其他行通过其parent_id与其绑定(

我最终得到了这个工作：

SELECT *
FROM table
WHERE id NOT IN(
SELECT id
FROM table
WHERE id IN (
SELECT DISTINCT(parent_id)
FROM table
)
)

但我不明白为什么第一个查询不起作用？有人能帮我了解一下这里发生了什么吗？我不明白not IN应该如何工作吗？

Answer 1

尝试

SELECT *
FROM table
WHERE id NOT IN (
SELECT DISTINCT(parent_id)
FROM table
WHERE parent_id IS NOT NULL
)

例如：

with t(x) as (values(1),(2))
select 3
where 3 not in (select x from t);
┌──────────┐
│ ?column? │
├──────────┤
│        3 │
└──────────┘

但是

with t(x) as (values(1),(2),(null))
select 3
where 3 not in (select x from t);
┌──────────┐
│ ?column? │
├──────────┤
└──────────┘

这是因为DBMS不能决定是否是id = null(结果是undefined(

您可以如上所述或使用not exists:进行修复

with t(x) as (values(1),(2),(null))
select 3
where not exists (select x from t where x = 3);
┌──────────┐
│ ?column? │
├──────────┤
│        3 │
└──────────┘