What is a self join and when do you use it?

Question

Accepted Answer

A self join is a regular JOIN where a table is joined to itself using table aliases. The two &ldquo;copies&rdquo; are treated as different tables for the duration of the query. Hierarchical data — employee &rarr; manager, category &rarr; parent category. Comparing rows in the same table — finding pairs, duplicates, sequence gaps. Aliases are mandatory — same table needs different names to disambiguate. For deep hierarchies — use a recursive CTE (MySQL 8+) instead of chained self joins. Self join = same table, two aliases. The employee/manager hierarchy is THE classic example. 'Find employees earning more than their manager' is a top interview query. The a.id < b.id trick for finding duplicates without self-matches is essential.