Oracle的表如何連接

這篇文章主要介紹Oracle的表如何連接，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！

Oracle表連接方法有四種：
  ● 排序合并連接（Sort Merge Join）
  ● 嵌套循環(huán)連接（Nested Loops Join）
  ● 哈希連接（Hash Join）
  ● 笛卡爾積（Cartesian Product）

如果where條件有exists、in或=any操作符+子查詢，則Oracle會將其處理為半連接，執(zhí)行計劃中對應的關鍵字為SEMI。

SQL> select department_id,department_name from departments d where exists (select 1 from employees e where d.department_id = e.department_id and e.salary > 2500);

11 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2188966913

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    10 |   230 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN SEMI             |             |    10 |   230 |     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 UNIQUE                |             |   105 |   735 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMPLOYEES   |   105 |   735 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

SQL> select department_id,department_name from departments d where department_id in (select department_id from employees e where d.department_id = e.department_id and e.salary > 2500);

11 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2188966913

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    10 |   230 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN SEMI             |             |    10 |   230 |     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 UNIQUE                |             |   105 |   735 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMPLOYEES   |   105 |   735 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

SQL> select department_id,department_name from departments d where department_id=any(select department_id from employees e where d.department_id = e.department_id and e.salary > 2500);

11 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2188966913

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    10 |   230 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN SEMI             |             |    10 |   230 |     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 UNIQUE                |             |   105 |   735 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMPLOYEES   |   105 |   735 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

以上是“Oracle的表如何連接”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關知識，歡迎關注億速云行業(yè)資訊頻道！