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:
- Inner join the table (t1) to itself (t2) on the grouping key.
- Add the condition on which you wish to find existing rows to the Join clause.
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:
- Left join the table to itself on the grouping key.
- Add the condition on which you wish to find existing rows to the Join clause.
- For the condition on which you wish to find missing rows on the right side,
- (a) add the value condition to the Join clause, and
- (b) in the Where clause, add an 'is null' condition for the right side:
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;
|
|