SQL NOT IN 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2187780/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL NOT IN Clause
提问by rs.
I have a query which is not working as expected
我有一个查询没有按预期工作
Q1:
SELECT id, name
FROM vw_x
WHERE id NOT IN (select pid from table_x)
GROUP BY id, name
Having max(c_date) > GETDATE()
Q2:
SELECT id, name
FROM vw_x
GROUP BY id, name
Having max(c_date) > GETDATE()
Q1 is not returning anything even though i know those ids are not in table_x Q2 runs correctly without NOT IN
即使我知道这些 id 不在 table_x 中，Q1 也没有返回任何内容 Q2 在没有 NOT IN 的情况下正确运行
What could be wrong with my query?
我的查询可能有什么问题？
回答by SQLMenace
you have a NULL value in the table
你在表中有一个 NULL 值
try this
尝试这个
SELECT id, name
FROM vw_x
WHERE id NOT IN (select pid from table_x where pid is not null)
GROUP BY id, name
Having max(c_date) > GETDATE()
or this
或这个
SELECT id, name
FROM vw_x
WHERE NOT EXISTS (select 1 from table_x where pid = vw_x.id )
GROUP BY id, name
Having max(c_date) > GETDATE()
See also Select all rows from one table that don't exist in another table
回答by Russ Bradberry
what about using a left join?
使用左连接怎么样？
SELECT id, name
FROM vw_x
LEFT JOIN table_x on id = pid
WHERE pid IS NULL
GROUP BY id, name
Having max(c_date) > GETDATE()
回答by user1706373
There is another situation: the subquery may return nothing. SQL Server does not work as expected if the NOT IN clause returns a null list. I have a query like the following:
还有另一种情况：子查询可能什么都不返回。如果 NOT IN 子句返回空列表，SQL Server 不会按预期工作。我有如下查询：
select * from table where id not in (select id from tableB where somecondition(x))
When the subquery contains a list of ids, the query will return the data as expected. But when the subquery returns nothing, the query will still return data, but then becomes stuck.
当子查询包含 id 列表时，查询将按预期返回数据。但是当子查询什么都不返回时，查询仍然会返回数据，但会卡住。
I changed the query to the following and solved the problem:
我将查询更改为以下内容并解决了问题：
select * from table where id not in (select id from tableB where somecondition(x) **union all select 0**)
which makes sure the subquery will contain at least one number.
这确保子查询将包含至少一个数字。
