Find employees who earn more than their manager
Table Employee(id, name, salary, manager_id) where manager_id references another row's id. Return employees who earn strictly more than their direct manager.
Requirements:
- the manager of an employee is the row whose
idequals the employee'smanager_id - compare each employee's
salaryagainst that manager'ssalary, keeping the strictly-greater ones - return the employee (and ideally the manager) for context
SELECT e.name AS employee, m.name AS manager
FROM Employee e
-- your query hereWrite the query.
Join Employee to itself: alias one copy e (employee) and another m (manager) on e.manager_id = m.id, then keep rows where e.salary > m.salary. The same table appears twice under different aliases, walking the manager_id → id relationship within one table.
- ✗Joining on
e.id = m.idinstead ofe.manager_id = m.id - ✗Trying to express the comparison with
GROUP BYinstead of a self-join - ✗Forgetting to alias the two copies of the same table distinctly
- →Why does the join condition use
manager_id → idrather thanid → id? - →How would a
LEFT JOINchange the result for employees with no manager?
Task
Find employees who earn more than their direct manager.
SELECT e.name AS employee, e.salary AS emp_salary,
m.name AS manager, m.salary AS mgr_salary
FROM Employee e
JOIN Employee m ON e.manager_id = m.id
WHERE e.salary > m.salary;How it works
A self-join is a join of a table to itself. Here Employee participates twice under different aliases:
e— the employee's row;m— their manager's row.
The link is the foreign key: e.manager_id = m.id pairs each employee with their manager. After the join, you just compare salaries within one row: e.salary > m.salary.
⚠️ The canonical self-join question. A common mistake is joining on e.id = m.id (a row to itself) instead of the manager_id → id relationship.
