刚刚发现这个问题,吓了我一跳,不知道算不算是Oracle的Bug(我对Oracle不熟):
有两个表A、B,其中A表有字段a,B表中无此字段。由于失误,发现这样的sql语句居然顺利运行:SELECT * FROM A WHERE a in (SELECT a FROM B);Oracle居然毫无报错,直接运行了。运行的效果就是
SELECT * FROM A如果单独运行in后面的语句,如
SELECT a FROM B
马上就报错,说B里没有a字段!
我靠,这个真是Oracle的Bug么?也忒严重了点吧?请各位指教啊!!!Oracle版本:10g Enterprise Edtion 10.1.0.2.0
Win 2003 Server
有两个表A、B,其中A表有字段a,B表中无此字段。由于失误,发现这样的sql语句居然顺利运行:SELECT * FROM A WHERE a in (SELECT a FROM B);Oracle居然毫无报错,直接运行了。运行的效果就是
SELECT * FROM A如果单独运行in后面的语句,如
SELECT a FROM B
马上就报错,说B里没有a字段!
我靠,这个真是Oracle的Bug么?也忒严重了点吧?请各位指教啊!!!Oracle版本:10g Enterprise Edtion 10.1.0.2.0
Win 2003 Server
你对in的嵌套查询原理不清楚
第一句SQL,你是作为where的条件,可以没有去select的字段进行判断!
select a from B 的结果就是null
第二句,是select 的语句,要判断选择的字段
--这个问题初一看是觉得挺奇怪的 但是 如果你看下执行计划的话就觉得可以理解了
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 1014 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 1014 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)
--这个语句是执行的时候就会提示错误了.
内层子查询里可以引用外层表的字段.
即使不要求实现,但是按sql标准,最后的结果应该是一样的.
看来以后要养成加别名的好习惯了:
SELECT * FROM A x WHERE x.a in (SELECT y.a FROM B y);
这样就不会出问题了。
谢谢各位!