Self Joins — Joining a Table with Itself

Self Joins

A self join joins a table to itself using two different aliases. It is useful for hierarchical data (like an employee–manager relationship) or comparing rows within the same table.

-- Suppose students has a referred_by column pointing to another student's id
-- Find each student and the name of the student who referred them
SELECT
  s.name        AS student,
  r.name        AS referred_by
FROM students s
LEFT JOIN students r ON r.id = s.referred_by;

The key insight is that you need distinct aliases (s and r) so SQL can tell which copy of the table you mean in each part of the query. Self joins are conceptually simple once you think of the two aliases as two separate tables.