今天在写实验时,发现一下SQL语句没有达到预期结果:
select distinct au_lname, state
from authors
where state not in
(
select state from publishers
);
上网搜寻,发现大家跟我写的语句不一样,以下为网上写的语句(同样的查询需求):
select distinct au_lname, state
from authors
where state not in
(
select state from publishers
where state is not null
);
这我就想不明白了:为什么子查询中的null值会导致not in失效呢?找了很久也没怎么有头绪。
后来一篇博客给我了启示,发现null值有以下特点:
NULL不能进行如何的“操作”
- 如果null参与算术运算,则该算术表达式的值为null。(例如:+,-,*,/)
- 如果null参与比较运算,则结果可视为false。(例如:>=,<=,<>等)
- 如果null参与聚集运算,则聚集函数都置为null。除count(*)之外。
- 如果在not in子查询中有null值的时候,则不会返回数据
为什么sql里面not in后面的子查询如果有记录为NULL的,主查询就查不到记录?原因如下:
select *
from TableA as A
where a.id not in ( 2, null );
--等同于:
select *
from TableA as A
where a.id <> 2
and a.id <> null
由于NULL值不能参与比较运算符,导致条件不成立,查询不出来数据。