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.00

2.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.00

 2.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.00

2.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.00

2.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.00

2.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.01
scott@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.01
scott@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 processed

 3.表连接的方法

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 processed

3.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 processed

3.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 processed

5.半连接

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

 

posted @ 2023-04-02 18:51  竹蜻蜓vYv  阅读(24)  评论(0编辑  收藏  举报