Not in 改写左连接不需要关注连接列是否重复数据

SQL> select * from a1;

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

已选择9行。

SQL> select * from a2;

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

已选择6行。

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

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

左连接:

SQL> select * from a1 left join a2 on a1.id=a2.id
  2  where a2.id is null;

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