5.表连接
1.表连接
1.1.表连接顺序
Oracle在实际执行该SQL时，都只能先两两做表连接，再一次执行这样的两两连接过程，直到目标SQL中所有的表都已经连接完毕
1.2.表连接方法
排序合并连接，嵌套循环连接，哈希连接，笛卡尔积连接
1.3.访问单表的方法
是采用全表扫描方法还是走索引。
2.表连接的类型
scott@ORCLPDB01 2023-04-02 17:08:40> select * from t1;
COL1 COL2
---------- -----
1 A
2 B
3 C
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 17:08:45> select * from t2;
COL2 COL3
----- -----
A A2
B B2
D D2
Elapsed: 00:00:00.002.1.内连接
scott@ORCLPDB01 2023-04-02 17:09:32> select t1.col1,t1.col2,t2.col3
2 from t1,t2
3 where t1.col2 = t2.col2;
COL1 COL2 COL3
---------- ----- -----
1 A A2
2 B B2
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 17:10:10> select t1.col1,t1.col2,t2.col3
2 from t1
3 join t2 on (t1.col2 = t2.col2);
COL1 COL2 COL3
---------- ----- -----
1 A A2
2 B B2
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 17:12:05> select t1.col1,t1.col2,t2.col3
2 from t1
3 join t2 using (col2);
select t1.col1,t1.col2,t2.col3
*
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 17:12:27> select t1.col1,col2,t2.col3
2 from t1
3 join t2 using (col2);
COL1 COL2 COL3
---------- ----- -----
1 A A2
2 B B2
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 17:12:39> select t1.col1,col2,t2.col3
2 from t1 natural join t2;
COL1 COL2 COL3
---------- ----- -----
1 A A2
2 B B2
Elapsed: 00:00:00.002.2左外连接
scott@ORCLPDB01 2023-04-02 17:14:25> select t1.col1,t1.col2,t2.col3
2 from t1
3 left outer join t2 on (t1.col2 = t2.col2);
COL1 COL2 COL3
---------- ----- -----
1 A A2
2 B B2
3 C
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 17:15:28> select t1.col1,col2,t2.col3
2 from t1
3 left outer join t2 using (col2);
COL1 COL2 COL3
---------- ----- -----
1 A A2
2 B B2
3 C
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 17:15:59> select t1.col1,t1.col2,t2.col3
2 from t1,t2
3 where t1.col2 = t2.col2(+);
COL1 COL2 COL3
---------- ----- -----
1 A A2
2 B B2
3 C
Elapsed: 00:00:00.002.3.右外连接
scott@ORCLPDB01 2023-04-02 17:16:47> select t1.col1,t1.col2,t2.col3
2 from t1
3 right outer join t2 on (t1.col2 = t2.col2);
COL1 COL2 COL3
---------- ----- -----
1 A A2
2 B B2
D2
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 17:19:57> select t1.col1,col2,t2.col3
2 from t1
3* right outer join t2 using (col2)
COL1 COL2 COL3
---------- ----- -----
1 A A2
2 B B2
D D2
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 17:19:59> select t1.col1,t1.col2,t2.col3
2 from t1,t2
3 where t1.col2(+) = t2.col2;
COL1 COL2 COL3
---------- ----- -----
1 A A2
2 B B2
D2
Elapsed: 00:00:00.002.4全外连接
scott@ORCLPDB01 2023-04-02 17:20:44> select t1.col1,t1.col2,t2.col3
2 from t1
3 full outer join t2 on (t1.col2 = t2.col2);
COL1 COL2 COL3
---------- ----- -----
1 A A2
2 B B2
D2
3 C
Elapsed: 00:00:00.002.5内连接和外连接区别
scott@ORCLPDB01 2023-04-02 17:26:21> select t1.col1,t1.col2,t2.col3
2 from t1
3 join t2 on (t1.col2 = t2.col2 and t1.col1 = 1);
COL1 COL2 COL3
---------- ----- -----
1 A A2
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 17:26:31> select t1.col1,t1.col2,t2.col3
2 from t1
3 join t2 on (t1.col2 = t2.col2)
4 where t1.col1 = 1;
COL1 COL2 COL3
---------- ----- -----
1 A A2
Elapsed: 00:00:00.01scott@ORCLPDB01 2023-04-02 17:34:32> select t1.col1,t1.col2,t2.col3
2 from t1
3 left outer join t2 on (t1.col2 = t2.col2 and t1.col1 = 1);
COL1 COL2 COL3
---------- ----- -----
1 A A2
2 B
3 C
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 17:36:30> select t1.col1,t1.col2,t2.col3
2 from t1
3 left outer join t2 on (t1.col2 = t2.col2)
4 where t1.col1 = 1;
COL1 COL2 COL3
---------- ----- -----
1 A A2
Elapsed: 00:00:00.01scott@ORCLPDB01 2023-04-02 17:28:33> select t1.col1,t1.col2,t2.col3
2 from t1
3 right outer join t2 on (t1.col2 = t2.col2 and t1.col1 = 1);
COL1 COL2 COL3
---------- ----- -----
1 A A2
B2
D2
Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 17:29:03> select t1.col1,t1.col2,t2.col3
2 from t1
3 right outer join t2 on (t1.col2 = t2.col2)
4 where t1.col1 = 1;
COL1 COL2 COL3
---------- ----- -----
1 A A2
Elapsed: 00:00:00.00注意：左右外连接，其中小括号限制的条件连接顺序其实是不一样的。
3.执行计划走向
scott@ORCLPDB01 2023-04-02 17:41:17> set autot trace
scott@ORCLPDB01 2023-04-02 17:42:39> select t1.col1,t1.col2,t2.col3
2 from t1,t2
3 where t1.col2(+) = t2.col2
4* and t1.col1(+) = 1
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1426054487
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 30 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 3 | 30 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T2 | 3 | 15 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T1 | 1 | 5 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."COL2"(+)="T2"."COL2")
3 - filter("T1"."COL1"(+)=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
763 bytes sent via SQL*Net to client
453 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
scott@ORCLPDB01 2023-04-02 17:44:22> r
1 select t1.col1,t1.col2,t2.col3
2 from t1,t2
3 where t1.col2(+) = t2.col2
4* and t1.col1 = 1
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 10 | 6 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 5 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 3 | 15 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."COL2"="T2"."COL2")
2 - filter("T1"."COL1"=1)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
699 bytes sent via SQL*Net to client
446 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed3.表连接的方法
3.1.排序合并连接
scott@ORCLPDB01 2023-04-02 18:09:40> select t1.col1,t2.col3
2 from t1,t2;
9 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1323614827
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 54 | 9 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 9 | 54 | 9 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T2 | 3 | 9 | 3 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 3 | 9 | 6 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 3 | 9 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
742 bytes sent via SQL*Net to client
616 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed3.2.嵌套循环连接
scott@ORCLPDB01 2023-04-02 18:09:36> r
1 select /*+ordered use_nl(t2) */ t1.col1, t1.col2, t2.col3
2 from t1,t2
3* where t1.col2 = t2.col2
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1054738919
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 30 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 3 | 30 | 6 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 3 | 30 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T1 | 3 | 15 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_T2 | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 5 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."COL2"="T2"."COL2")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
758 bytes sent via SQL*Net to client
455 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed3.3.哈希连接
3.4.笛卡尔连接
4.反链接
scott@ORCLPDB01 2023-04-02 18:25:10> select * from t1
2 where col2 not in (select col2 from t2);
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1275484728
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI NA | | 1 | 7 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3 | 15 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 3 | 6 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("COL2"="COL2")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
622 bytes sent via SQL*Net to client
639 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
scott@ORCLPDB01 2023-04-02 18:30:19> select * from t1
2 where col2 <> all( select col2 from t2);
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1275484728
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI NA | | 1 | 7 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3 | 15 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 3 | 6 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("COL2"="COL2")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
622 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
scott@ORCLPDB01 2023-04-02 18:32:48> select * from t1
2 where not exists (select 1 from t2 where col2 = t1.col2);
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1534930707
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 1 | 7 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3 | 15 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_T2 | 2 | 4 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL2"="T1"."COL2")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
622 bytes sent via SQL*Net to client
439 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed5.半连接
scott@ORCLPDB01 2023-04-02 18:42:55> set autot trace
scott@ORCLPDB01 2023-04-02 18:43:00> select * from t1
2 where col2 in (select col2 from t2);
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3783859632
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 21 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 3 | 21 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3 | 15 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_T2 | 3 | 6 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL2"="COL2")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
678 bytes sent via SQL*Net to client
414 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
scott@ORCLPDB01 2023-04-02 18:43:22> select * from t1
2 where col2 = any (select col2 from t2);
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3783859632
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 21 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 3 | 21 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3 | 15 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_T2 | 3 | 6 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL2"="COL2")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
678 bytes sent via SQL*Net to client
417 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
scott@ORCLPDB01 2023-04-02 18:43:52> select * from t1
2 where exists (select 1 from t2 where col2 = t1.col2);
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3783859632
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 21 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 3 | 21 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3 | 15 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_T2 | 3 | 6 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL2"="T1"."COL2")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
678 bytes sent via SQL*Net to client
431 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
