《卸甲筆記》-PostgreSQL和Oracle的SQL差異分析之一：外連線

PostgreSQL是世界上功能最強大的開源資料庫，在國內得到了越來越多機構和開發者的青睞和應用。隨著PostgreSQL的應用越來越廣泛，Oracle向PostgreSQL資料庫的資料遷移需求也越來越多。資料庫之間資料遷移的時候，首先是遷移資料，然後就是SQL、儲存過程、序列等程式中不同的資料庫中資料的使用方式的轉換。下面根據自己的理解和測試，寫了一些SQL以及資料庫物件轉換方面的文章，不足之處，尚請多多指教。

外連線

Oracle資料庫中，資料的外連線有兩種寫法。一種是標準SQL的寫法。【left (outer) join XXX on】。這種標準寫法Oracle和PostgreSQL都是支援的。但是，Oracle還有自己的一種寫法，就是(+)。這種寫法PostgreSQL不支援。所以資料遷移過來以後，需要將Oracle的這種特有的SQL寫法轉換成PostgreSQL支援的寫法，程式才能夠在PostgreSQL資料庫中正常的執行。下面利用下面的表資料分析一下Oracle的這種外連線的寫法和轉換的方式。

SQL> select * from employ;

        ID NAME              AGE ADDR
---------- ---------- ---------- --------------------
         1 趙大               20 山東濟南
         2 錢二               20 山東青島
         4 李四               20 山東濟南
         5 週五               20 山東日照

SQL> select * from salary;

     EMPID YYMM       SALARY
---------- ------ ----------
         1 200010       6000
         2 200010       5000
         3 200010       7000

SQL> select * from achieve;

     EMPID YYMM      ACHIEVE
---------- ------ ----------
         1 200010     150000
         2 200010     100000
         5 200011      50000

1、左外連線

Oracle的(+)寫在等號右側的時候，表示是以等號左邊的表為主表，以右側的表為連線表的左外連線。以左側的表為主表，右側的表的資料如果有，則把資料取出來顯示。如果沒有，則 顯示空(NULL)。
PostgreSQL中，可以把它轉化成對應的【left (outer) join on】語句就可以了。注意兩個相同的表有多個關聯條件的情況（比如下面的第二個SQL）。

Oracle 左外連線

SQL> select  emp.id,  emp.Name, sal.yymm, sal.salary
  2  from employ emp, salary sal
  3  where  emp.id = sal.empid(+);

        ID NAME       YYMM       SALARY
---------- ---------- ------ ----------
         1 趙大       200010       6000
         2 錢二       200010       5000
         5 週五
         4 李四

SQL> select ach.empid, ach.yymm, ach.achieve, sal.salary
  2  from  achieve ach,  salary sal
  3  where  ach.empid=sal.empid(+)
  4  and ach.yymm=sal.yymm(+);

     EMPID YYMM      ACHIEVE     SALARY
---------- ------ ---------- ----------
         1 200010     150000       6000
         2 200010     100000       5000
         5 200011      50000

對應的PostgreSQL的SQL

postgres=# select  emp.id,  emp.Name, sal.yymm, sal.salary
postgres-# from employ emp
postgres-# left outer join salary sal on emp.id=sal.empid;
 id | name |  yymm  | salary
----+------+--------+--------
  1 | 趙大 | 200010 |   6000
  2 | 錢二 | 200010 |   5000
  5 | 週五 |        |
  4 | 李四 |        |
(4 行記錄)

postgres=# select ach.empid, ach.yymm, ach.achieve, sal.salary
postgres-# from  achieve ach
postgres-# left join salary sal
postgres-# on  ach.empid=sal.empid
postgres-# and ach.yymm=sal.yymm;
 empid |  yymm  | achieve | salary
-------+--------+---------+--------
     1 | 200010 |  150000 |   6000
     2 | 200010 |  100000 |   5000
     5 | 200011 |   50000 |
(3 行記錄)

2、右外連線

Oracle的(+)寫在等號左側的時候，表示是以等號右邊的表為主表，以左側的表為連線表的右外連線。以右側的表為主表，左側的表的資料如果有，則把資料取出來顯示。如果沒有，則 顯示空(NULL)。
PostgreSQL中，可以把它轉化成對應的【right (outer) join on】語句就可以了。

