--这个问题初一看是觉得挺奇怪的 但是 如果你看下执行计划的话就觉得可以理解了

scott@YPCOST> select * from emp where empno in (select empno from dept);



     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO

---------- ---------- --------- ---------- ---------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 1980-12-17        800        900         20

      7499 ALLEN      SALESMAN        7698 1981-02-20       1600        300         30

      7521 WARD       SALESMAN        7698 1981-02-22       1250        500         30

      7566 JONES      MANAGER         7839 1981-04-02       2975        900         20

      7654 MARTIN     SALESMAN        7698 1981-09-28       1250       1400         30

      7698 BLAKE      MANAGER         7839 1981-05-01       2850                    30

      7782 CLARK      MANAGER         7839 1981-06-09       2450                    10

      7788 SCOTT      ANALYST         7566 1987-04-19       3000        900         20

      7839 KING       PRESIDENT            1981-11-17       5000                    10

      7844 TURNER     SALESMAN        7698 1981-09-08       1500          0         30

      7876 ADAMS      CLERK           7788 1987-05-23       1100        900         20

      7900 JAMES      CLERK           7698 1981-12-03        950                    30

      7902 FORD       ANALYST         7566 1981-12-03       3000        900         20

      7934 MILLER     CLERK           7782 1982-01-23       1430                    10



14 rows selected.





Execution Plan

----------------------------------------------------------

Plan hash value: 3989606110



----------------------------------------------------------------------------------------------------

| Id  | Operation          | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                                |     1 |    38 |    10   (0)| 00:00:01 |

|*  1 |  FILTER            |                                |       |       |            |          |

|   2 |   TABLE ACCESS FULL| EMP                            |    14 |   532 |     3   (0)| 00:00:01 |

|*  3 |   FILTER           |                                |       |       |            |          |

|   4 |    INDEX FULL SCAN | BIN$8mPMMewIQa6BxCHkeZP6Wg==$0 |     1 |       |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "DEPT" "DEPT" WHERE :B1=:B2))

   3 - filter(:B1=:B2)



--从上面的执行计划可以查出oracle会将in子查询解析成exists

--所有查询语句会被解析成

select * from emp where exists(select 0 from dept where empno=empno);

--再看下这个语句的执行计划

scott@YPCOST> select * from emp where exists(select 0 from dept where empno=empno);



     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO

---------- ---------- --------- ---------- ---------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 1980-12-17        800        900         20

      7499 ALLEN      SALESMAN        7698 1981-02-20       1600        300         30

      7521 WARD       SALESMAN        7698 1981-02-22       1250        500         30

      7566 JONES      MANAGER         7839 1981-04-02       2975        900         20

      7654 MARTIN     SALESMAN        7698 1981-09-28       1250       1400         30

      7698 BLAKE      MANAGER         7839 1981-05-01       2850                    30

      7782 CLARK      MANAGER         7839 1981-06-09       2450                    10

      7788 SCOTT      ANALYST         7566 1987-04-19       3000        900         20

      7839 KING       PRESIDENT            1981-11-17       5000                    10

      7844 TURNER     SALESMAN        7698 1981-09-08       1500          0         30

      7876 ADAMS      CLERK           7788 1987-05-23       1100        900         20

      7900 JAMES      CLERK           7698 1981-12-03        950                    30

      7902 FORD       ANALYST         7566 1981-12-03       3000        900         20

      7934 MILLER     CLERK           7782 1982-01-23       1430                    10



14 rows selected.





Execution Plan

----------------------------------------------------------

Plan hash value: 3989606110



----------------------------------------------------------------------------------------------------

| Id  | Operation          | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                                |     1 |    38 |    10   (0)| 00:00:01 |

|*  1 |  FILTER            |                                |       |       |            |          |

|   2 |   TABLE ACCESS FULL| EMP                            |    14 |   532 |     3   (0)| 00:00:01 |

|*  3 |   FILTER           |                                |       |       |            |          |

|   4 |    INDEX FULL SCAN | BIN$8mPMMewIQa6BxCHkeZP6Wg==$0 |     1 |       |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "DEPT" "DEPT" WHERE :B1=:B2))

   3 - filter(:B1=:B2)

--你会发现这两个语句的执行计划是完全一样的，所有这个应该是可以解释的 



--如果你用select * from emp where empno in (select t.empno from dept t)

--这个语句是执行的时候就会提示错误了.