oracle – 不使用NULL值进行选择

with data1(id,val) as
 (select 1,'1' from dual union all 
  select 2,'2' from dual),data2(id,null from dual union all 
  select 2,'2' from dual)

select id,val
  from data1
 where (id,val) IN (select id,val from data2);

-- Result (as expected):
-- id,val
-- 2   '2'

with data1(id,val) NOT IN (select id,val from data2)

-- No Result ???

即第一行(1,’1′)既不是IN数据2也不是非IN数据2？

以上链接为我们提供的最重要的部分：

Comparisons with NULL and the three-valued logic (3VL)

Since Null is not a member of any data domain,it is not considered a
“value”,but rather a marker (or placeholder) indicating the absence
of value. Because of this,comparisons with Null can never result in
either True or False,but always in a third logical result,
Unknown.[8] The logical result of the expression below,which compares
the value 10 to Null,is Unknown:

06000

这样两个比较：x = NULL和x<> NULL计算为NULL(未知).

SQL implements three logical results,so SQL implementations must
provide for a specialized three-valued logic (3VL). The rules
governing SQL three-valued logic are shown in the tables below (p and
q represent logical states)”[9] The truth tables SQL uses for AND,OR,
and NOT correspond to a common fragment of the Kleene and ?ukasiewicz
three-valued logic (which differ in their definition of implication,
however SQL defines no such operation).

06001

Effect of Unknown in WHERE clauses

SQL three-valued logic is encountered in Data Manipulation Language
(DML) in comparison predicates of DML statements and queries. The
WHERE clause causes the DML statement to act on only those rows for
which the predicate evaluates to True.

简而言之：WHERE子句将NULL视为FALSE

现在请考虑一个更简单的案例：

SELECT * FROM T1;

|      X |
|--------|
|      1 |
| (null) |

和查询：

SELECT * FROM t1 WHERE x IN (1,NULL);

以上查询是这一个的简称：

SELECT * FROM t1 
WHERE x = 1
  OR  x = NULL

对于表t中的第二行(x = NULL),此条件如下所示：

WHERE NULL = 1
   OR NULL = NULL

所以行x = NULL的条件求值为NULL,因为NULL = 1为NULL,NULL = NULL为NULL,NULL OR NULL也为NULL(请参见上面的表3VL).

现在考虑更奇怪的情况：

SELECT * FROM t1 WHERE x NOT IN (1,NULL);

该子句x NOT IN(1,NULL)等效于NOT(x IN(1,NULL))
所以它也相当于：

NOT (
  x = 1
  OR
  x = NULL
)

根据De Morgan’s laws,它相当于：

NOT ( x = 1 ) AND NOT ( x = NULL )

(如果我们用x<&y; y替换NOT x = y),它也等同于：

x <> 1 AND x <> NULL

请仔细查看最后一个条件：

WHERE 
x <> 1 AND x <> NULL

我们知道比x<> NULL始终求值为NULL.我们也从上面的3VL表中知道,true AND NULL都是NULL而false AND NULL的计算结果为FALSE,因此整个条件总是计算为FALSE或NULL,但它永远不会计算为TRUE.
因此具有以下条件的查询：

SELECT .....
WHERE x NOT IN ( NULL,whatever)

始终返回空结果集

现在你的查询,也很好奇：

SELECT * FROM t1
WHERE (id,val from data2);

可以重写(使用常量值)来：

SELECT * FROM t1
WHERE (id,val) NOT IN (
       (1,null),(2,2 )
)

此查询使用所谓的row value expression
基本上是使用行值表达的条件,如下所示

(a,b) = (x,y)

相当于这一个：

a = x AND b = y

所以上面的查询可以重写为这个：

SELECT * FROM t1
WHERE NOT (
   id = 1 AND val = NULL
   OR
   id = 2 AND val = 2
)

根据De Morgan的定律,这与以下内容相同：

SELECT * FROM t1
WHERE 
   NOT ( id = 1 AND val = NULL )
   AND
   NOT ( id = 2 AND val = 2 )

并进一步：

SELECT * FROM t1
WHERE 
   ( id <> 1 OR val <> NULL )
   AND
   ( id <> 2 OR val <> 2 )

由于条件的第一部分(id<> 1 OR val<> NULL)仅在id<>的情况下评估为真. 1(请参见上面的3VL表),这个条件可以简化为：

SELECT * FROM t1
WHERE 
   ( id <> 1 )
   AND
   ( id <> 2 OR val <> 2 )

并进一步(根据德摩根的法律)：

SELECT * FROM t1
WHERE 
   id <> 1 AND id <> 2
   OR
   id <> 1 AND  val <> 2

因此源数据1中的(1,1)和(2,2)都不符合这些条件.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!