0

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!

Stack
  • 3
  • 2

2 Answers2

3

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)
Brian DeMilia
  • 13,103
  • 1
  • 23
  • 33
  • ok, but lets say that we dont need set that will give us different results, what's the difference in that case? – Stack Jul 26 '14 at 23:27
  • @Stack the second would likely have fewer table scans (you can take a look at the execution plans in the 2 fiddles I posted), so that may perform better, however I would only use that query if it's actually what you want (even though the 2 queries return the same result right now, they might not return the same result in the future, as new data is added) – Brian DeMilia Jul 26 '14 at 23:30
0

I think you only need this:

SELECT  ename, deptno, sal, comm
FROM    emp
WHERE   deptno = 30

@Brian DeMilia just made a great observation why the above may be wrong. You may want this:

SELECT  emp.ename, emp.deptno, emp.sal, emp.comm
FROM    emp
JOIN    emp AS emp2 
WHERE   emp2.deptno = 30 AND emp.comm = emp2.comm AND emp.sal = emp2.sal

It might be better to use HAVING for the comm and sal matches. Of course you should pick the simplest thing that works. And depending on your RLDBM, the keyword AS may cause a failure -- remove it if so.

No one can answer your question about speed without more information. It is hard to imagine any difference in speed between any of these, but much could depend on the size of the recordsets, the choice of RDBMS (database program), and table indexes.

Smandoli
  • 6,919
  • 3
  • 49
  • 83
  • it's very likely that he is querying to find people w/ the same salary or commission as those employees working in department 30 (but who they themselves may not be in department 30), which this does not do. – Brian DeMilia Jul 26 '14 at 22:39