CODEWITHSUNDEEP
sql
ketflix
ketflix

Reputation: 25

Unexpected result of a SQL query

Can anyone explain why this query:

SELECT employee_id, last_name, salary 
FROM   employees
WHERE  department_id IN (SELECT department_id
                     FROM   employees
                     WHERE  last_name LIKE '%u%'
                     )
AND  salary > (SELECT AVG(salary)  
           FROM employees);

returns way less rows than this nested one: 

SELECT employee_id, last_name, salary 
FROM   employees
WHERE  department_id IN (SELECT department_id
                     FROM   employees
                     WHERE  last_name LIKE '%u%'
                     AND  salary > (SELECT AVG(salary)  
                                    FROM employees);
                     )

Upvotes: 2

Views: 131

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270201

The first returns all employees who meet the following conditions:

  • The employee is in a department has a "u" employee.
  • The employee has a salary larger than the average.

The second returns all employees who meet these conditions:

  • The employee is in a department that has a "u" employee who has a salary larger than the average.

The two are very different conditions. I wouldn't expect them to return the same result set.

Also, whenever you have more than one table in a query, you should use table aliases that are abbreviations of the table name and you should qualify all column names.

Upvotes: 2

Related Questions