SQL Academy · Lesson

What Is a Self Join

Join a table to itself with aliases.

Joining a Table to Itself

A self join is when you join a table to itself. This sounds unusual at first, but it is a powerful technique used when rows in a single table are related to other rows in the same table.

The most common example is an employees table where each employee row also stores the ID of their manager — who is also an employee in the same table.

The Employees Table

Consider an employees table where each row has an id, a name, and a manager_id that points to another row in the same table.

This structure is called a self-referencing or recursive relationship. Run the query below to create and populate the table.

CREATE TABLE employees (
  id         INT PRIMARY KEY,
  name       VARCHAR(50),
  manager_id INT
);

INSERT INTO employees VALUES
  (1, 'Alice',   NULL),
  (2, 'Bob',     1),
  (3, 'Carol',   1),
  (4, 'Dave',    2),
  (5, 'Eve',     2),
  (6, 'Frank',   3);

All lessons in this course

← Back to SQL Academy