Oracle的表連線方法（五）

Oracle表連線方法有四種：
  ● 排序合併連線（Sort Merge Join）
  ● 巢狀迴圈連線（Nested Loops Join）
  ● 雜湊連線（Hash Join）
  ● 笛卡爾積（Cartesian Product）

 

前面連線方法的示例都是內連線，我們知道Oracle還有外連線，包括左外連線，右外連線和全外連線。執行計劃中左外連線和右外連線對應的關鍵字是OUTER，全外連線對應的關鍵字是FULL OUTER。

 

內連線：
SQL> select e.employee_id,e.last_name,d.department_name from employees e join departments d on e.department_id=d.department_id;

106 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1343509718

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |   106 |  3286 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |             |   106 |  3286 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |             |   107 |  1605 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

 

左外連線：
SQL> select e.employee_id,e.last_name,d.department_name from employees e left join departments d on e.department_id=d.department_id;

107 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2296652067

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |   107 |  3317 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |             |   107 |  3317 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

 

右外連線：
SQL> select e.employee_id,e.last_name,d.department_name from employees e right join departments d on e.department_id=d.department_id;

122 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 514479674

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |   106 |  3286 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN OUTER            |             |   106 |  3286 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |             |   107 |  1605 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

 

全外連線：
SQL> select e.employee_id,e.last_name,d.department_name from employees e full join departments d on e.department_id=d.department_id;

123 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2631508678

-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |   122 |  5368 |     6   (0)| 00:00:01 |
|   1 |  VIEW                 | VW_FOJ_0    |   122 |  5368 |     6   (0)| 00:00:01 |
|*  2 |   HASH JOIN FULL OUTER|             |   122 |  3782 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------