SELF JOIN

Objectives

By the end of this lesson, you should be able to:

• Join a table to itself using two different aliases
• Model a hierarchy (like a manager relationship) with a self-referencing foreign key
• Explain why aliases are required for a self join, not just convenient

💡 Why this matters: “Who is this employee’s manager” is a relationship within a single staff table, an employee row relates to another row in that same table. A self join is how a query expresses that.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database.

A Self-Referencing Foreign Key

CREATE TABLE staff (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    manager_id INTEGER REFERENCES staff(id)
);

INSERT INTO staff (name, manager_id) VALUES
  ('Taylor Nakamura', NULL),
  ('Priya Desai', 1),
  ('Erin Castillo', 1),
  ('Jordan Blake', 2);

manager_id INTEGER REFERENCES staff(id) is a foreign key pointing back at its own table, this is legal, and exactly how a hierarchy is usually modeled. Taylor Nakamura has manager_id = NULL (no manager, presumably the top of the hierarchy), Priya Desai and Erin Castillo both report to Taylor (manager_id = 1), and Jordan Blake reports to Priya (manager_id = 2).

Joining staff to Itself

SELECT e.name AS employee, m.name AS manager
FROM staff e LEFT JOIN staff m ON e.manager_id = m.id
ORDER BY e.name;

    employee     |    manager
------------------+------------------
 Erin Castillo   | Taylor Nakamura
 Jordan Blake    | Priya Desai
 Priya Desai     | Taylor Nakamura
 Taylor Nakamura |

staff e and staff m are two aliases for the same table, staff, used as if they were two separate tables for the purposes of this query. e represents each row as “the employee,” m represents a row as “that employee’s manager,” and the join condition e.manager_id = m.id connects them. LEFT JOIN (rather than INNER JOIN) matters here, Taylor Nakamura has no manager, an INNER JOIN would have dropped her entirely, the same reasoning as any other unmatched row from Module 10’s LEFT JOIN lesson.

Why Aliases Are Required

SELECT name, name FROM staff JOIN staff ON manager_id = id;

ERROR:  table name "staff" specified more than once

Without aliases, PostgreSQL has no way to tell which staff reference in manager_id = id means “the employee’s copy” and which means “the manager’s copy,” both would refer to the exact same table with no way to distinguish them. Aliases aren’t just a readability convenience for a self join, they’re required, this is the one case where skipping them isn’t optional.

Try It

1. Write a self join on staff showing every employee and their manager’s name, including employees with no manager.
2. Add a WHERE clause that shows only employees who report to Taylor Nakamura specifically.
3. Write a query counting how many direct reports each manager has (hint: GROUP BY on the manager side of the join, from Module 8).
4. Explain, in your own words, why manager_id needs to allow NULL, given that Taylor Nakamura has no manager.

Recap

• A self join joins a table to itself, using two different aliases to distinguish each “copy.”
• It’s the standard way to query a hierarchy modeled with a self-referencing foreign key (like manager_id REFERENCES staff(id)).
• Aliases are required for a self join, not optional, PostgreSQL can’t otherwise tell which reference to the table means which role.

Next lesson: joining more than two tables together in a single query.