NOT IN选择为NULL值 [英] NOT IN selection with NULL values

问题描述

谁可以解释第二选择的结果.我希望data1(1，'1')的第一行不在data2中吗?

Who can explain the result of the second select. I expected the first row of data1 (1, '1') is not in data2?

with data1(id, val) as
 (select 1, '1' from dual union all 
  select 2, '2' from dual),
     data2(id, val) as
 (select 1, 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) as
 (select 1, '1' from dual union all 
  select 2, '2' from dual),
     data2(id, val) as
 (select 1, null from dual union all 
  select 2, '2' from dual)

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

-- No Result ???

即第一行(1，'1')既不是IN数据2还是不是IN数据2?

I.e. first row (1,'1') is neither IN data2 nor NOT IN data2?

推荐答案

首先讲一点理论:

First a bit of theory: Null (SQL)

The most important parts for us from the above link:

与NULL和三值逻辑(3VL)的比较

由于Null不是任何数据域的成员，因此不将其视为 值"，而是表示缺少的标记(或占位符) 价值.因此，与Null进行比较永远不会导致 是或否，但总是在第三个逻辑结果中， 未知.[8]以下表达式的逻辑结果，用于比较 值为10的Null，是未知的:

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:

SELECT 10 = NULL       -- Results in Unknown

，以便两个比较:x = NULL和x <> NULL的计算结果均为NULL(未知).

so that both comparisons: x = NULL and x <> NULL evaluates to NULL(unknown).

SQL实现了三个逻辑结果，因此SQL实现必须 提供专门的三值逻辑(3VL).规则 下表中显示了控制SQL三值逻辑的方法(p和 q代表逻辑状态)"[9] SQL用于AND，OR的真值表， 而不是与Kleene和Łukasiewicz的共同片段相对应 三值逻辑(涵义的定义不同， 但是SQL并未定义此类操作.

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).

+---------+-------------+-------------+-------------+-----------+--------+
|    p    |        q    |     p OR q  |     p AND q |    p = q  |p != q  |
+---------+-------------+-------------+-------------+-----------+--------+
| True    |     True    |     True    |     True    |   True    | False  |
| True    |     False   |     True    |     False   |   False   | True   |
| True    |     Unknown |     True    |     Unknown |   Unknown | Unknown|
| False   |     True    |     True    |     False   |   False   | True   |
| False   |     False   |     False   |     False   |   True    | False  |
| False   |     Unknown |     Unknown |     False   |   Unknown | Unknown|
| Unknown |     True    |     True    |     Unknown |   Unknown | Unknown|
| Unknown |     False   |     Unknown |     False   |   Unknown | Unknown|
| Unknown |     Unknown |     Unknown |     Unknown |   Unknown | Unknown|
+---------+-------------+-------------+-------------+-----------+--------+

---

WHERE子句中的未知效果

SQL三值逻辑 (DML)作为DML语句和查询的比较谓词. WHERE子句使DML语句仅对这些行起作用 谓词的评估结果为True.

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

So in short: WHERE clause treats NULL as FALSE

现在，请考虑一个更简单的情况:

Now please consider a simpler case:

SELECT * FROM T1;

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

和查询:

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

上面的查询是该查询的一个短处:

The above query is a shortland to this one:

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

对于表t(x = NULL)的第二行，此条件如下:

For the second row from table t ( x = NULL) this condition looks like:

WHERE NULL = 1
   OR NULL = NULL

因此，行x=NULL的这种条件的计算结果为NULL，因为NULL=1为NULL，NULL=NULL为NULL，并且NULL OR NULL也为NULL(请参见上面的表3VL).

so this condition for the row x=NULL evaluates to NULL because NULL=1 is NULL, NULL=NULL is NULL, and NULL OR NULL is also NULL (please see the table 3VL above).

现在考虑更奇怪的情况:

Now consider more curious case:

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

此子句x NOT IN (1, NULL)等效于NOT ( x IN (1, NULL) )
所以也等同于:

This clause x NOT IN (1, NULL) is equivalent to NOT ( x IN (1, NULL) )
so it is also equivalent to:

NOT (
  x = 1
  OR
  x = NULL
)

，并且根据迪摩根定律，它等同于:

and according to De Morgan's laws it is equivalent to:

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

并且(如果我们将NOT x = y替换为x <> y)，它也等同于:

and (if we replace NOT x = y with x <> y) it's also equivalent to:

 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.

因此，查询条件如下:

We know than x <> NULL always evaluates to NULL. We also know from the 3VL table above, that both true AND NULL is NULL and false AND NULL evaluates to FALSE, so the whole condition always evaluates either to FALSE or NULL, but it never evaluates to TRUE.

Therefore a query with this condition:

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

总是返回空结果集

现在您的查询也很好奇:

And now your query, which is also curious:

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

可以将其重写(使用常量值):

which can be rewriten (using constant values) to:

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

此查询使用的是所谓的行值表达式

基本上是这样的条件，使用行值expressin

This query is using so called row value expression

Basically a condition using the row value expressin like this

(a, b) = (x, y)

与此等效:

a = x AND b = y

因此上述查询可以重写为以下查询:

so the above query can be rewritten into this one:

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

根据De Morgan的法律，这等同于:

According to De Morgan's laws this is identical to:

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的情况下才为true(请参见上面的3VL表)，因此该条件可以简化为:

Since the first part ( id <> 1 OR val <> NULL ) of the condition evaluates to true only in a case where id <> 1 (please see the 3VL table above), this condition can be simplified into:

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

进一步(根据De Morgan的法律)为:

and further (according to De Morgan's laws) into:

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

，因此来自源data1的(1,1)和(2,2)均不符合这些条件.

so neither (1,1) nor (2,2) from the source data1 comply with these conditions.

这篇关于NOT IN选择为NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！