SQL Topic
Self Join
A self join joins a table to itself, using aliases to treat the same table as two roles. It is the classic tool for comparing rows within one table: employees to their managers, weather to the previous day, machines to their own process steps.
When is Self Join used?
Row-to-row comparisons inside a single table — manager/employee, previous/next record, start/end events for the same process.
Core syntax
SELECT ... FROM employee e JOIN employee m ON m.id = e.manager_id — alias each copy of the table.Common mistakes
- Forgetting table aliases.
- Not handling NULL keys, which never match a join.
- Producing duplicate rows when the key relationship is not one-to-one.
Practice Self Join questions
Work through them in order — each question builds on the last.