not in改写关联无需考虑重复数据

SQL> select * from a1;

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

SQL> select * from a2;

	ID
----------
	 2
	 3
	 2

SQL>  select * from a1 where id not in (select id from a2);

	ID NAME
---------- ----------
	 1 a
	 1 a

SQL> select a1.* from a1,a2
  2  where a1.id=a2.id(+)
  3  and a2.id is null;

	ID NAME
---------- ----------
	 1 a
	 1 a

posted @ 2014-01-03 09:21  czcb  阅读(292)  评论(0编辑  收藏  举报