SQL ยท Lesson 94 of 126
Employees and Managers
The self join question.
Here's another favourite. Find employees who earn more than their manager. It tests whether you know self joins.

- Earn more than their manager?
- Tests self joins
- A favourite question
Earning more than the boss
Join staff to itself. E is the employee, M is their manager. Then compare the two salaries. Run it. No rows at all. At Helen's shop, nobody earns more than their manager. An empty result is still an answer. Say so confidently in an interview.
query.sqlsql
SELECT e.name AS employee, e.salary,
m.name AS manager, m.salary AS manager_salary
FROM staff AS e
JOIN staff AS m ON m.staff_id = e.manager_id
WHERE e.salary > m.salary;A follow-up
A good follow-up is, who's closest to their manager's salary? Subtract, and sort. Run it. Priya Nair earns sixteen thousand less than Helen. That's the smallest gap.
query.sqlsql
SELECT e.name AS employee,
m.salary - e.salary AS gap_to_manager
FROM staff AS e
JOIN staff AS m ON m.staff_id = e.manager_id
ORDER BY gap_to_manager
LIMIT 3;Output
โโโโโโโโโโโโโโฌโโโโโโโโโโโโโโโโโ โ employee โ gap_to_manager โ โโโโโโโโโโโโโโผโโโโโโโโโโโโโโโโโค โ Priya Nair โ 16000 โ โ Marcus Lee โ 18000 โ โ Sara Odum โ 22000 โ โโโโโโโโโโโโโโดโโโโโโโโโโโโโโโโโ
Summary
Self join, then compare columns from the two copies. And remember, an empty result can be the right answer.
Key takeaways- Self join
- Compare e and m
- Empty can be correct
Next up: Finding Duplicates โ GROUP BY with HAVING