两种连接的表达 ：left(right) join 和 (+)

稍微研究了一下 oracle 自己的join 和标准的join。主要表现在on ，where 关键字所起的作用不同，和连接本身的特性。
yang@ORACL> set autotrace on
yang@ORACL> select  *
  2  from a,b
  3  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
执行计划
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |   280 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     4 |   280 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| A    |     4 |   140 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     2 |    70 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."ID"="B"."ID"(+))
   2 - filter("A"."NAME" LIKE 'x%')
由执行计划可以看出先对a表进行过滤，让后进行与b表的left join，
--
yang@ORACL> 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
         4 x4
         1 y1
         2 y2
         3 y3
         4 y4

已选择8行。

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     8 |   560 |    27   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER  |      |     8 |   560 |    27   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | A    |     8 |   280 |     3   (0)| 00:00:01 |
|   3 |   VIEW               |      |     1 |    35 |     3   (0)| 00:00:01 |
|*  4 |    FILTER            |      |       |       |            |          |
|*  5 |     TABLE ACCESS FULL| B    |     1 |    35 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("A"."NAME" LIKE 'x%')
   5 - filter("A"."ID"="B"."ID")
-- 先连接后筛选 （由 Table A 驱动 NESTED LOOPS OUTER ，每一次循环筛选记录
外连接中的on不需要过滤基表数据，过滤基表数据是在where里做的，on只是连接条件，根据连接条件找匹配的从表数据，找不到匹配的从表行，则置空。
yang@ORACL> 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
         3 x3
执行计划
----------------------------------------------------------
Plan hash value: 1365417139

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |   280 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     4 |   280 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| A    |     4 |   140 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     2 |    70 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."ID"="B"."ID"(+))
   2 - filter("A"."NAME" LIKE 'x%')

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

yang@ORACL> 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

执行计划
----------------------------------------------------------
Plan hash value: 652036164
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    70 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    70 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| A    |     1 |    35 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     2 |    70 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."ID"="B"."ID")
   2 - filter("A"."NAME" LIKE 'x%')
需要说明的是：
 on关键字，的确是连接条件，它不能过滤基表。过滤不了的原因是on过滤掉的emp表结果，最后又被left outer join拿了回来。
 select e.ename,d.dname from emp e left outer join dept d on d.deptno=e.deptno and e.ename='SCOTT';
  伪代码
    for rec_e in ( select ename from emp e ) loop
        for rec_d in ( select dname from dept d where d.deptno = rec_e.deptno ) loop
             if rec_d != NULL and rec_e..ename='SCOTT' then
                show (rec_e.ename,rec_d.name);
             else
                show (rec_e.ename,null);   --on过滤掉的emp表结果，最后又被left outer join拿了回来。
             end if;
        end loop;
     end loop;
--伪代码部分内容参考了puber ccsnmoracle 的表述

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22664653/viewspace-688974/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22664653/viewspace-688974/