Employees paid more than their manager: the SQL self-join

One table, two names, one join. The employees-and-managers question is a SQL interview classic, and most people fumble it on the direction of the ON clause, not on the idea. This page covers the self-join, the ON direction, and the LEFT JOIN case. The print-ready A4 PDF is at the bottom.

The question

• The table employees has id, name, salary and manager_id.
• Find who earns more than their manager.

The answer

• FROM employees e: the employee side.
• JOIN employees m: the same table, as the manager.
• ON e.manager_id = m.id: the join is the whole answer.
• WHERE e.salary > m.salary: then compare.

The query

SELECT e.name AS employee,
       e.salary,
       m.name AS manager,
       m.salary AS manager_salary
FROM employees e
JOIN employees m
  ON e.manager_id = m.id
WHERE e.salary > m.salary;

Same table twice, under two names. The ON clause says which side is the employee and which is the manager.

Read the ON clause out loud: the employee row (e) carries a manager_id, and it must match the id of the manager row (m). Reverse it and you list managers next to their reports, which is a different question.

Remember

• Aliases: two names, one table.
• LEFT JOIN if people with no manager must stay in the result.

For this question you do not need the LEFT JOIN. A person with no manager cannot earn more than their manager, so the inner join is right. Use the LEFT JOIN when the question is about everyone, for example a list of all employees with their manager's name, where the top person has NULL.

The trap: the ON clause, backwards

Both joins run. Only one answers the question.

The wrong version gives no error and returns rows, so you can miss it. Check it with one known pair: pick one employee, find their manager by hand, and see which side the query puts them on.

The quiz

Who earns more than the boss?

FROM employees e
JOIN employees m

A)  ON e.id = m.manager_id

B)  ON e.manager_id = m.id

WHERE e.salary > m.salary

B. The employee row carries the manager_id, so it has to match the manager's id. In A, e is the manager and m is one of their reports: it lists managers who earn more than their reports, the opposite question.

Interview phrasing worth memorizing: I alias the table twice, join the employee's manager_id to the manager's id, then filter on salary.