Schema and sample data
| emp_id | name | salary | manager_id |
|---|---|---|---|
| 1 | Alice Nguyen | 150000.00 | NULL |
| 2 | Bruno Costa | 90000.00 | 1 |
| 3 | Chen Wei | 95000.00 | 2 |
| 4 | Diana Flores | 70000.00 | 2 |
| 5 | Ethan Park | 88000.00 | 1 |
| 6 | Farah Ali | 160000.00 | 1 |
| 7 | Grace Kim | 60000.00 | 3 |
Show setup SQL (copy to run)
CREATE TABLE Employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
salary DECIMAL(10,2) NOT NULL,
manager_id INT NULL
);
INSERT INTO Employees (emp_id, name, salary, manager_id) VALUES
(1, 'Alice Nguyen', 150000.00, NULL),
(2, 'Bruno Costa', 90000.00, 1),
(3, 'Chen Wei', 95000.00, 2),
(4, 'Diana Flores', 70000.00, 2),
(5, 'Ethan Park', 88000.00, 1),
(6, 'Farah Ali', 160000.00, 1),
(7, 'Grace Kim', 60000.00, 3);The Employees table stores each person along with the manager_id that points back to their manager's own emp_id. Top level staff have a NULL manager_id. Return the employee_name and salary of every employee who earns strictly more than their direct manager. Sort the result by salary from highest to lowest.
Expected result
| employee_name | salary |
|---|---|
| Farah Ali | 160000.00 |
| Chen Wei | 95000.00 |
Show hint
You need the same table twice in one query: once as the employee and once as the manager. Join the employee copy to the manager copy by matching e.manager_id to m.emp_id, then compare the two salary columns. Give each copy a different alias so the engine can tell them apart.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT e.name AS employee_name, e.salary
FROM Employees AS e
JOIN Employees AS m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary
ORDER BY e.salary DESC;This is a self join: the Employees table is referenced twice, once aliased as e for the employee and once as m for the manager. The aliases are what make it work, because without them the engine cannot tell which salary or emp_id you mean. The join condition e.manager_id = m.emp_id lines each employee row up next to the single row that describes their manager. See the SQL joins guide for more on how a table can join to itself.
With employee and manager side by side, the filter WHERE e.salary > m.salary keeps only the pairs where the employee out earns the manager. Chen Wei on 95000 beats Bruno Costa on 90000, and Farah Ali on 160000 beats Alice Nguyen on 150000, so those two rows survive. ORDER BY e.salary DESC then lists Farah Ali before Chen Wei.
One detail is easy to miss: this is an inner JOIN, so any employee whose manager_id is NULL is dropped. NULL never equals a real emp_id, so Alice Nguyen (a top level employee with no manager) is excluded before the salary comparison ever runs. That is correct here since a person with no manager cannot out earn one, but if the question asked you to keep those staff you would switch to a LEFT JOIN and handle the NULL manager separately. More scenario style variations live at the employees earning more than their manager scenario.