Given a table employee( employeeID INT, mgr_employeeID INT, salary DECIMAL(10,2)) , find the managers who earn less than one or more of their subordinates.
We can write this query directly from the logic of its spec...
SELECT DISTINCT employeeID FROM employee AS e WHERE EXISTS ( SELECT employeeID FROM employee AS m WHERE m.mgr_employeeID = e.employeeID AND e.salary > m.salary );...but a JOIN (or decorrelated) version of the logic is usually much faster. This query pattern is simple:
SELECT DISTINCT m.employeeID FROM employee AS e INNER JOIN employee AS m ON e.Mgr = m.employeeID AND e.salary > m.salary;The correlated subquery version of the Not Exists query just inserts a strategic NOT :
SELECT DISTINCT employeeID FROM employee AS e WHERE NOT EXISTS ( SELECT employeeID FROM employee AS m WHERE m.Mgr = e.employeeID AND e.salary > m.salary );The decorrelated version of Not Exists uses an exclusion join--a LEFT JOIN with an IS NULL condition imposed on the right side of the join:
SELECT DISTINCT m.employeeID FROM employee AS e LEFT JOIN employee AS m ON e.Mgr = m.employeeID AND e.salary > m.salary WHERE m.employeeID IS NULL; Last updated 19 Nov 2018 |