子查询中的NULL问题

 子查询返回有单行，多行和null值；适用于单行子查询的比较运算符是=，>,>=,<,<=<>和!=。适用于多行子查询的比较运算符是in,not in,any和any。在子查询中不可回避的是会返回null值，下面就这个问题来讨论一下。

在单行子查询中

SQL> select last_name,department_id from  employees where department_id is null;
LAST_NAME                 DEPARTMENT_ID
-------------------------                 -------------
Grant

先来查看一下employees表中的department_id，它有个null值。

SQL> select last_name,department_id from  employees 
   where department_id=(select department_id from employees where last_name='Haus');
no rows selected

在employees表中没有一行的last_name是等于Haus的，故子查询返回null值。由上面的查询知道department_id是有null值的，那没有显示呢？

这是因为任何运算（加减乘除、字符串连接等）的运算数中包括NULL值时，整个表达式的值即为NULL。使用单行函数对NULL值进行处理，得到的结果也为NULL（NVL等函数除外）。

 

2.在多行子查询中

SQL> select employee_id,manager_id from employees   where manager_id is null;
EMPLOYEE_ID   MANAGER_ID
----------- ---          -------
        100

先来查看一下employees表中的manager_id,它有个null值。

 

SQL> select  last_name  from employees  where  employee_id  not in (select manager_id from
employees );
no rows selected

那这个为什么也没有显示？我们来先看一下in

SQL> select  last_name  from employees emp where emp.employee_id  in (select manager_id from employees);
LAST_NAME
-------------------------
Cambrault
De Haan
Errazuriz
Fripp

这里用in可以显示了。这个原因是因为not in 和in本质上都是OR运算，但是计算逻辑OR时处理NULL的方式不同，产生的结果也不同。

 

为了说明上面的问题，我们来看一下NULL在布尔预算中的规则：

        由于NULL是未知，所以NULL AND NULL、NULL OR NULL、NULL AND TRUE和NULL OR FALSE的值都是未知的，这些的结果仍然是NULL。那么为什么NULL AND FALSE和NULL OR TRUE得到了一个确定的结果呢？仍然从NULL的概念来考虑。NULL是未知的，但是目前NULL的类型是布尔类型，因此NULL只有可能是TRUE或者FALSE中的一个。所以NULL AND FALSE的结果是FALSE,NULL OR TRUE的结果是TRUE。

 

 

有了这个我们就能解释上面的问题了，对于

select  last_name  from employees  where  employee_id  not in (select manager_id from
employees );

子查询select manager_id from employees会产生很多值而且有一个NULL值，它们之间是OR的关系，假设manager_id是10,11和NULL，那么可以把上面的句子改为：

select  last_name  from employees  where  employee_id  not in （10,11，null);

其中where  employee_id  not in （10,11，null)等价于where no (employee_id=10 or employee_id=11 or employee_id=null)，拿employee_id=12来说明一下。

not (12=10 or 12=11 or 12=null)

not(false or false or null) (OR运算，null不确定，所以整个表达式为null)

not null （这是布尔运算中，NULL表示的是未知的含义，而增加一个NOT操作后，并不能使NULL变为一个确定的值，如果是TRUE，NOT TRUE将变为FALSE，如果是FALSE，NOT FALSE将变为TRUE，值不确定，所以为null)

null

所以结果显示为no rows selected

 

对于

select  last_name  from employees emp where emp.employee_id  in (select manager_id from employees);

按照上面的方法，假设manager_id是10,11和NULL，则句子改为：

select  last_name  from employees  where  employee_id  not in （10,11，null);

其中where  employee_id  in （10,11，null)等价于where  (employee_id=10 or employee_id=11 or employee_id=null)，拿employee_id=10来说明一下。

(10=10 or 10=11 or 10=null)

(true or false or null)(OR运算有一真则表达式为真）

true

所以in是可以查找出来的。