sql - WHERE 子句谓词可以评估为 NULL 吗？

Question

WHERE 子句可以返回 NULL 而不是 TRUE 或 FALSE 吗？
根据下面的练习，这是可能的，但我无法想象一个返回 NULL 的示例，这真的可能吗？

4. Which of the following values can NOT be returned after evaluation of WHERE clause
condition?
A.  UNKNOWN
B.  TRUE
C.  FALSE
D.  NULL
Answer: A. If the result of the condition in WHERE clause is not known, NULL is returned. In all
other scenarios, either TRUE or FALSE is returned.

Answer 1

在 SQL 中，所有逻辑运算符的计算结果为 TRUE、FALSE 和 UNKNOWN(Oracle docs)，而 MySQL UNKNOWN 结果调用 NULL(MySQL docs)。

根据oracle文档:

"To test for nulls, use only the comparison conditions IS NULL and IS NOT NULL. If you use any other condition with nulls and the result depends on the value of the null, then the result is UNKNOWN."

所以评估后只能返回 TRUE、FALSE 和 UNKNOWN。

关于你的问题:

"Can a WHERE clause return NULL instead of TRUE or FALSE?"

严格来说，在 Oracle 中 - 否 因为这样的结果叫做 UNKNOWN。

但总的来说，UNKNOWN 和 NULL 的含义在这种情况下是等效的，它只是同一事物的不同名称。
因此，下面的 SQL 示例 ( a.a >= all ) 评估为 UNKNOWN。

with table_a as (
select null as a from dual
union all 
select 10 as a from dual
union all 
select 5 as a from dual),
table_b as (
select null as a from dual
union all 
select 10 as a from dual
union all 
select 5 as a from dual)

select * from table_a a where a.a >= all(select a from table_b b)