Self-joins
SQL · 5 interview questions
SQL is good at comparing columns within a row and bad at comparing a row to its neighbours. A self-join is the classic workaround: alias the same table twice and join it to itself, so a single query can see two rows at once.
Employees and their managers live in one table; joining it to itself on e.manager_id = m.id gives you both in one row. Comparing today's temperature to yesterday's means joining the table to itself on a date offset.
The trap in that second example is joining on id = id - 1 instead of on the date. Ids are not guaranteed to be consecutive or in date order, and the moment one is missing the query starts comparing the wrong pair of days — while still returning results that look plausible. Join on the thing you actually mean.
Since MySQL 8.0, LAG and LEAD do the neighbour comparison directly and are usually clearer. Self-joins remain the right tool for relationships that aren't sequential — pairs, hierarchies, and anything where a row relates to another row by meaning rather than by position.
Self-joins interview questions
- What is a self-join and when do you need one?
- Joining a table to itself under two aliases, so one row can be compared against another row of the same table. Needed for hierarchies (employee/manager), sequential comparisons (today vs yesterday), and pair-finding within one table.
- Why they ask: The definition is easy; the 'when' is what's being tested.
- For 'find days warmer than the previous day', why is joining on
id = id - 1wrong? - Ids aren't guaranteed to be consecutive or ordered by date. One missing row and you're comparing non-adjacent days while still returning plausible-looking results. Join on the date arithmetic instead —
DATEDIFF(a.date, b.date) = 1. - Why they ask: A wrong answer that passes small test cases, which is the worst kind.
- Why do both sides of a self-join need aliases?
- Without them every column reference is ambiguous — the parser can't tell which copy of the table you mean. The aliases are what make the two copies distinct entities.
- Why they ask: Trivial, but it's the error message people hit first.
- When should a self-join be replaced by a window function?
- Whenever the comparison is positional — previous row, next row, running total, rank within a group. LAG/LEAD express it in one line and read the table once. Keep the self-join for relationships defined by meaning rather than order, like employee-to-manager.
- Why they ask: Shows you know both tools and when each wins, which is the actual senior signal.
- How do you stop a self-join from returning each pair twice?
- Add an inequality on the key:
WHERE a.id < b.id. Without it you get both (1,2) and (2,1), plus every row paired with itself if you used<=or nothing at all. - Why they ask: The standard fix for pair-finding problems, and easy to forget under pressure.
Practise it
Free, database-tagged LeetCode problems that drill this specific skill. In the app these tick themselves off from your solve history.
You'll forget most of this by next week
That's not a discipline problem, it's how memory works. In the app these come back on an expanding schedule — right before you'd lose them.
Start your streakFull refund within 7 days. No questions.