从Oracle9i开始，对于外连接(Outer join)Oracle支持SQL92标准：这个标准有很多新的连接语法。外连接的新语法有left outer join和rigth outer join以及full outer join，旧语法外连接使用+号，
另外，旧语法无法实现full outer join，只能用union all来代替。Oracle也强烈建议9i以及之后的版本使用新的外连接语法。
outer join外连接有基表和从表的概念，基表也就是参考表，此表的内容会在outer join中全部选中，然后基表根据join的条件到从表中选出从表记录，如果满足条件则按从表实际内容选出，否则没有找到则从表对应行的所表列值为null。
就新语法而言，比如
左外链接（left outer join)左边的表是基表，右边的表是从表，如a left join b则a为基表，b为从表；
右外链接（rigth outer join）右边的表是基表，左边的表是从表，如a rigth join b则a为从表，b为基表；
全链接（full outer join）比较特殊，左右边表互为基表和从表，如a full join b则a,b互为基表和从表。
旧语法来说，在where条件中无(+)号的表达式中的列所属表为基表，有(+)号的表达式中的列所属表为从表，比如where a.id=b.id(+)则a为基表，b为从表。
下面测试下新旧语法外连接的差别。
表a,b,c初始化脚本如下：

点击(此处)折叠或打开

SQL> drop table a;
表已删除。
SQL> create table a as
2 select level id,'x'||level name
3 from dual connect by level<5
4 union all
5 select level,'y'||level
6 from dual connect by level<5;
表已创建。
SQL> drop table b;
表已删除。
SQL> create table b as
2 select level id,'x'||level name
3 from dual connect by level<3;
表已创建。
SQL> drop table c;
drop table c
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
SQL> create table c as
2 select level id,'y'||level name
3 from dual connect by level<3;
表已创建。
SQL> select * from a;
ID NAME
---------- -----------------------------------------
1 x1
2 x2
3 x3
4 x4
1 y1
2 y2
3 y3
4 y4
已选择8行。
SQL> select * from b;
ID NAME
---------- -----------------------------------------
1 x1
2 x2
SQL> select * from c;
ID NAME
---------- -----------------------------------------
1 y1
2 y2

外连接旧语法(+)如下：

点击(此处)折叠或打开

SQL> show linesize;
linesize 80
SQL> set linesize 200;
SQL> column name format a10
SQL> select * from a,b
2 where a.id=b.id(+) and a.name like 'x%';
ID NAME ID NAME
---------- ---------- ---------- ----------
1 x1 1 x1
2 x2 2 x2
4 x4
3 x3

新语法left(rigth/full) outer join测试代码如下：

点击(此处)折叠或打开

SQL> set null null
SQL> select * from a left join b
2 on a.id=b.id and a.name like 'x%';
ID NAME ID NAME
---------- ---------- ---------- ----------
1 x1 1 x1
2 x2 2 x2
3 x3 null null
4 x4 null null
1 y1 null null
2 y2 null null
3 y3 null null
4 y4 null null
已选择8行。
SQL> select * from a left join b
2 on a.id=b.id
3 where a.name like 'x%';
ID NAME ID NAME
---------- ---------- ---------- ----------
1 x1 1 x1
2 x2 2 x2
4 x4 null null
3 x3 null null
SQL> select * from a left join b
2 on a.name like 'x%'
3 where a.id=b.id;
ID NAME ID NAME
---------- ---------- ---------- ----------
1 x1 1 x1
2 x2 2 x2

从上面新语法测试代码第一个sql语句中可知，外连接中的on不需要过滤基表数据，过滤基表数据是在where里做的，on只是连接条件，根据连接条件找匹配的从表数据，找不到匹配的从表行，则置空。简单来说on关键字，只是连接条件，它不能过滤基表。过滤不了的原因是on过滤掉的a表结果，最后又被left outer join拿了回来。

ITPub博客

outer join新旧语法分析

最新文章