(一)如下两条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值这条记录返回。