Oracle 右外連線

SQL> select  emp.id,  emp.Name, sal.yymm, sal.salary
  2  from employ emp, salary sal
  3  where  emp.id(+) = sal.empid;

        ID NAME       YYMM       SALARY
---------- ---------- ------ ----------
         1 趙大       200010       6000
         2 錢二       200010       5000
                      200010       7000

SQL> select ach.empid, ach.yymm, ach.achieve, sal.salary
  2  from  achieve ach,  salary sal
  3  where  ach.empid(+)=sal.empid
  4  and ach.yymm(+)=sal.yymm;

     EMPID YYMM      ACHIEVE     SALARY
---------- ------ ---------- ----------
         1 200010     150000       6000
         2 200010     100000       5000
                                   7000

對應的PostgreSQL的SQL

postgres=# select  emp.id,  emp.Name, sal.yymm, sal.salary
postgres-# from employ emp
postgres-# right outer join salary sal on emp.id=sal.empid;
 id | name |  yymm  | salary
----+------+--------+--------
  1 | 趙大 | 200010 |   6000
  2 | 錢二 | 200010 |   5000
    |      | 200010 |   7000
(3 行記錄)

postgres=# select ach.empid, ach.yymm, ach.achieve, sal.salary
postgres-# from  achieve ach
postgres-# right join salary sal
postgres-# on  ach.empid=sal.empid
postgres-# and ach.yymm=sal.yymm;
 empid |  yymm  | achieve | salary
-------+--------+---------+--------
     1 | 200010 |  150000 |   6000
     2 | 200010 |  100000 |   5000
       |        |         |   7000
(3 行記錄)

3、多個連線條件的注意事項

Oracle中，兩個表通過多個關連條件外連線的時候，如果多個條件中有沒有寫(+)的條件，則連線會自動變成內連線，而不再是外連線。這種情況應該是屬於寫SQL的失誤。遇到這種情況的時候一定要注意。

Oracle

SQL> select ach.empid, ach.yymm, ach.achieve, sal.salary
  2  from  achieve ach,  salary sal
  3  where  ach.empid=sal.empid
  4  and ach.yymm=sal.yymm;

     EMPID YYMM      ACHIEVE     SALARY
---------- ------ ---------- ----------
         1 200010     150000       6000
         2 200010     100000       5000

SQL> select ach.empid, ach.yymm, ach.achieve, sal.salary
  2  from  achieve ach,  salary sal
  3  where  ach.empid=sal.empid(+)
  4  and ach.yymm=sal.yymm(+);

     EMPID YYMM      ACHIEVE     SALARY
---------- ------ ---------- ----------
         1 200010     150000       6000
         2 200010     100000       5000
         5 200011      50000

SQL> select ach.empid, ach.yymm, ach.achieve, sal.salary
  2  from  achieve ach,  salary sal
  3  where  ach.empid=sal.empid(+)
  4  and ach.yymm=sal.yymm;

     EMPID YYMM      ACHIEVE     SALARY
---------- ------ ---------- ----------
         1 200010     150000       6000
         2 200010     100000       5000

對應的PostgreSQL的SQL

postgres=# select ach.empid, ach.yymm, ach.achieve, sal.salary
postgres-# from  achieve ach
postgres-# left join salary sal
postgres-# on  ach.empid=sal.empid
postgres-# and ach.yymm=sal.yymm;
 empid |  yymm  | achieve | salary
-------+--------+---------+--------
     1 | 200010 |  150000 |   6000
     2 | 200010 |  100000 |   5000
     5 | 200011 |   50000 |
(3 行記錄)

postgres=# select ach.empid, ach.yymm, ach.achieve, sal.salary
postgres-# from  achieve ach, salary sal
postgres-# where ach.empid=sal.empid
postgres-# and ach.yymm=sal.yymm;
 empid |  yymm  | achieve | salary
-------+--------+---------+--------
     1 | 200010 |  150000 |   6000
     2 | 200010 |  100000 |   5000
(2 行記錄)