CODEWITHSUNDEEP
mysqlsql
Uzumaki Naruto
Uzumaki Naruto

Reputation: 753

How does this subquery work?

select max(salary) 
from employee 
WHERE salary NOT IN (select MAX(salary) from employee)

The query above returns 2nd highest salary. Now I know there are other ways to find 2nd highest salary but I can't understand how the above query is working. This might be very easy but could someone please break it down so that it is understandable.

Upvotes: 0

Views: 111

Answers (4)

Balan
Balan

Reputation: 421

'Where' conditions are execute before aggregate function

For more clearification
Here in employee table has salary like 10000,15000,20000

FIRST WHERE salary NOT IN (select MAX(salary) from employee) =20000 GET Execute and
value 20000 stored for comparision

THEN IT CHECK one by one from MAX value LIKE 20000 != 20000 false

THEN CHECK other MAX value 15000 != 20000 true

so 15000 will return

To know more about SQL execution order see the image below

enter image description here

Upvotes: 1

xQbert
xQbert

Reputation: 35323

You need to understand the order in which SQL executes. In this case, the where clause executes against the set of records in the employee table eliminating the employee with the highest salary from the dataset the engine is working with (a subset of employee now excluding the highest paid). Then the select executes returning the employee with the highest salary left in the set.

John    45,000
Paul    26,000
George  87,000
Ringo   33,000

So the engine identifies George as having 87,000 the highest and eliminates it from the data set (note: All indivduals having this max salary are excluded not just 1 so as Linkan points out, if Brian has a salary of 87,000 it too would be eliminated) ... leaving the following in memory.

Thus the where clause WHERE salary NOT IN (select MAX(salary) from employee) has done its job.

John    45,000
Paul    26,000
Ringo   33,000

The engine then looks through and finds John is now the highest (max) in the reamining set, and thus eliminates the other 2 and returns: and the select completes it's job: select max(salary) 

45,000

So the order in which the SQL executes is generalized as:

  1. FROM
  2. JOINS
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. SELECT
  7. ORDER BY

Upvotes: 1

e4c5
e4c5

Reputation: 53734

This is a potentially very slow and complex query which does the same as this one:

SELECT salary FROM employee ORDER BY salary DESC limit 1,1

Your subquery finds the highest salary, then you are doing an outer query to find all the salaries that are not the higest salaries and then taking it's max.

Find highest

select MAX(salary) from employee

Find salaries that are not highest as follows:

WHERE NOT IN (select MAX(salary) from employee)

find maximum salary which is not the highest!!

select max(salary) ....

Upvotes: 1

Viki888
Viki888

Reputation: 2774

Say employee has below records

EmpId Salary

1     50000
2     40000
3     35000
4     30000
5     25000
6     20000

The sub-query in where clause returns the below result which is

Max(Salary)
50000

Then in the where clause you have used Salary NOT IN (select MAX(salary) from employee) which implies Salary NOT IN (50000), which will give you the result

Max(Salary)
40000

Hence the result is the second largest value.

Upvotes: 0

Related Questions