not in 用外连接实现

sys@CRMCN>select * from test;

        ID       NAME
---------- ----------
         1          2
         2          3

sys@CRMCN>select * from test1;

        ID       NAME
---------- ----------
         1          2
         2          3

sys@CRMCN>select
  2  test.*
  3  from
  4  test,
  5  test1
  6  where
  7  test1.id(+) = test.id
  8  and test1.name(+) = 2
  9  and test1.id is null
 10  ;

        ID       NAME
---------- ----------
         2          3
        
等价于：

select test.*
from test
where test.id not in (
select test1.id
from test1
where test1.name = 2
)
;

sys@CRMCN>select * from
  2  test,
  3  test1
  4  where
  5  test1.id(+) = test.id
  6  and test1.name(+) = 2;

        ID       NAME         ID       NAME
---------- ---------- ---------- ----------
         1          2          1          2
         2          3

sys@CRMCN>select * from
  2  test,
  3  test1
  4  where
  5  test1.id(+) = test.id;

        ID       NAME         ID       NAME
---------- ---------- ---------- ----------
         1          2          1          2
         2          3          2          3
连接以后。对于test1.name不为2的记录，整条记录用null值代替。

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

转载于:http://blog.itpub.net/22034023/viewspace-715426/