oracle 半联结 IN、EXISTS、=ANY_=any是半联结吗-优快云博客

本文链接：https://blog.youkuaiyun.com/feier7501/article/details/21833059

本文通过实际案例对比了SQL中IN与EXISTS子查询的执行效率，展示了两者在某些情况下可以被优化器转化为相同的执行计划，同时讨论了=ANY的用法。

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

用SCOTT/TIGER登录。

创建小表：‘

CREATE TABLE EMP1 AS WITH TEMP_EMP AS (SELECT ROWNUM RN, E.* FROM EMP E) SELECT * FROM TEMP_EMP WHERE RN BETWEEN 4 AND 10;

开启AUTOTRACE：

SQL> SET AUTOTRACE ON

查看IN的执行计划：

SQL> SELECT EMPNO, ENAME FROM EMP1 WHERE EMPNO IN (SELECT EMPNO FROM EMP);

     EMPNO ENAME
---------- ----------
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER

已选择7行。


执行计划
----------------------------------------------------------
Plan hash value: 1034424604

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     7 |   168 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |        |     7 |   168 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP1   |     7 |   140 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_EMP |     1 |     4 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   3 - access("EMPNO"="EMPNO")

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        589  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          7  rows processed

SQL>

看EXISTS的执行计划：

SQL> SELECT T1.EMPNO, T1.ENAME FROM EMP1 T1 WHERE EXISTS (SELECT T2.EMPNO FROM E
MP T2 WHERE T1.EMPNO = T2.EMPNO);

     EMPNO ENAME
---------- ----------
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER

已选择7行。


执行计划
----------------------------------------------------------
Plan hash value: 3083125084

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     7 |   168 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI |        |     7 |   168 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP1   |     7 |   140 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_EMP |    14 |    56 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   3 - access("T1"."EMPNO"="T2"."EMPNO")

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        589  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          7  rows processed

SQL>

所以，IN和EXISTS其实是一样的。

事实是优化器能够并且确实将这两种形式的查询转换成了同样的语句。

=ANY：

SELECT EMPNO, ENAME FROM EMP1 WHERE EMPNO =ANY (SELECT EMPNO FROM EMP);

其实和IN一样。