ORACLE SQL中NOT IN与NULL值的困境

Question

当我使用这个代码时

WHEN col1 NOT IN (SELECT col2 FROM table_name) THEN 'something'

它知道col2包含NULL值，却没有给出预期的结果，为什么会发生这种情况？将IN与NULL值一起使用会扰乱存储在内存中的数据吗？

Answer 1

这不是Oracle的问题。SQL就是这样定义的。

当子查询返回具有NOT IN的NULL值时，则根本没有行匹配。因此，我强烈建议始终使用NOT EXISTS：

WHEN NOT EXISTS (SELECT 1 FROM bst WHERE x.n = bst.p)
THEN 'Leaf'

作为推论，我通常使用EXISTS而不是IN，尽管它没有这个问题。

为什么会出现这种情况？NULL意味着该值是未知的，而不是该值"丢失"或其他什么。

因此，如果所有元素都有值，这很容易计算：

1 NOT IN (1, 2)  --> false
3 NOT IN (1, 2)  --> true

但是：

1 NOT IN (1, 2, NULL)  --> false 
3 NOT IN (1, 2, NULL)  --> NULL, because NULL could be equal to "3"

基本上，如果任何值都是NULL，则NOT IN返回"false"或NULL。"false"和NULL在WHEN和WHERE中的处理相同。