【SQL】表連線七種方式

楊奇龍發表於2010-09-15


---交叉連線,即是笛卡兒乘積 是一種將一個表的所有資料與另一個表中的所有的資料進行組合的操作。
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

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22664653/viewspace-673990/,如需轉載,請註明出處,否則將追究法律責任。

相關文章