《卸甲笔记》-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       7000SQL> 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.salarypostgres-# from employ emppostgres-# 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.salarypostgres-# from  achieve achpostgres-# left join salary salpostgres-# on  ach.empid=sal.empidpostgres-# 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       7000SQL> 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.salarypostgres-# from employ emppostgres-# 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.salarypostgres-# from  achieve achpostgres-# right join salary salpostgres-# on  ach.empid=sal.empidpostgres-# 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       5000SQL> 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      50000SQL> 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.salarypostgres-# from  achieve achpostgres-# left join salary salpostgres-# on  ach.empid=sal.empidpostgres-# 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.salarypostgres-# from  achieve ach, salary salpostgres-# where ach.empid=sal.empidpostgres-# and ach.yymm=sal.yymm; empid |  yymm  | achieve | salary-------+--------+---------+--------     1 | 200010 |  150000 |   6000     2 | 200010 |  100000 |   5000(2 行记录)