ITPub博客

首页 > 数据库 > Oracle > null的研究(第三发)

null的研究(第三发)

原创 Oracle 作者:stilllovekk 时间:2016-03-15 17:55:25 0 删除 编辑
null与sql
一个老生常谈的例子,两张表  t1,t2要查询t1表中不在t2表的数据。别的还行,如果是用not in,那么结果就不对了。
简单写得几个写法如下:
SQL> create table t1( a number,b number);


Table created.


SQL> create table t2( a number,b number);


Table created.


SQL> insert into t1 values(1,2);


1 row created.


SQL> insert into t1 values (3,4);


1 row created.


SQL> insert into t2 values(1,2);


1 row created.


SQL> insert into t2 values(null,4);


1 row created.


SQL> commit;


Commit complete.




SQL> select * from t1  where not exists(select 1 from t2  where t1.a = t2.a and t1.b = t2.b);


         A          B
---------- ----------
         3          4


SQL> select * from t1 where (a,b)not in (select a,b from t2);


no rows selected


SQL> select * from t1 minus select * from t2;


         A          B
---------- ----------
         3          4


SQL> SELECT t1.a,t1.b
  2  FROM t1
  3  LEFT JOIN t2
  4  ON t1.a   = t2.a
  5  AND t2.b  = t1.b
  6  where t2.b IS NULL
  7  AND t2.a IS NULL;


         A          B
---------- ----------
         3          4

可以发现,四种写法,只有not in没有查询出正确结果。
这是因为啥呢?如下:
select * from t1 where (a,b)not in (select a,b from t2);
这种写法等价于:
select * from t1 where (a,b) not in(1,2) and (a,b) not in (null,4)
继续往下拆分,还等价于:
select * from t1 where (a!=1 or b!=2) and (a!=null or b!=4)
这就是原因了,前面两篇博客中已经可以知道,a!=null返回的是null,当然查询就没有结果。
另外,在此说一下sql的写法,类似这种需求的写法,
not in   nest loop 一条一条的去比,数据量大的话很慢,not exists 情况类似,minus 会去重并且排序;
我最常用的是 /*+use_hash()*/+minus以及left join+is null的方法,数据量大的话,hash join是最适合的表链接方式,这是另一方面的基础,后续在说。

再进一步,


SQL> update t2 set b=5 where a is null;


1 row updated.


SQL> commit;


Commit complete.


SQL> select * from t2;

下面在执行一次not in 的写法:


         A          B
---------- ----------
         1          2
                    5



SQL>  select * from t1 where (a,b)not in (select a,b from t2);


         A          B
---------- ----------
         3          4
可以发现可以有结果了,原因看上面把这个sql分解成等价sql的过程,很好理解的。

把b表数据改回来,换几个写法:



SQL> update t2 set b=4 where a is null;


1 row updated.


SQL> commit;


Commit complete.


SQL> select * from t2;


         A          B
---------- ----------
         1          2
                    4
SQL> select * from t1 where (a,b) not in (select a,b from t2 where a is not null and b is not null);


         A          B
---------- ----------
         3          4

同时,集合和分组语句对null也是忽略的,唯一索引中,认为null也是不相等的,全null也无法进入索引,只是可以一直插入,过程见前面的博客。


关于null的排序,生序的话  ase   ,null是在最后,desc 降序的话,null是在最前面的。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30123160/viewspace-2057774/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-01-30

  • 博文量
    20
  • 访问量
    29406