【SQL】表连接七种方式

---交叉连接,即是笛卡儿乘积 是一种将一个表的所有数据与另一个表中的所有的数据进行组合的操作。
SQL> select e.ename,d.dname
  2  from emp e,dept d;
Execution Plan

----------------------------------------------------------
Plan hash value: 3429684969
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    56 |   896 |     9   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|      |    56 |   896 |     9   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | DEPT |     4 |    40 |     3   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |      |    14 |    84 |     6   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | EMP  |    14 |    84 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

SQL> select e.ename ,d.dname 
  2  from emp e cross join dept d; ---ANSI 的标准语法。(提倡使用)
Execution Plan
----------------------------------------------------------
Plan hash value: 3429684969
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    56 |   896 |     9   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|      |    56 |   896 |     9   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | DEPT |     4 |    40 |     3   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |      |    14 |    84 |     6   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | EMP  |    14 |    84 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
SQL> set autot on
--条件连接,交叉连接加上过滤条件,即只返回符合条件的数据行而不是所有的数据行。条件连接也叫内连接。省略了inner
 
SQL> select e.ename ,s.grade
  2  from emp e,salgrade s
  3  where e.sal between s.losal and s.hisal;

Execution Plan
----------------------------------------------------------
Plan hash value: 2200150871
---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     1 |    20 |     8  (25)| 00:00:01 |
|   1 |  MERGE JOIN          |          |     1 |    20 |     8  (25)| 00:00:01 |
|   2 |   SORT JOIN          |          |     5 |    50 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL | SALGRADE |     5 |    50 |     3   (0)| 00:00:01 |
|*  4 |   FILTER             |          |       |       |            |          |
|*  5 |    SORT JOIN         |          |    14 |   140 |     4  (25)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| EMP      |    14 |   140 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("E"."SAL"<="S"."HISAL")
   5 - access("E"."SAL">="S"."LOSAL")
       filter("E"."SAL">="S"."LOSAL")

SQL> select e.ename ,s.grade
  2  from emp e join salgrade s on e.sal between s.losal and s.hisal;

Execution Plan
----------------------------------------------------------
Plan hash value: 2200150871
---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     1 |    20 |     8  (25)| 00:00:01 |
|   1 |  MERGE JOIN          |          |     1 |    20 |     8  (25)| 00:00:01 |
|   2 |   SORT JOIN            |          |     5 |    50 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL | SALGRADE |     5 |    50 |     3   (0)| 00:00:01 |
|*  4 |   FILTER                  |          |       |       |            |          |
|*  5 |    SORT JOIN          |          |    14 |   140 |     4  (25)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| EMP      |    14 |   140 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("E"."SAL"<="S"."HISAL")
   5 - access("E"."SAL">="S"."LOSAL")
       filter("E"."SAL">="S"."LOSAL")
---等值连接 也称为自然连接 natural join 。此种连接条件中只有 等于操作。
SQL> select e.ename ,d.dname
  2  from emp e,dept d
  3  where e.deptno =d.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 2865896559
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   308 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   308 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   126 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("E"."DEPTNO"="D"."DEPTNO")
       filter("E"."DEPTNO"="D"."DEPTNO")

 

SQL> select e.ename ,d.dname  --和前一个sql是一样的。
  2  from emp e join dept d on e.deptno =d.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 2865896559
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   308 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   308 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   126 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("E"."DEPTNO"="D"."DEPTNO")
       filter("E"."DEPTNO"="D"."DEPTNO")

SQL>--自连接 表和自己连接。

SQL> select e.ename,m.ename
  2  from emp e join emp m on e.mgr=m.empno;

Execution Plan
----------------------------------------------------------
Plan hash value: 2513364761
---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    13 |   260 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |        |    13 |   260 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   140 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |        |    13 |   130 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMP    |    13 |   130 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("E"."MGR"="M"."EMPNO")
       filter("E"."MGR"="M"."EMPNO")
   5 - filter("E"."MGR" IS NOT NULL)

SQL> select e.ename ,m.ename
  2  from emp e ,emp m
  3  where e.mgr=m.empno;

Execution Plan
----------------------------------------------------------
Plan hash value: 2513364761
---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    13 |   260 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |        |    13 |   260 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   140 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |        |    13 |   130 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMP    |    13 |   130 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("E"."MGR"="M"."EMPNO")
       filter("E"."MGR"="M"."EMPNO")
   5 - filter("E"."MGR" IS NOT NULL)

SQL> --外连结,拓展了条件连接的结果集。外连结会返回基表的全部数据,即使另一个表中没有与之相匹配的数据行。对于无法匹配的相关数据行的字段,系统以NULL 值显示。
SQL> set autot on
SQL> select e.ename,m.ename
  2  from emp e,emp m
  3  where e.mgr=m.empno(+);

ENAME      ENAME
---------- ----------
FORD       JONES
SCOTT      JONES
JAMES      BLAKE
TURNER     BLAKE
MARTIN     BLAKE
WARD       BLAKE
ALLEN      BLAKE
MILLER     CLARK
ADAMS      SCOTT
CLARK      KING
BLAKE      KING
JONES      KING
SMITH      FORD
KING       -空值

14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1286159638
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   280 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |    14 |   280 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."MGR"="M"."EMPNO"(+))
--左连接
SQL> select e.ename ,m.ename
  2  from emp e left join emp m
  3  on e.mgr=m.empno;

