RUI个人博客 首页>>Oracle Development>>[原]关于left join 和rigt join 的理解实验

[原]关于left join 和rigt join 的理解实验

  对于 left join 与 right join 的实验过程, 参考 :教主的 查询改写 一书。

测试 LEFT JOIN 与 RIGHT JOIN

drop table L purge;
drop table R purge;


/*左表*/
create table L as 
select 'left_1' as str,'1' as v from dual union all
select 'left_2','2' as v from dual union all
select 'left_3','3' as v from dual union all
select 'left_4','4' as v from dual;


/* 右表 */
create table R as
select 'right_3' as str,'3' as v,1 as status from dual union all
select 'right_4' as str,'4' as v,0 as status from dual union all
select 'right_5' as str,'5' as v,0 as status from dual union all
select 'right_6' as str,'6' as v,0 as status from dual;

select * from L;


select * from R;


    1. 1 测试 INNER JOIN的特点
select l.str as left_str,r.str as right_str
       from L
       inner join R on L.v = R.v
       order by 1,2;

等价改写 where 条件
select L.str as left_str,R.str as right_str
       from L,R
       where L.v = R.v
       order by 1,2;
    1.2 测试LEFT JOIN 的特点
    该方式的左表为主表,左表返回所有的数据,右表中只返回与左表匹配的数据
select L.str as left_str,R.str as right_str
       from L
       left join R on L.v = R.v
       order by 1,2;


等价改写 : 加(+) 后的写法
select L.str as left_str,R.str as right_str
       from L,R
       where L.v = R.v(+)
       order by 1,2;
    1.3 测试 RIGHT JOIN 的特点
    该方式的右表 为主表,左表中只返回与右表匹配的数据。右表返回所有的数据。
select L.str as left_str,R.str as right_str
       from L
       right join R
       on (L.v = R.v)
       order by 1,2;

等价 改写 加号(+) 的写法
select L.str as left_str,R.str as right_str
       from L,R
       where L.v(+) = R.v
       order by 1,2;
    1.4 FULL JOIN 的特点
    该方式的左右表均返回所有的数据,但只有相匹配的数据显示在同一行,非匹配的行志向是一个表的数据。
select L.str as left_str,R.str as right_str
       from L
       full join R
       on L.v = R.v
       order by 1,2;

FULL JOIN  无(+)的写法
2. 自连接
select a.empno,a.ename,a.job,a.mgr,b.empno,b.ename
    from emp a
    left join emp b on (a.empno = b.empno)
    order by 1;

理解改写:
create or replace view emp_a as select * from emp;
create or replace view emp_b as select * from emp;

select a.empno,a.ename,a.job,a.mgr,b.empno,b.ename
       from emp_a a
       left join emp_b b on a.mgr = b. empno
       order by 1;
3. NOT IN 、NOT EXISTS 和 LEFT JOIN
alter table dept add constraints pk_dept primary key(deptno);

   // NOT IN 写法
explain plan for
    select * from dept
    where deptno not in (
       select emp.deptno from emp where emp.deptno is not null);

select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 1353548327

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    23 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN ANTI             |         |     1 |    23 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE                |         |    14 |    42 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMP     |    14 |    42 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("DEPTNO"="EMP"."DEPTNO")
       filter("DEPTNO"="EMP"."DEPTNO")
   5 - filter("EMP"."DEPTNO" IS NOT NULL)

19 rows selected.
 // NOT EXISTS 写法
explain plan for
    select * from dept
    where not exists (
       select null from emp where emp.deptno = dept.deptno);

select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 1353548327

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    23 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN ANTI             |         |     1 |    23 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE                |         |    14 |    42 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |    42 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("DEPTNO"="EMP"."DEPTNO")
       filter("DEPTNO"="EMP"."DEPTNO")

19 rows selected.
 // LEFT JOIN 写法
explain plan for
    select dept.*
    from dept
    left join emp on emp.deptno = dept.deptno
   where emp.deptno is null

select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 1353548327

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    23 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN ANTI             |         |     1 |    23 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE                |         |    14 |    42 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |    42 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

18 rows selected.

explain plan for select emp.empno,emp.ename,dept.deptno,dept.dname
           from emp
        full join dept on dept.deptno=emp.deptno;

select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 51889263

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |    15 |   630 |     6   (0)| 00:00:01 |
|   1 |  VIEW                 | VW_FOJ_0 |    15 |   630 |     6   (0)| 00:00:01 |
|*  2 |   HASH JOIN FULL OUTER|          |    15 |   390 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | DEPT     |     4 |    52 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | EMP      |    14 |   182 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   2 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

16 rows selected.

explain plan for select emp.empno,emp.ename,dept.deptno,dept.dname
                  from emp
                  left join dept on dept.deptno = emp.deptno
    union all 
    select emp.empno,emp.ename,dept.deptno,dept.dname
           from emp
        right join dept on dept.deptno = emp.deptno
        where emp.empno is null;

select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 315464403

------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |    15 |   390 |    12   (9)| 00:00:01 |
|   1 |  UNION-ALL                     |         |       |       |            |          |
|*  2 |   HASH JOIN OUTER              |         |    14 |   364 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL           | EMP     |    14 |   182 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL           | DEPT    |     4 |    52 |     3   (0)| 00:00:01 |
|*  5 |   FILTER                       |         |       |       |            |          |
|   6 |    MERGE JOIN OUTER            |         |     1 |    26 |     6  (17)| 00:00:01 |
|   7 |     TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   8 |      INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  9 |     SORT JOIN                  |         |    14 |   182 |     4  (25)| 00:00:01 |
|  10 |      TABLE ACCESS FULL         | EMP     |    14 |   182 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("DEPT"."DEPTNO"(+)="EMP"."DEPTNO")
   5 - filter("EMP"."EMPNO" IS NULL)
   9 - access("DEPT"."DEPTNO"="EMP"."DEPTNO"(+))
       filter("DEPT"."DEPTNO"="EMP"."DEPTNO"(+))

25 rows selected.

昵  称:
邮  箱:
评论内容:
验 证 码:
可用[code][/code]插入代码
点击刷新验证码