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 |
-------------------------------------------------------------------------------------
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28536251/viewspace-2140407/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28536251/viewspace-2140407/