Employees who earn more than their manager

Find every employee who earns more than their own manager. Show the employee's name and salary, and the manager's name and salary.

What it tests

Whether you can treat one table as two — a self join — and read a self-referencing key the right way round.

The pattern

Self join. "Their manager", "their parent", "the person who referred them" — a column pointing at another row of the same table.

How to approach it

  1. Notice that manager_id points to another row of employees.
  2. Use the table twice: e for the employee, m for the manager.
  3. Join with m.emp_id = e.manager_id (m is e's manager).
  4. Keep rows where e.salary > m.salary. An inner join is fine here: people with no manager can't earn more than one.

Edge cases to mention

Common mistakes

Not true: e.emp_id = m.manager_id joins each employee e to their manager m.
In fact: That condition makes e the manager of m. To join each employee e to their manager m, write m.emp_id = e.manager_id.

Part of Unit 8: SQL interview problems, from easy to hard in the free SQL course.