老问题了, 只有在anti-join中, 才要注意LZ提的问题, 使用not exists可以避免,这主要是优化器处理的方法不同!使用not exists是,执行计划的谓词会自动加上一个filter, 这个filter就是避免发生LZ提的问题[例如如下的语句3,就会加上3 - filter("E2"."COMM" IS NOT NULL)]!
此类问题, 目前面试问的比较多,但回答没有必要如此详细!
--1
SQL> select * from emp e1 where e1.comm in(select e2.comm from emp e2);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7499 ALLEN SALESMAN 7698 2/20/1981 1600.00 300.00 30
7521 WARD SALESMAN 7698 2/22/1981 1250.00 500.00 30
7654 MARTIN SALESMAN 7698 9/28/1981 1250.00 1400.00 30
7844 TURNER SALESMAN 7698 9/8/1981 1500.00 0.00 30
--2
SQL> select * from emp e1 where e1.comm not in(select e2.comm from emp e2);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
--3
SQL> select * from emp e1 where not exists(select 1 from emp e2 where e2.comm = e1.comm);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7934 MILLER CLERK 7782 1/23/1982 1300.00 10
7902 FORD ANALYST 7566 12/3/1981 3000.00 20
7900 JAMES CLERK 7698 12/3/1981 950.00 30
7876 ADAMS CLERK 7788 5/23/1987 1100.00 20
7839 KING PRESIDENT 11/17/1981 5000.00 10
7788 SCOTT ANALYST 7566 4/19/1987 3000.00 20
7782 CLARK MANAGER 7839 6/9/1981 2450.00 10
7698 BLAKE MANAGER 7839 5/1/1981 2850.00 30
7566 JONES MANAGER 7839 4/2/1981 2975.00 20
7369 SMITH CLERK 7902 12/17/1980 800.00 20
10 rows selected