Oracle資料庫聯接(inner join ,outer join)和NOT IN的特殊情況

資料庫版本：
SQL> select * from v$version;

BANNER
----------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

情況1：
SQL> create table a1(id number);

表已建立。

SQL> create table a2(id number);

表已建立。

SQL> insert into a1 values(1);

已建立 1 行。

SQL> insert into a1 values(2);

已建立 1 行。

SQL> insert into a1 values(1);

已建立 1 行。

SQL> insert into a1 values(3);

已建立 1 行。

SQL> commit;

提交完成。

SQL> insert into a2 values(1);

已建立 1 行。

SQL> insert into a2 values(3);

已建立 1 行。

SQL> insert into a2 values(1);

已建立 1 行。

SQL> insert into a2 values(5);

已建立 1 行。

SQL> commit;

提交完成。

SQL> select * from a1;

        ID
----------
         1
         2
         1
         3
a1表ID=1有兩條資料。

SQL> select * from a2;

        ID
----------
         1
         3
         1
         5
a2表ID=1的也有兩條資料。

SQL> select a1.id,a2.id from a1,a2 where a1.id=a2.id;

        ID         ID
---------- ----------
         1          1
         1          1
         3          3
         1          1
         1          1
a1表和a2表ID=1的都有兩條資料，那麼內連線的結果就是將a1表和a2表ID=1的結果進行了笛卡爾集，所以返回的結果有4條ID=1的記錄。

SQL> select a1.id,a2.id from a1 left outer join a2
  2  on a1.id=a2.id;

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

已選擇6行。
a1表只有4條記錄，理論上左連線的結果應該只有4條，但是這裡的查詢出現了6條記錄，原因就在於a1表和a2表都有重複記錄，兩個表ID=1的資料做了笛卡爾集，所以多了兩條資料出來。

如果只有一張表有重複記錄，笛卡爾集也不會出現多出的資料：
SQL> delete from a2 where id=1;

已刪除2行。

SQL> select a1.id,a2.id from a1 left outer join a2
  2  on a1.id=a2.id;

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

由此看來，所謂的連線其實是把多個表的相同記錄來做笛卡爾集，如果兩個表不是同時的重複相同的資料，那麼結果都將是我們滿意的結果，如果同時重複某條關聯資料，那麼將收到成倍的返回資料。

情況2：
對於not in來說會出現如下情況：
SQL> select * from a1;

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

SQL> select * from a2;

        ID
----------
         1
         3
         1
         5

SQL> insert into a1 values (1);

已建立 1 行。

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

        ID
----------
         2

SQL> select a1.id,a2.id from a1 left outer join a2 on a1.id=a2.id;

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

已選擇8行。

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

        ID         ID
---------- ----------
         2
雖然使用not in和left outer join的方式的結果是相同的，但是從left outer join的中間結果可以看出，由於兩個表都有ID=1的重複記錄，所以中間結果會多很多資料出來，這也會導致SQL效能的下降。如果where子句的過濾條件是is not null，那麼同樣會返回包含笛卡爾集的返回結果，為了更好的效能和準確的資料返回，可以先使用distinct的方法至少去掉一張表關聯欄位的重複值，之後再進行相關的not in或者聯接操作。

情況3：
對於具有Null值，not in的處理：
SQL> select * from a1;

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

SQL> select * from a2;

        ID
----------
         1
         3
         1
         5

SQL> insert into a2 values(null);

已建立 1 行。

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

未選定行
如果a2表中包含空值，那麼使用not in將沒有任何值返回，因為Null不是一個值，不能在IN ,NOT IN,=中出現。

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

        ID         ID
---------- ----------
         2
使用left outer join的方式就可以接受a2表有NULL值的情況，因為使用這種方式將兩表的值進行關聯(非NOT IN ,IN ,=)，NULL關聯不上自然不會出現。



以上是到目前為止遇到過的有關聯接和NOT IN之間遇到的特殊問題，記錄下來以備檢視，如有類似問題繼續補充！

--end--