postgresql Postgres NOT IN (null) 没有结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17877100/
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
Postgres NOT IN (null) gives no result
提问by wutzebaer
I'm using Postgres with this query
我在这个查询中使用 Postgres
select
*
from Entity this_
where
(this_.ID not in (null))
Why does this give me no results? I would expect to get all rows where id is not null
为什么这没有给我任何结果？我希望获得 id 不为空的所有行
with
和
(this_.ID not in (1))
i get the expected results
我得到了预期的结果
回答by Clodoaldo Neto
The result of [not] in (null)will always be null. To compare to null you need is [not] nullor is [not] distinct from null
的结果[not] in (null)将始终为空。要与 null 进行比较，您需要is [not] null或is [not] distinct from null
select *
from Entity this_
where this_.ID is not null
If you want where (ID not in (1,null))as in your comment you can do
如果你想where (ID not in (1,null))在你的评论中，你可以做
where ID is not null and ID not in (1)
回答by AndreaBoc
PostgreSQL uses NULL as undefined value.
What you're asking is to return the items that are not in a list or an undefined value. Since undefined means that you do not know what's inside, PostgreSQL does not return any item because simply can not respond to the request.
While the request:
PostgreSQL 使用 NULL 作为未定义的值。
您要求的是返回不在列表或未定义值中的项目。由于 undefined 意味着你不知道里面有什么，PostgreSQL 不会返回任何项目，因为根本无法响应请求。
虽然请求：
select * from Entity where id in (1, null)
can return the records, because if it finds an element with ID = 1 knows that is in the collection
the request:
可以返回记录，因为如果它找到一个 ID = 1 的元素就知道它在集合中
，请求：
select * from Entity where (ID not in (1, null))
can not be satisfied because the null value can be any value.
不能满足，因为空值可以是任何值。
回答by Misha
select *
from Entity this_
where (this_.ID not in (null))
"IN" or "NOT IN" do not select NULL values You can write
“IN”或“NOT IN”不要选择NULL值 可以写
select *
from Entity this_
where (this_.ID not in (1))
And your selection will not contains null values
并且您的选择不会包含空值
回答by Ktt
I have had similar inproblem and eventually came up with the following solution;
我遇到了类似的问题，最终想出了以下解决方案；
select * from public."Employee_11" where (COALESCE("Name",'@'),"Surname")
in (
('@','dummy')
)
It does return the records which Namecolumn has null values. You can also use this for not inclause which will return not null records of Name;
它确实返回Name列具有空值的记录。您也可以将其用于not in子句，它将返回Name 的非空记录；
select * from public."Employee_11" where (COALESCE("Name",'@'),"Surname")
not in (
('@','dummy')
)
回答by BB23850
I had similar problem. My ego that I knew SQL well, got punctured. Here is the simplified example from scott/tiger tables.
我有类似的问题。我对 SQL 很了解的自负被刺穿了。这是来自 scott/tiger 表的简化示例。
select empno, ename from emp where deptno not in (10, 20, null);
Returned nothing. Although I use NOT IN condition very sparingly because it does not use index and is very slow. I prefer to use OUTER JOIN instead.
什么都没回。虽然我非常谨慎地使用 NOT IN 条件，因为它不使用索引并且非常慢。我更喜欢使用 OUTER JOIN 代替。
I tried this query in Postgres and Oracle both and results are the same. So, must be a standards compliant result. NULL behaves this way only in NOT IN condition.
我在 Postgres 和 Oracle 中都试过这个查询，结果是一样的。所以，一定是符合标准的结果。NULL 仅在 NOT IN 条件下以这种方式运行。
回答by Ahmet Erkan ?EL?K
You can use <> ANY operator. Example on your code:
您可以使用 <> ANY 运算符。您的代码示例：
select
*
from Entity this_
where
(this_.ID <> ANY (null))
