两个SQL查询的区别，但结果相同 [英] Difference in two SQL query, but same result

问题描述

我是新来的，我想我会得到一些帮助。

我有两个查询

FIRST

  SELECT ename，deptno，sal，comm 
 FROM emp 
 WHERE sal IN 
（SELECT sal FROM emp WHERE deptno = 30）
和comm IN 
（SELECT comm FROM emp WHERE deptno = 30）
  
 
 
 
  SECOND  
  SELECT ename，deptno，sal，comm 
 FROM emp 
 WHERE（sal，comm）IN 
（SELECT sal，comm FROM emp WHERE deptno = 30）
  
它们产生相同的结果，但它们之间的区别是什么？
 
 
 
也许SECOND将返回比第一个更快的结果....或??? 
 
 
 
非常感谢！
解决方案
这些是不一样的查询。他们可能会产生相同的数据，但这只是因为你的数据集没有说明差别。
 
 
 
正如你可以看到的：（你的第二个查询）
  http://sqlfiddle.com/#!4/eaf0d/2/0  
 
 
 
与以下内容不同：（您的第一个查询）
  http://sqlfiddle.com/#!4/eaf0d/1/0  
 
 
 
您的第一个查询将返回任何具有部门30中的任何人的薪水的任何雇员，以及与部门30中的任何人相同的佣金，但是每个人都被允许是不同的人，即。工资可能是一个匹配w /人X和佣金可能是一个匹配w /人Y 
 
 
 
您的第二个查询将返回任何员工具有相同的薪水和佣金至少一个员工在部门30.但不同于第一个查询，薪水和佣金都必须是部门30中的同一个员工的匹配，你不能有一个匹配人X和另一个人Y. 
 
 
 
换句话说，你的第二个查询更有选择性。
 
 
 
这就是为什么'Fish'在我的示例数据集中，他的薪水与'Brian'匹配（但不是Brian的佣金），他有一个匹配w / Doodle的开始（但不是Doodle的薪水）。 
 
 
 
他被包括在另一个查询中，因为一个或另一个确实匹配w /至少1个员工（但是这些员工是不同的人）。
 
 
 
性能方面，下面的查询将导致更少的表扫描， ，但是你应该只使用它，如果它匹配你的意图基于我的描述上面的2个查询之间的功能差异：
  SELECT ename，deptno，sal，comm 
 FROM emp 
 WHERE（sal，comm）IN 
（SELECT sal，comm FROM emp WHERE deptno = 30）
  
 
i'm new here, i suppose that i will get some help.

I have two queries 

FIRST
SELECT  ename, deptno, sal, comm
FROM    emp
WHERE   sal IN
        (SELECT sal FROM emp WHERE deptno = 30)
and comm IN
(SELECT comm FROM emp WHERE deptno = 30)
SECOND
SELECT  ename, deptno, sal, comm
FROM    emp
WHERE   (sal, comm) IN
(SELECT sal, comm FROM emp WHERE deptno = 30)
They produce same result, but what is the difference between them???

Maybe the SECOND will return the result faster than the first one....or???

Thanks a lot!
解决方案
These are not the same queries at all. They might happen to produce the same data, but that's just because your dataset isn't illustrating the difference.

As you can see, this: (your 2nd query)
http://sqlfiddle.com/#!4/eaf0d/2/0

Is not the same as this:(your 1st query)
http://sqlfiddle.com/#!4/eaf0d/1/0

Your 1st query will return any employee who has the salary of anyone in department 30, and the same commission as anyone in department 30, but each of those is allowed to be different people, ie. the salary might be a match w/ person X and commision might be a match w/ person Y

Your 2nd query will return any employee who has the same salary AND commission of at least one employee in department 30. But unlike the first query, the salary and commission both have to be a match for the same employee in department 30, you can't have one match up with person X and the other with person Y.

In other words, your second query is much more selective.

This is why 'Fish' gets excluded in my example data set, he has a salary that matches up with 'Brian' (but not a match w/ Brian's commission), and he has a match w/ Doodle's commmission (but not Doodle's salary). Because there is not a single match for an employee on the basis of BOTH commission and salary, he got excluded.

He was included in the other query because one or the other did match up w/ at least 1 employee (but those employees were different people).

Performance-wise, the query below will result in fewer table scans and may run faster, but you should only use it if it matches your intentions based on my description of the functional difference between the 2 queries above:
SELECT  ename, deptno, sal, comm
FROM    emp
WHERE   (sal, comm) IN
(SELECT sal, comm FROM emp WHERE deptno = 30)


                        
这篇关于两个SQL查询的区别，但结果相同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！