大家帮忙分析一下，这两个语句执行的结果为什么不同！

Question

本帖最后由 sh_zhuting 于 2010-11-03 15:28:25 编辑

Answer 1

--通过执行计划分析，你就知道原因了，全表扫描次数不一样
1、成对比较：
EXPLAIN PLAN FOR 
SELECT *
  FROM Emp
 WHERE (Sal, Nvl(Comm, 0)) IN
       (SELECT Sal, Nvl(Comm, 0) FROM Emp WHERE Deptno = 30);SELECT * FROM TABLE(dbms_xplan.display);--执行计划：
Plan hash value: 1026997002                                                
                                                                           
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    47 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |     1 |    47 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    16 |   608 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     8 |    72 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
                                                                           
Predicate Information (identified by operation id):                        
---------------------------------------------------                        
                                                                           
   1 - access("SAL"="SAL" AND NVL("COMM",0)=NVL("COMM",0))                 
   3 - filter("DEPTNO"=30)                                                 
2、非成对比较：
EXPLAIN PLAN FOR 
SELECT *
  FROM Emp
 WHERE Sal IN (SELECT Sal FROM Emp WHERE Deptno = 30)
   AND Nvl(Comm, 0) IN (SELECT Nvl(Comm, 0) FROM Emp WHERE Deptno = 30);SELECT * FROM TABLE(dbms_xplan.display);--执行计划：
Plan hash value: 4041616129                                                 
                                                                            
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     2 |   100 |    10  (10)| 00:00:01 |
|*  1 |  HASH JOIN SEMI     |      |     2 |   100 |    10  (10)| 00:00:01 |
|*  2 |   HASH JOIN SEMI    |      |     2 |    86 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    16 |   608 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| EMP  |     8 |    40 |     3   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL | EMP  |     8 |    56 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
                                                                            
Predicate Information (identified by operation id):                         
---------------------------------------------------                         
                                                                            
   1 - access("SAL"="SAL")                                                  
   2 - access(NVL("COMM",0)=NVL("COMM",0))                                  
   4 - filter("DEPTNO"=30)                                                  
   5 - filter("DEPTNO"=30)                                                  

Answer 2

谢谢！问题是除了语句执行成本不一样外，查询的结果集也不相同，《精通Oracle10PLSQL编程》中的实例。

Answer 3

《精通Oracle10PLSQL编程》中的实例显示，第一条查询语句返回6行记录
第二条查询语句返回7条记录

Answer 4

--是不是数据的问题？
--我觉得应该是返回结果一样才对！第二种肯定效率低于第一种，因为少了全表扫描次数 CPU那些也少了
--从执行计划可以看出来--我执行了下两个结果，发现时一样的：SQL*Plus: Release 8.0.6.0.0 - Production on 星期三 11月 3 15:44:23 2010(c) Copyright 1999 Oracle Corporation.  All rights reserved.
Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> set linesize 1000
SQL> select * from emp;     EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- -----
      7369 SMITH                CLERK                    7902 17-12月-80        800                    20
      7499 ALLEN                SALESMAN                 7698 20-2月 -81       1600        300         30
      7521 WARD                 SALESMAN                 7698 22-2月 -81       1250        500         30
      7566 JONES                MANAGER                  7839 02-4月 -81       2975                    20
      7654 MARTIN               SALESMAN                 7698 28-9月 -81       1250       1400         30
      7698 BLAKE                MANAGER                  7839 01-5月 -81       2850                    30
      7782 CLARK                MANAGER                  7839 09-6月 -81       1450                    10
      7788 SCOTT                ANALYST                  7566 09-12月-82       3000                    20
      7839 KING                 PRESIDENT                     17-11月-81       5000                    10
      7844 TURNER               SALESMAN                 7698 08-9月 -81       1500          0         30
      7876 ADAMS                CLERK                    7788 12-1月 -83       1100                    20     EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- -----
      7900 JAMES                CLERK                    7698 03-12月-81        950                    30
      7902 FORD                 ANALYST                  7566 03-12月-81       3000                    20
      7934 MILLER               CLERK                    7782 23-1月 -82       1300                    10
      7955 BLACK                SALESMAN                 7698 28-3月 -82       1250       1400         30
      7999 MAXTON               MANAGER                  7698 28-3月 -82      11250       1400         3016 rows selected.SQL> --1、成对比较：
