null的研究（第三发）

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30123160/viewspace-2057774/