right outer join

最新推荐文章于 2025-05-19 09:09:32 发布

原创最新推荐文章于 2025-05-19 09:09:32 发布 · 590 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#right outer join

right outer join 专栏收录该内容

1 篇文章

订阅专栏

本文解析了SQL中的半连接概念及其实现方式，并通过具体的执行计划示例展示了如何识别半连接的存在。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select ename,sal,job ,dname,dept.deptno
from emp right outer join dept on emp.deptno=dept.deptno where dept.deptno in(10,20,40);

上面SQL与下面的SQL是等价的，只是SQL写法不同

select ename,sal,job ,dname,dept.deptno
from emp,dept where dept.deptno=emp.deptno(+) and dept.deptno in(10,20,40);

SQL> /

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2776144877

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation			   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		   |	      |     9 |   306 |     4	(0)| 00:00:01 |        |      | 	   |
|   1 |  PX COORDINATOR 		   |	      |       |       | 	   |	      |        |      | 	   |
|   2 |   PX SEND QC (RANDOM)		   | :TQ10002 |     9 |   306 |     4	(0)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN OUTER BUFFERED	   |	      |     9 |   306 |     4	(0)| 00:00:01 |  Q1,02 | PCWP | 	   |
|   4 |     BUFFER SORT 		   |	      |       |       | 	   |	      |  Q1,02 | PCWC | 	   |
|   5 |      PX RECEIVE 		   |	      |     3 |    39 |     2	(0)| 00:00:01 |  Q1,02 | PCWP | 	   |
|   6 |       PX SEND HASH		   | :TQ10000 |     3 |    39 |     2	(0)| 00:00:01 |        | S->P | HASH	   |
|   7 |        INLIST ITERATOR		   |	      |       |       | 	   |	      |        |      | 	   |
|   8 | 	TABLE ACCESS BY INDEX ROWID| DEPT     |     3 |    39 |     2	(0)| 00:00:01 |        |      | 	   |
|*  9 | 	 INDEX UNIQUE SCAN	   | PK_DEPT  |     3 |       |     1	(0)| 00:00:01 |        |      | 	   |
|  10 |     PX RECEIVE			   |	      |     9 |   189 |     2	(0)| 00:00:01 |  Q1,02 | PCWP | 	   |
|  11 |      PX SEND HASH		   | :TQ10001 |     9 |   189 |     2	(0)| 00:00:01 |  Q1,01 | P->P | HASH	   |
|  12 |       PX BLOCK ITERATOR 	   |	      |     9 |   189 |     2	(0)| 00:00:01 |  Q1,01 | PCWC | 	   |
|* 13 |        TABLE ACCESS FULL	   | EMP      |     9 |   189 |     2	(0)| 00:00:01 |  Q1,01 | PCWP | 	   |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("EMP"."DEPTNO"(+)="DEPT"."DEPTNO")
   9 - access("DEPT"."DEPTNO"=10 OR "DEPT"."DEPTNO"=20 OR "DEPT"."DEPTNO"=40)
  13 - filter("EMP"."DEPTNO"(+)=10 OR "EMP"."DEPTNO"(+)=20 OR "EMP"."DEPTNO"(+)=40)


Statistics
----------------------------------------------------------
	 24  recursive calls
	  0  db block gets
	 16  consistent gets
	  0  physical reads
	  0  redo size
	922  bytes sent via SQL*Net to client
	416  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  4  sorts (memory)
	  0  sorts (disk)
	  9  rows processed

SQL> l
  1  select ename,sal,job ,dname,dept.deptno
  2* from emp right outer join dept on emp.deptno=dept.deptno where dept.deptno in(10,20,40)
select ename,sal,job ,dname,dept.deptno
from emp,dept where dept.deptno=emp.deptno(+) and dept.deptno in(10,20,40); 


9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2776144877

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation			   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		   |	      |     9 |   306 |     4	(0)| 00:00:01 |        |      | 	   |
|   1 |  PX COORDINATOR 		   |	      |       |       | 	   |	      |        |      | 	   |
|   2 |   PX SEND QC (RANDOM)		   | :TQ10002 |     9 |   306 |     4	(0)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN OUTER BUFFERED	   |	      |     9 |   306 |     4	(0)| 00:00:01 |  Q1,02 | PCWP | 	   |
|   4 |     BUFFER SORT 		   |	      |       |       | 	   |	      |  Q1,02 | PCWC | 	   |
|   5 |      PX RECEIVE 		   |	      |     3 |    39 |     2	(0)| 00:00:01 |  Q1,02 | PCWP | 	   |
|   6 |       PX SEND HASH		   | :TQ10000 |     3 |    39 |     2	(0)| 00:00:01 |        | S->P | HASH	   |
|   7 |        INLIST ITERATOR		   |	      |       |       | 	   |	      |        |      | 	   |
|   8 | 	TABLE ACCESS BY INDEX ROWID| DEPT     |     3 |    39 |     2	(0)| 00:00:01 |        |      | 	   |
|*  9 | 	 INDEX UNIQUE SCAN	   | PK_DEPT  |     3 |       |     1	(0)| 00:00:01 |        |      | 	   |
|  10 |     PX RECEIVE			   |	      |     9 |   189 |     2	(0)| 00:00:01 |  Q1,02 | PCWP | 	   |
|  11 |      PX SEND HASH		   | :TQ10001 |     9 |   189 |     2	(0)| 00:00:01 |  Q1,01 | P->P | HASH	   |
|  12 |       PX BLOCK ITERATOR 	   |	      |     9 |   189 |     2	(0)| 00:00:01 |  Q1,01 | PCWC | 	   |
|* 13 |        TABLE ACCESS FULL	   | EMP      |     9 |   189 |     2	(0)| 00:00:01 |  Q1,01 | PCWP | 	   |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPT"."DEPTNO"="EMP"."DEPTNO"(+))
   9 - access("DEPT"."DEPTNO"=10 OR "DEPT"."DEPTNO"=20 OR "DEPT"."DEPTNO"=40)
  13 - filter("EMP"."DEPTNO"(+)=10 OR "EMP"."DEPTNO"(+)=20 OR "EMP"."DEPTNO"(+)=40)


Statistics
----------------------------------------------------------
	 25  recursive calls
	  0  db block gets
	 16  consistent gets
	  0  physical reads
	  0  redo size
	918  bytes sent via SQL*Net to client
	416  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  4  sorts (memory)
	  0  sorts (disk)
	  9  rows processed

半连接是指两个表/结果集做JOIN，但是只返回某一个表/结果集中的数据。
执行计划中，看到有 NESTED LOOPS SEMI/HASH JOIN SEMI 就表示有半连接