sys@ORCL> select * from v$version where rownum=1;

BANNER
----------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

sys@ORCL> !uname -a
Linux localhost.localdomain 2.6.18-308.el5xen #1 SMP Fri Jan 27 17:59:00 EST 2012 i686 i686 i386 GNU/Linux

    ① 单列和复合列NOT IN 子查询

    not in (........)里面的null，如果存在null，则返回的绝对是空值。因为，in本是或的关系，加上not，则任何值和null，逻辑与，其结果都是空。

    测试：

    --Q1;单列not in子查询有null分析

        hr@ORCL> drop table test1;
        hr@ORCL> drop table test2;
        hr@ORCL> create table test1 (id number);
       
        Table created.
       
        hr@ORCL> create table test2 (id number);
       
        Table created.
       
        hr@ORCL> insert into test1 values(1);
       
        1 row created.
       
        hr@ORCL> insert into test1 values(2);
       
        1 row created.
       
        hr@ORCL> insert into test2 values(null);
       
        1 row created.
       
        hr@ORCL> insert into test2 values(1);
       
        1 row created.
       
        hr@ORCL> commit;
       
        Commit complete.

      --需求：选出在test1、却不在test2的记录
      --单列、常见错误如下、没有结果：

        hr@ORCL> select id from test1 where id not in (select id from test2);
       
        no rows selected

      --正确的写法、常见的还是not exists

        hr@ORCL> select id from test1 where not exists (select 1 from test2 where test1.id=test2.id);
       
                ID
        ----------
                 2

    --Q2：复合列not in子查询有null分析

        hr@ORCL> create table t1 (a number,b number);
       
        Table created.
 &n