i'm new here, i suppose that i will get some help.
I have two queries
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)
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!Answer1:
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) <a href="http://sqlfiddle.com/#!4/eaf0d/2/0" rel="nofollow">http://sqlfiddle.com/#!4/eaf0d/2/0</a>
Is not the same as this:(your 1st query) <a href="http://sqlfiddle.com/#!4/eaf0d/1/0" rel="nofollow">http://sqlfiddle.com/#!4/eaf0d/1/0</a>
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)Answer2:
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
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.