SQL> SELECT *
  2    FROM Emp
  3   WHERE (Sal, Nvl(Comm, 0)) IN
  4         (SELECT Sal, Nvl(Comm, 0) FROM Emp WHERE Deptno = 30);     EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- -----
      7499 ALLEN                SALESMAN                 7698 20-2月 -81       1600        300         30
      7521 WARD                 SALESMAN                 7698 22-2月 -81       1250        500         30
      7955 BLACK                SALESMAN                 7698 28-3月 -82       1250       1400         30
      7654 MARTIN               SALESMAN                 7698 28-9月 -81       1250       1400         30
      7698 BLAKE                MANAGER                  7839 01-5月 -81       2850                    30
      7844 TURNER               SALESMAN                 7698 08-9月 -81       1500          0         30
      7900 JAMES                CLERK                    7698 03-12月-81        950                    30
      7999 MAXTON               MANAGER                  7698 28-3月 -82      11250       1400         308 rows selected.SQL> --2、非成对比较：
SQL> SELECT *
  2    FROM Emp
  3   WHERE Sal IN (SELECT Sal FROM Emp WHERE Deptno = 30)
  4     AND Nvl(Comm, 0) IN (SELECT Nvl(Comm, 0) FROM Emp WHERE Deptno = 30);     EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- -----
      7499 ALLEN                SALESMAN                 7698 20-2月 -81       1600        300         30
      7654 MARTIN               SALESMAN                 7698 28-9月 -81       1250       1400         30
      7955 BLACK                SALESMAN                 7698 28-3月 -82       1250       1400         30
      7521 WARD                 SALESMAN                 7698 22-2月 -81       1250        500         30
      7698 BLAKE                MANAGER                  7839 01-5月 -81       2850                    30
      7844 TURNER               SALESMAN                 7698 08-9月 -81       1500          0         30
      7900 JAMES                CLERK                    7698 03-12月-81        950                    30
      7999 MAXTON               MANAGER                  7698 28-3月 -82      11250       1400         308 rows selected.SQL> 

Answer 5

Nvl(Comm, 0) 要写成 Comm，如果Comm的值为 null时 IN 会失效

Answer 6

SELECT *
  FROM Emp
 WHERE Sal IN (SELECT Sal FROM Emp WHERE Deptno = 30)
   AND Nvl(Comm, 0) IN (SELECT Nvl(Comm, 0) FROM Emp WHERE Deptno = 30);中 
 只要 0 in(SELECT Nvl(Comm, 0) FROM Emp WHERE Deptno = 30); 时，会把所有的数据都显示出来的

Answer 7

--刚才马虎了，没有注意子查询中的where条件。
--这两条语句不等价，比如在表中存在下面的数据：
sal    comm    deptno
1       A       30
2       B       30
1       B       29
2       A       28那么成对查询得到的结果是2条，不成对查询得到的结果是4条。成对查询例句：
WITH EMP AS 
(
SELECT 1 SAL, 'A' COMM, 30 DEPTNO FROM DUAL UNION ALL
SELECT 2 , 'B' , 30  FROM DUAL UNION ALL
SELECT 1 , 'B' , 29  FROM DUAL UNION ALL
SELECT 2 , 'A' , 28 DEPT FROM DUAL
)
SELECT *
  FROM Emp
 WHERE (Sal, Nvl(Comm, 0)) IN
       (SELECT Sal, Nvl(Comm, 0) FROM Emp WHERE Deptno = 30);不成对查询例句：
WITH EMP AS 
(
SELECT 1 SAL, 'A' COMM, 30 DEPTNO FROM DUAL UNION ALL
SELECT 1 , 'B' , 29  FROM DUAL UNION ALL
SELECT 2 , 'A' , 28 DEPT FROM DUAL
)
SELECT *
  FROM Emp
 WHERE (Sal, Nvl(Comm, 0)) IN
       (SELECT Sal, Nvl(Comm, 0) FROM Emp WHERE Deptno = 30);

Answer 8

第一个语句需要同一条数据中的comm和sal都满足条件.第二个不限定为同一条数据.

Answer 9

你这个根本就不正确，原表数据都不一样了，结果肯定不同啊！
我在我的资料库中根据两个SQL查询出来是一样的结果，从楼主给的书上的图片来看，第二个SQL多了的那条记录是EMPNO=7844的记录，comm为0，但是我的资料库中也存在这样的数据，可是我查询出来就是都是一样的结果！
从根本来说，两个SQL我认为是一样的！
详细的测试如我1楼和5楼的分析。

