Self-Join: Comparing One Row Against Another in the Same Table

“Which employees earn more than their manager?” One table, one question, and no second table to join to.

The problem

The employees table has a manager_id column pointing back at its own id. Who out-earns their manager?

There is no second table. So treat the same table as two tables by giving it two aliases:

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

Common mistakes

1. Forgetting aliases

-- Error: which id belongs to which copy?
FROM employees JOIN employees ON id = manager_id

A self-join requires an alias on both sides.

2. INNER JOIN where LEFT JOIN was needed

Above, the person with no manager (manager_id IS NULL) drops out entirely. If the report must list everyone, switch:

FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id

3. Counting each pair twice

For “find every pair of employees in the same department”, writing ON a.dept = b.dept AND a.id <> b.id returns both (Ann, Ben) and (Ben, Ann). Use a.id < b.id so each pair appears once.

The other common shape: comparing to the previous day

SELECT t.id
FROM weather t
JOIN weather y ON y.date = t.date - INTERVAL '1 day'
WHERE t.temperature > y.temperature;

This is LeetCode 197. Where window functions exist, LAG is faster and easier to read — but the self-join is still the only option on MySQL 5.7.

Practice

• LeetCode 181 — Employees Earning More Than Their Managers
• LeetCode 197 — Rising Temperature
• LeetCode 1747 — Leetflex Banned Accounts
• LeetCode 1607 — Sellers With No Sales
• HackerRank — Placements