Uzumaki Naruto Uzumaki Naruto - 10 days ago 6
MySQL Question

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.

Answer

Where condition are execute before aggregate functions

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

FIRST WHERE salary NOT IN (select MAX(salary) from employee) =2000 GET Execute and
value 2000 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

Comments