Answer 10

我在17楼的不查询的例句写错了，修正如下：不成对查询例句：
WITH EMP AS 
(
SELECT 1 SAL, 'A' COMM, 30 DEPTNO FROM DUAL UNION ALL
SELECT 2 , 'B' , 30  FROM DUAL UNION ALL
SELECT 1 , 'B' , 29  FROM DUAL UNION ALL
SELECT 2 , 'A' , 28 DEPT FROM DUAL
)
SELECT *
  FROM Emp
 WHERE (Sal, Nvl(Comm, 0)) IN
       (SELECT Sal, Nvl(Comm, 0) FROM Emp WHERE Deptno = 30);

Answer 11

刚看语句还以为一样的 看了结果确实不一样 估计第二句并没有完全满足 是列comm有null的值造成的

Answer 12

看错了 是多了CLARK这条记录上面分析就推翻，我再分析下呢，可能我们错了！

Answer 13

改为这样
WITH EMP AS 
(
SELECT 1 SAL, 'A' COMM, 30 DEPTNO FROM DUAL UNION ALL
SELECT 2 , 'B' , 30  FROM DUAL UNION ALL
SELECT 1 , 'B' , 29  FROM DUAL UNION ALL
SELECT 2 , 'A' , 28 DEPT FROM DUAL
)
SELECT *
  FROM Emp
 WHERE Sal IN (SELECT Sal FROM Emp WHERE Deptno = 30)
   AND Nvl(Comm, -1) IN (SELECT Nvl(Comm, -1) FROM Emp WHERE Deptno = 30);

Answer 14

哦 ，我懂了，书上是对的，两个SQL语句是不一样，原因是第一个是联合匹配，也就是说SAL，comm必须全部都和子查询里的匹配成功，第二个是单独匹配的，SAL和comm是不存在逻辑关联性，有可能会导致如书上的那笔资料CLARK SAL=1500,COMM=300 记录出现。

Answer 15

非成对比较：
WITH EMP AS 
(
SELECT 1 SAL, 'A' COMM, 30 DEPTNO FROM DUAL UNION ALL
SELECT 2 , 'B' , 30  FROM DUAL UNION ALL
SELECT 1 , 'B' , 29  FROM DUAL UNION ALL
SELECT 2 , 'A' , 28 DEPT FROM DUAL
)
SELECT *
  FROM Emp
 WHERE Sal IN (SELECT Sal FROM Emp WHERE Deptno = 30)
   AND Nvl(Comm, -1) IN (SELECT Nvl(Comm, -1) FROM Emp WHERE Deptno = 30);实际上相当于
WITH EMP AS 
(
SELECT 1 SAL, 'A' COMM, 30 DEPTNO FROM DUAL UNION ALL
SELECT 2 , 'B' , 30  FROM DUAL UNION ALL
SELECT 1 , 'B' , 29  FROM DUAL UNION ALL
SELECT 2 , 'A' , 28 DEPT FROM DUAL
)
SELECT *
  FROM Emp
 WHERE  Nvl(Comm, -1) IN ('A','B')
 and Sal in (1,2);
这样就比较好理解了，当 Nvl(Comm, -1)为A的时候Sal可以为1,2
当Nvl(Comm, -1)为B的时候Sal也可以为1,2所以查询出来的结果是4条记录，而成对查询结果为2条记录

Answer 16

这可能就是 （col1,col2）in( value1,value2) 与 col1=value1 and col2=value2的区别今天学到了。呵呵  

Answer 17

26楼改成-1是什么情况？这个和-1还是0没有关系的。我解释下两者在逻辑上的区别吧：第1条语句只有配对出现的sal和comm才符合条件，那么如果sal在deptno = 30列表中的第1条数据，而comm在deptno = 30的第2条数据，是不符合条件的。而第2条语句不需要配对，那么如果sal在deptno = 30列表中的第1条数据，而comm在deptno = 30的第2条数据，是符合条件的。

Answer 18

我改的这段，在看看你上传的代码就知道了！跟nvl（……）没关系，这是我从书上的例子直接copy上去的！ WHERE Sal IN (SELECT Sal FROM Emp WHERE Deptno = 30)
   AND Nvl(Comm, -1) IN (SELECT Nvl(Comm, -1) FROM Emp WHERE Deptno = 30);

Answer 19

是跟nvl没关系，看我27楼和29楼解释就明白了，两个结果集的完全匹配和不完全匹配的问题