Self Joins: A Table Meets Itself

When the foreign key points home

This shape is everywhere once you look: a comment stores the id of its parent comment, a category stores its parent category, a user stores who referred them. Whenever a row relates to another row of the same kind, the foreign key points at its own table. The classic example is an employees table that stores who manages whom with a manager_id column pointing at another row in the same table:

Ana is the boss (no manager). To print each worker next to their manager's name, you join the table to itself. That requires giving the table two temporary names, called aliases, with AS:

SELECT worker.name, boss.name
FROM employees AS worker
JOIN employees AS boss ON boss.id = worker.manager_id
ORDER BY worker.id;

Mentally there are now two copies of employees: one playing the worker role, one playing the boss role. The ON rule connects a worker's manager_id to a boss's id.

Pairing each worker with their manager

Three rows come back, one per employee who has a manager. Ana is absent because her manager_id is NULL and an inner join drops the unmatched.

CREATE TABLE employees (
  id INTEGER,
  name TEXT,
  manager_id INTEGER
);

INSERT INTO employees VALUES (1, 'Ana', NULL);
INSERT INTO employees VALUES (2, 'Ben', 1);
INSERT INTO employees VALUES (3, 'Cara', 1);
INSERT INTO employees VALUES (4, 'Dan', 2);

SELECT worker.name, boss.name
FROM employees AS worker
JOIN employees AS boss ON boss.id = worker.manager_id
ORDER BY worker.id;

Output

Ben|Ana
Cara|Ana
Dan|Ben

One table is named twice in the same query, under two different aliases, and the database treats worker and boss as two independent copies to be matched against each other. Nothing about a join requires the two sides to be different tables.

Ana appears in the output as a boss on two lines even though she has no line of her own as a worker. Her row is present in the boss copy and merely unmatched in the worker copy, which the next example fixes.

Why the aliases are mandatory here

Without two distinct names, employees.name would be ambiguous, since SQL would have no way to tell which copy of the table is meant.

The query uses one table in two roles at once. Aliases give each role a name, so worker.name and boss.name unambiguously refer to different copies, and the ON clause can compare boss.id against worker.manager_id.

FROM employees AS worker
JOIN employees AS boss ON boss.id = worker.manager_id

In an ordinary two-table join aliases are optional, though still useful for shortening long names, as in customers AS c. In a self join they are the only thing that makes the query expressible at all.

The AS keyword itself is optional in most databases, so FROM employees worker means the same thing. Writing AS is clearer for a reader and is the style used throughout this course.

Keeping the employee who has no manager

One word changes and Ana reappears, with an empty manager column.

CREATE TABLE employees (
  id INTEGER,
  name TEXT,
  manager_id INTEGER
);

INSERT INTO employees VALUES (1, 'Ana', NULL);
INSERT INTO employees VALUES (2, 'Ben', 1);
INSERT INTO employees VALUES (3, 'Cara', 1);
INSERT INTO employees VALUES (4, 'Dan', 2);

SELECT worker.name, boss.name
FROM employees AS worker
LEFT JOIN employees AS boss ON boss.id = worker.manager_id
ORDER BY worker.id;

Output

Ana|
Ben|Ana
Cara|Ana
Dan|Ben

LEFT JOIN keeps every row of the left table, which is worker here, and pads the boss columns with NULL where no match exists. Ana is the company owner, so the blank is the correct answer rather than missing data.

This combination of a self join with a left join is how organization charts, category trees, and comment threads get queried. The root of the tree is exactly the row whose parent pointer is NULL.

Counting direct reports per manager

The self join from the first example, grouped by the boss side.

CREATE TABLE employees (
  id INTEGER,
  name TEXT,
  manager_id INTEGER
);

INSERT INTO employees VALUES (1, 'Ana', NULL);
INSERT INTO employees VALUES (2, 'Ben', 1);
INSERT INTO employees VALUES (3, 'Cara', 1);
INSERT INTO employees VALUES (4, 'Dan', 2);

SELECT boss.name, COUNT(*) AS reports
FROM employees AS worker
JOIN employees AS boss ON boss.id = worker.manager_id
GROUP BY boss.name
ORDER BY boss.name;

Output

Ana|2
Ben|1

Each joined row is one worker-and-boss pair, so counting the rows in each boss.name bucket counts direct reports. Ana manages Ben and Cara for a total of 2, and Ben manages Dan for a total of 1.

Cara and Dan manage nobody, and the inner join leaves them out of the result rather than reporting a zero. Switching to a left join with worker on the left would not fix that either. Listing every employee with a count that can be zero means putting the boss copy on the left instead, and counting worker.id rather than rows.