Oracle 怎么选为null,oracle – 不使用NULL值进行选择

首先是一点理论：

Null (SQL)

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

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

)

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

(如果我们用x

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)都不符合这些条件.