oracle的left join，right join和full join的一点介绍

wsql

浏览: 11790602 次
性别:
来自: 深圳

最近访客更多访客>>

GDGZWQZ

IT_way

qq_33632159

mhx1535

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (14573)

社区版块

存档分类

2013-03 ( 19)
2013-02 ( 15)
2013-01 ( 268)
更多存档...

以下是摘自oracle ocp9i文档：
outer join syntax:
1)you use an outer join to also see rows that do not meet the join condition
2)the outer join operator is the plus sign(+)
outer join restrictions:
1)the outer join operator can appear on only one side of the expression:the side that has information missing.it returns those rows from one table that have no direct match in the other table.
2)a condition involving an outer join cannot use the IN operator or be linked to another condition by the OR operator.

配置实验环境：

hr@ORCL> drop table a;
hr@ORCL> drop table b;
hr@ORCL> create table a(id number,name varchar2(10));
hr@ORCL> create table b(id number,name varchar2(10));
hr@ORCL> insert into a values(1,'a');
hr@ORCL> insert into a values(2,'b');
hr@ORCL> insert into a values(3,'c');
hr@ORCL> insert into b values(1,'a');
hr@ORCL> insert into b values(2,'b');
hr@ORCL> insert into b values(4,'d');
hr@ORCL> select * from a;

        ID NAME
---------- ----------
         1 a
         2 b
         3 c

hr@ORCL> select * from b;

        ID NAME
---------- ----------
         1 a
         2 b
         4 d

hr@ORCL> commit;

--全外连接的结果是：
1）选出所有满足条件的结果
2）以左表为准，将左表不满足条件的结果接在左边
3）以右表为准，把右表不满足条件的结果接在右边
4）将以上结果全部合起来

hr@ORCL> select a.id,b.id from a full join b on a.id=b.id;

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

--left out join和oracle的加号在右结果是相同。同理，right out join和加号在左是一样的。（sql99的语法和oracle私有语法的比较）

hr@ORCL> select a.id,b.id from a,b where a.id=b.id(+);

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

hr@ORCL> select a.id,b.id from a left outer join b on a.id=b.id;

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

--结果类似于from a left join b on a.col=b.col and a.coln=....。单个列选择条件的列是基表（加号在谁身上谁是从表，没有加号的一方是基表）的用decode和+改写，不能用一般的改写，若不是基表的可以简单用+改写。比如：

SQL> SELECT a.ID,b.ID
  2  FROM a FULL JOIN b
  3  ON a.ID=b.ID AND a.NAME='a';

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

改写之：

SQL> SELECT a.ID,b.ID
  2  FROM a,b WHERE a.id=decode(a.NAME,'a',b.ID(+))
  3  UNION ALL
  4  SELECT a.ID,b.ID
  5  FROM a,b WHERE a.ID(+)=b.ID AND a.NAME(+)='a' AND a.ID IS NULL;

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

用union来实现上面例子的full join结果，需要考虑表的关系。

1）如果是两表1:1
加号在左，以右为准（相当于sql99的right join）
加号在右，以左为准（相当于sql99的left join）

即：在没加号的地方，读取前缀。

比如：where e.department_id(+)=d.department_id

读作：所有部门

hr@ORCL> select a.id,b.id from a,b where a.id=b.id(+)
  2  union
  3      select a.id,b.id from a,b where a.id(+)=b.id;

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

2）如果是两表1:n，用union剔重就不正确。

hr@ORCL> insert into a values(1,'a');
hr@ORCL> commit;
hr@ORCL> select * from a;

        ID NAME
---------- ----------
         1 a
         2 b
         3 c
         1 a

hr@ORCL> select * from b;

        ID NAME
---------- ----------
         1 a
         2 b
         4 d
--1:n用UNION不正确
SQL> SELECT a.ID,b.ID
  2  FROM a,b WHERE a.ID=b.ID(+)
  3  UNION
  4  SELECT a.ID,b.ID
  5  FROM a,b WHERE a.ID(+)=b.ID;

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

--正确解法有三。注意，在使用sql时，任何时候，任何地方，一定要考虑null！！！切记。
法一：
SQL> SELECT a.ID,b.ID
  2  FROM a,b WHERE a.ID=b.ID(+)
  3  UNION ALL
  4  SELECT a.ID,b.ID
  5  FROM a,b WHERE a.ID(+)=b.ID AND a.ID IS NULL;

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

法二：
SQL> SELECT a.ID,b.ID
  2  FROM a,b WHERE a.ID=b.ID(+) AND b.ID IS NULL
  3  UNION ALL
  4  SELECT a.ID,b.ID
  5  FROM a,b WHERE a.ID(+)=b.ID;

        ID         ID
---------- ----------
         3 
         1          1
         1          1
         2          2
                    4
法三：
SQL> SELECT a.ID,b.ID
  2  FROM a FULL JOIN b
  3  ON a.ID=b.ID;

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

--逗号和full join是不一样的。另外，full join须加上关键字on，才是完整的语句。

hr@ORCL> select p.id,t.id from p,t where p.id=t.id;

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

hr@ORCL> select p.id,t.id from p full join t on p.id=t.id;

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

全外连接和union连接都可以实现相同结果。我们来看一下他们的执行计划。

全外连接的执行计划：
hr@ORCL> select a.id,b.id from a full join b on a.id=b.id;

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


Execution Plan
----------------------------------------------------------
Plan hash value: 2192011130

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     4 |   104 |    13   (8)| 00:00:01 |
|   1 |  VIEW                |      |     4 |   104 |    13   (8)| 00:00:01 |
|   2 |   UNION-ALL          |      |       |       |            |          |
|*  3 |    HASH JOIN OUTER   |      |     3 |   312 |     7  (15)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| A    |     3 |   195 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| B    |     3 |   117 |     3   (0)| 00:00:01 |
|*  6 |    HASH JOIN ANTI    |      |     1 |    26 |     7  (15)| 00:00:01 |
|   7 |     TABLE ACCESS FULL| B    |     3 |    39 |     3   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL| A    |     3 |    39 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"."ID"="B"."ID"(+))
   6 - access("A"."ID"="B"."ID")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         29  consistent gets
          0  physical reads
          0  redo size
        520  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

union的执行计划：
hr@ORCL> select a.id,b.id from a,b where a.id=b.id(+)
  2  union
  3      select a.id,b.id from a,b where a.id(+)=b.id;

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


Execution Plan
----------------------------------------------------------
Plan hash value: 891669117

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     6 |   156 |    15  (60)| 00:00:01 |
|   1 |  SORT UNIQUE         |      |     6 |   156 |    15  (60)| 00:00:01 |
|   2 |   UNION-ALL          |      |       |       |            |          |
|*  3 |    HASH JOIN OUTER   |      |     3 |    78 |     7  (15)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| A    |     3 |    39 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| B    |     3 |    39 |     3   (0)| 00:00:01 |
|*  6 |    HASH JOIN OUTER   |      |     3 |    78 |     7  (15)| 00:00:01 |
|   7 |     TABLE ACCESS FULL| B    |     3 |    39 |     3   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL| A    |     3 |    39 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"."ID"="B"."ID"(+))
   6 - access("A"."ID"(+)="B"."ID")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
        520  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          4  rows processed

显然，union连接的cpu代价要比full join连接来得多。此外，union还会暗含一个排序操作。当数据量海量时，估计会对性能带来一定的影响。而且，在oracle的私有语法里，是没有全外连接的，只能通过union连接来模拟full join。所以，建议需要使用外连接时，请使用full join，不要用union模拟。

分享到：