ENAME      ENAME
---------- ----------
FORD       JONES
SCOTT      JONES
JAMES      BLAKE
TURNER     BLAKE
MARTIN     BLAKE
WARD       BLAKE
ALLEN      BLAKE
MILLER     CLARK
ADAMS      SCOTT
CLARK      KING
BLAKE      KING
JONES      KING
SMITH      FORD
KING

14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1286159638
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   280 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |    14 |   280 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."MGR"="M"."EMPNO"(+))

--右连接
SQL> select e.ename ,m.ename
  2  from emp e right join emp m on e.mgr =m.empno;

ENAME      ENAME
---------- ----------
空值       SMITH
空值       ALLEN
空值       WARD
FORD       JONES
SCOTT      JONES
空值      MARTIN
TURNER     BLAKE
ALLEN      BLAKE
WARD       BLAKE
JAMES      BLAKE
MARTIN     BLAKE
MILLER     CLARK
ADAMS      SCOTT
BLAKE      KING
JONES      KING
CLARK      KING
空值       TURNER
空值       ADAMS
空值       JAMES
SMITH      FORD
空值       MILLER

21 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1511908152
---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    14 |   280 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN OUTER            |        |    14 |   280 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   140 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |        |    13 |   130 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMP    |    13 |   130 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("E"."MGR"(+)="M"."EMPNO")
       filter("E"."MGR"(+)="M"."EMPNO")
   5 - filter("E"."MGR"(+) IS NOT NULL)

SQL> select m.ename manager ,e.ename subordinate
  2  from emp e full outer join emp m on e.mgr =m.empno;

MANAGER    SUBORDINAT
---------- ----------
JONES      FORD
JONES      SCOTT
BLAKE      JAMES
BLAKE      TURNER
BLAKE      MARTIN
BLAKE      WARD
BLAKE      ALLEN
CLARK      MILLER
SCOTT      ADAMS
KING       CLARK
KING       BLAKE
KING       JONES
FORD       SMITH
空值       KING
SMITH      空值
ALLEN      空值
WARD       空值
MARTIN     空值
TURNER     空值
ADAMS      空值
JAMES      空值
MILLER     空值

22 rows selected.
--这个执行计划 竟然扫描了四次emp,其中一次ifs
Execution Plan
----------------------------------------------------------
Plan hash value: 3980240577
-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |        |    22 |   308 |    13  (16)| 00:00:01 |
|   1 |  VIEW --将结果集作为视图       |        |    22 |   308 |    13  (16)| 00:00:01 |
|   2 |   UNION-ALL                    |        |       |       |            |          |
|*  3 |    HASH JOIN OUTER             |        |    14 |   280 |     7  (15)| 00:00:01 |
|   4 |     TABLE ACCESS FULL          | EMP    |    14 |   140 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL          | EMP    |    14 |   140 |     3   (0)| 00:00:01 |
|   6 |    MERGE JOIN ANTI             |        |     8 |   112 |     6  (17)| 00:00:01 |
|   7 |     TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   140 |     2   (0)| 00:00:01 |
|   8 |      INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |
|*  9 |     SORT UNIQUE                |        |    13 |    52 |     4  (25)| 00:00:01 |
|* 10 |      TABLE ACCESS FULL         | EMP    |    13 |    52 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("E"."MGR"="M"."EMPNO"(+))
   9 - access("E"."MGR"="M"."EMPNO")
       filter("E"."MGR"="M"."EMPNO")
  10 - filter("E"."MGR" IS NOT NULL)

SQL> --半连接 SEMI JOIN :当一个表在另一个表找到匹配的记录之后,半连接返回第一张表中的记录。与条件连接相反,
即使在右节点中找到几条匹配的记录,左节点也只会返回同一条记录。另外,右节点中的数据一条也不会返回。半连接使用 EXISTS

 IN 作为连接条件
SQL> select deptno ,dname ,loc
  2  from dept
  3  where deptno in (select deptno from emp);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
Execution Plan
----------------------------------------------------------
Plan hash value: 2365756639

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     3 |    69 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN SEMI             |         |     3 |    69 |     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"="DEPTNO")
       filter("DEPTNO"="DEPTNO")

SQL> select deptno ,dname ,loc
  2  from dept
  3  where exists (select deptno from emp where emp.deptno = dept.deptno );

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO

Execution Plan
----------------------------------------------------------
Plan hash value: 2365756639
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     3 |    69 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN SEMI             |         |     3 |    69 |     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")

SQL> 
SQL> --反连接(ANTI JOIN) 是一种比较特殊的半连接,只有在另外一张表找不到匹配记录时才会返回结果,连接条件常以 NOT IN  ,NOT EXISTS 出现。
SQL> select deptno ,dname ,loc
  2  from dept
  3  where not exists (select deptno from emp where emp.deptno =dept.deptno);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

Execution Plan
----------------------------------------------------------
Plan hash value: 3724808082
----------------------------------------------------------------------------------------
| 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")

SQL> select deptno ,dname ,loc
  2  from dept
  3  where deptno not in (select deptno from emp where emp.deptno =dept.deptno);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

Execution Plan
----------------------------------------------------------
Plan hash value: 3724808082

----------------------------------------------------------------------------------------
| 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")

关于外连接的介绍请参见我的另外的博客:http://space.itpub.net/22664653/viewspace-671023

上一篇:网页超过一屏时自动浮动在网页最上方的图层特效


下一篇:2017年安全漏洞审查报告:安全补丁在不断增加,用户却不安装