65854

Difference in two SQL query, but same result

Question:

i'm new here, i suppose that i will get some help.

I have two queries

<strong>FIRST</strong>

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)

<strong>SECOND</strong>

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 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.

Recommend

  • My variable is defined but python is saying it isn't?
  • SQL displaying same row
  • Google drive usage statistics difference between google drive UI and API
  • Group sum from two tables according to date in MySQL
  • Equivalent of GroupBy and Having clause in Relational Algebra
  • Beginner sql join query
  • WHERE If condition is not met get all values including NULL
  • Get count of zeros in an integer using MySQL
  • Single-row subquery returns more than one row
  • Get count of row and sum group by quarter of date, if another column doesnt exits a value in SQL Ser
  • How do I give each person in this list a raise
  • Replacing NULL value in current row based on 'closest' matching 'earlier' row. (
  • Symfony2 Form is always empty after submitting
  • Single Table Inheritance in Rails 4
  • Using Select and where statement in Criteria
  • Getting data from database using HQL
  • How to merge two Request in Laravel
  • Read a file in “chunks” using PHP
  • Express displaying mongodb documents in Jade
  • Leaflet z-index
  • auth.provider is not set to 'password' when user signs-in with email and password
  • How can I extend PHP DOMElement?
  • How to make R's read_csv2() recognise the text characters properly
  • Android Activity.onWindowFocusChanged doesn't get called from within TabHost
  • C: Incompatible pointer type initializing
  • How to write order and limit within cakephp joins array
  • Installing iPhone App to iPhone
  • Abort upload large uploads after reading headers
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • Display Images one by one with next and previous functionality
  • Compare two NSDates in iPhone
  • bootstrap to use multiple ng-app
  • Data Validation Drop Down Box Arrow Disappearing
  • How to get icons for entities from eclipse?
  • SQL merge duplicate rows and join values that are different
  • AngularJs get employee from factory
  • Can Visual Studio XAML designer handle font family names with spaces as a resource?
  • Turn off referential integrity in Derby? is it possible?
  • JaxB to read class hierarchy