Oracle数据库中关于null的各种坑

（一）如下两条sql执行结果

查询1：select 1 as id from dual d where null = null;
查询2：select 1 as id from dual d where 1 != null;
查询3：select 1 as id from dual d where null != null;
查询4：select 1 as id from dual d where null is null;
查询5：select 1 as id from dual d where 1 is not null;  
查询1、查询2、查询3没有结果,
查询4、查询5有结果；
也就是说a= null 和 a != null （包括null = null 以及 null !=null）结果都是false，
null只能用is null和is not null来做判断 
如null is null是true ,1 is not null 是true;

为了下面的操作我们先创建两张表并初始化一些数据

create table ta( id number(10), name varchar(20));
create table tb( id number(10), name varchar(20));

insert into ta (id, name) values (1, 'xiaohong');
insert into ta (id, name) values (2, 'xiaoming');
insert into ta (id, name) values (3, null);
insert into ta (id, name) values (4, 'gouzi');

insert into tb (id, name) values (1, 'xiaohong');
insert into tb (id, name) values (2, 'xiaoming');
insert into tb (id, name) values (3, null)

此时ta tb两表中的数据如下所示：

（二）关于in的坑

确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。

查询：select * from ta a where a.name in (select b.name from tb b);
查询结果如下图所示：

可以看出 ta中的name为null值的记录并未被查询出来。
假设条件a in (val1,val2,null) 可以将查询简单的理解为
a = val1 or a = val2 or a= null 
当ta中name值为null时，null =val1 or null =val2 or null = null通通返回false,是不成立的。
所以 ta中的name为null值的记录并未被查询出来。   

（三）关于not in的坑

查询  select * from ta a where a.name not in (select b.name from tb b);
查询结果为空，并没有将ta表中name 为‘gouzi’的记录查询出来。
假设条件 a not in (val1,val2,null) 可以将查询简单的理解为:
a != val1 and a != val2 and a != null
如果tb表中存在null值，对于ta表中的每条记录做如下判断
name != val1 and name != val2 and name != null
name != null肯定不成立 ,所以如果tb表中存在null值时，ta表将查询不出结果。
如果将上面查询变为：
select * from ta a where a.name not in (select b.name from tb b where b.name is not null);
此时查询结果如下图：
此时对于ta表中null值做如下判断 null != val1 and null != val2 and null != null
肯定不成立，所以结果中只有name值为gouzi的记录。

（四）关于exists的坑

指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。

查询语句：select * from ta a where exists (select 1 from tb b where a.name = b.name)
查询结果如下图：并没有将null查询出来
上面的查询可以简单的理解首先遍历ta表，然后取ta表记录逐条遍历tb表，当ta.name = tb.name 为true则返回ta表中这条记录，
再取ta表中下一条记录继续遍历ttb表。
当ta.name值为null时,逐条遍历tb表，ta.name = tb.name 为false则该条记录不能查出。

（五）关于not exists的坑

 查询语句： select * from ta a where not exists (select 1 from tb b where a.name = b.name);
 查询结果如下图：将ta表中name is null的记录查询出来。
 上面的查询可以简单理解为先遍历ta表，然后取ta表中记录去逐条遍历tb表 ，遍历完tb表所有记录后 a.name =b.name返回false，
 则返回ta记录；
 当ta表中name值为null时 去遍历tb表所有记录，最终返回false，则将ta表中null值这条记录返回。