How to join the tables of Oracle
This article mainly introduces how to connect the table of Oracle. It is very detailed and has certain reference value. Friends who are interested must finish it.
There are four ways to join Oracle tables:
● sort merge join (Sort Merge Join)
● nested Loop join (Nested Loops Join)
● Hash connection (Hash Join)
● Cartesian product (Cartesian Product)
If the where condition has the exists, in, or = any operator + subquery, Oracle treats it as a semi-join, and the corresponding keyword in the execution plan is 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 |
-
The above is all the contents of the article "how to connect the tables of Oracle". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!
