《卸甲笔记》-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 行记录)