Read the ON clause out loud: "the manager row is the one whose id equals the employee's manager_id". Getting that direction backwards (e.id = m.manager_id) is the classic mistake: it swaps the roles and lists each manager under their report's name.

Meera is missing: her manager_id is NULL, which matches nothing. If the report must list everyone, use a left join and label the top:

SELECT e.name AS employee, COALESCE(m.name, '(none)') AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.name;

Comparing a row with its partner

Once the two rows sit side by side, any comparison is a WHERE clause. The interview classic — employees who earn more than their manager:

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
ORDER BY e.name;

The same shape answers "employees hired before their manager", "products cheaper than the product they replaced" or "tasks due before the task they depend on".

Counting through a self join works too — the number of direct reports per manager:

SELECT m.name AS manager, COUNT(e.id) AS reports
FROM employees m
LEFT JOIN employees e ON e.manager_id = m.id
GROUP BY m.id, m.name
ORDER BY reports DESC, m.name;

Here m is on the left because every employee is a potential manager, and the left join gives zero to the people who manage nobody.

Going up more than one level

Each extra level of the hierarchy is one more alias:

SELECT e.name AS employee, m.name AS manager, g.name AS managers_manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
LEFT JOIN employees g ON g.id = m.manager_id
ORDER BY e.name;

That works for a fixed depth. A hierarchy of unknown depth — "everyone under Meera, however far down" — needs a recursive CTE, which comes in a later module.

Pairs of rows

Customers in the same city:

SELECT a.name AS customer_a, b.name AS customer_b, a.city
FROM customers a
JOIN customers b ON b.city = a.city AND a.id < b.id
ORDER BY a.city, customer_a, customer_b;

Why a.id < b.id? Without it, every customer matches themselves (same city, after all), and every real pair appears twice, as (Asha, Chen) and (Chen, Asha). a.id <> b.id removes the self-pairs but keeps both orderings. The strict < keeps exactly one ordering of each pair. A city with n customers gives n(n − 1)/2 pairs. Customers whose city is NULL never pair, because NULL = NULL is not true.

The same trick finds duplicate records — two rows with the same email but different ids:

SELECT a.id AS keep_id, b.id AS duplicate_id, a.email
FROM users a
JOIN users b ON b.email = a.email AND a.id < b.id
ORDER BY a.email, b.id;

Comparing with the previous day

Daily readings, one row per date — on which days was it warmer than the day before?

SELECT w.day, w.temp, p.temp AS previous_temp
FROM weather w
JOIN weather p ON w.day = DATE_ADD(p.day, INTERVAL 1 DAY)
WHERE w.temp > p.temp
ORDER BY w.day;

The partner of each row is the row dated exactly one day earlier. If a date is missing from the table, the day after the gap has no partner and drops out — "the day before" is a calendar fact, not "the previous row". Comparing by subtracting dates (w.day - 1) does not work in MySQL: arithmetic on a DATE treats it as the number 20240102, so subtracting 1 from 20240101 gives 20240100, not December 31st. Use DATE_ADD/DATE_SUB or DATEDIFF(w.day, p.day) = 1. In PostgreSQL, p.day + 1 on a date is correct, and in SQL Server the function is DATEADD(day, 1, p.day).

Window functions (LAG) compare with the previous row instead and are often simpler; they come in a later module. Self joins remain the answer when the partner is defined by a condition rather than by position, and interviewers still ask for them.

Performance note

A self join is executed like any join: the database looks up partners using indexes on the join columns. An index on manager_id, or on (city), keeps a large self join fast. A pair query with only a.id < b.id and no equality condition is close to a cross join — n²/2 rows — so make sure an equality narrows it down.

Pitfalls

• Reversing the ON direction and swapping the roles of the two aliases.
• Forgetting the top of the hierarchy, whose parent key is NULL.
• Pair queries without a.id < b.id: self-pairs and every pair twice.
• Date arithmetic with - 1 in MySQL instead of DATE_ADD or DATEDIFF.
• Aliases like t1/t2 that hide which row plays which role.

Key takeaways

• A self join names one table twice with two aliases and joins them like two tables.
• Name the aliases by role (e, m) and read ON aloud to check the direction.
• Use LEFT JOIN when rows with no partner (the top manager) must stay.
• a.id < b.id produces each pair once and never a row with itself.
• For "the day before", join on DATE_ADD(p.day, INTERVAL 1 DAY); gaps in dates are respected.