SQL 查询的 NULL NOT IN (Empty_Relation) 在不同引擎上显示不同的行为 [英] NULL NOT IN (Empty_Relation) of SQL query shows different behaviors on different engines

问题描述

我尝试在 Postrgresql、Spark 上测试 NULL NOT IN Empty_Relation 的查询，但得到了不同的结果.

I try a query which test NULL NOT IN Empty_Relation on Postrgresql, Spark and I got different results.

select count(*) from
(select 1)
where null not in
(a empty relation)

Postgresql 输出 1.其他输出0.

Postgresql outputs 1. The other outputs 0.

我理解 NOT IN 的 NULL 行为，但我的子查询是空关系，这种情况似乎更有趣.有很多帖子讨论 NOT IN，但我没有找到任何与 NOT IN Empty_Relation 相关的内容.

I understand the NULL behaviour of NOT IN, but my the subquery is empty relation, this situation seems more interesting. There are a lot of posts discussing NOT IN but I don't find anything related to NOT IN Empty_Relation.

所以我的问题更像是 ANSI SQL 是否定义了这种行为，或者这实际上是一个灰色区域，两个答案都可以接受.

So my question is more like does ANSI SQL define this behavior or this is actually a grey area, both answers could be accepted.

推荐答案

tl;dr: PostgreSQL 是正确的.

tl;dr: PostgreSQL is correct.

这是 SQL 规范对这种行为的说明:

This is what the SQL specification says about this behavior:

4) 表达式 RVC NOT IN IPV 等价于 NOT ( RVC IN IPV )

4) The expression RVC NOT IN IPV is equivalent to NOT ( RVC IN IPV )

5) 表达式 RVC IN IPV 等价于 RVC = ANY IPV

5) The expression RVC IN IPV is equivalent to RVC = ANY IPV

所以，NULL NOT IN () 等价于 NOT (NULL = ANY ())

接着说:

R 的结果<量词>T 是通过应用隐含的 R 导出的.RT到T中的每一行RT.

The result of R <comp op> <quantifier> T is derived by the application of the implied <comparison predicate> R <comp op> RT to every row RT in T.

[...]

d) 如果 T 为空或如果 对于 T 中的每一行 RT 隐含的 为 False，然后 R <一些>T 是错误的.

d) If T is empty or if the implied <comparison predicate> is False for every row RT in T, then R <comp op> <some> T is False.

(注意:<some> 要么是 ANY 要么是 SOME —— 它们的意思相同).

(Note: <some> is either ANY or SOME -- they both mean the same).

根据这个规则，由于 T 为空，NULL = ANY () 为假，所以 NOT (NULL = ANY (<;空关系>)为真.

By this rule, since T is empty, NULL = ANY (<empty>) is False, so NOT (NULL = ANY (<empty relation>) is True.

这篇关于SQL 查询的 NULL NOT IN (Empty_Relation) 在不同引擎上显示不同的行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！