子查询中的NULL问题
子查询中的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是可以查找出来的。