JuniorCodeOccasionalNot answered yet
Query employees who earn more than their managers
List each employee whose salary exceeds their manager's salary.
-- Employee(id, name, salary, dept_id, manager_id)
-- manager_id references Employee(id)
-- write your query hereWrite the query.
Self-join the table on the manager link and compare salaries: SELECT e.name FROM Employee e JOIN Employee m ON e.manager_id = m.id WHERE e.salary > m.salary. The table is joined to itself, aliasing one copy as the employee and one as the manager via manager_id → id.
- ✗Comparing to the company average rather than the specific manager
- ✗Joining on department instead of the
manager_id → idlink - ✗Forgetting to alias the two copies of the table distinctly
- →How do you also show employees who have no manager (a NULL
manager_id)? - →Why must both sides of the self-join carry distinct table aliases?
Contents
Task
List the employees in Employee whose salary exceeds their manager's salary.
Solution
SELECT e.name AS employee, m.name AS manager
FROM Employee e
JOIN Employee m ON e.manager_id = m.id
WHERE e.salary > m.salary;Key points
- The canonical self-join: the table is joined to itself via
manager_id → id. - Aliases
eandmdistinguish the employee row from the manager row. - The inner
JOINdrops employees without a manager; useLEFT JOINto include them.
Contents
