SELF-JOIN

Introduction to PostgreSQL self-join

A self-join is a regular join that joins a table to itself. You specify the same table twice with different aliases and provide the join predicate after ON. Self-joins are most commonly used to query hierarchical data or to find rows within the same table that share a common value.

Using INNER JOIN:

SELECT select_list
FROM table_name t1
INNER JOIN table_name t2
  ON join_predicate;

Using LEFT JOIN (to include rows with no matching partner):

SELECT select_list
FROM table_name t1
LEFT JOIN table_name t2
  ON join_predicate;

Querying hierarchical data with a self-join

Consider an employee table where each row has a manager_id that references another row's employee_id:

CREATE TABLE employee (
  employee_id INT PRIMARY KEY,
  first_name  VARCHAR(255) NOT NULL,
  last_name   VARCHAR(255) NOT NULL,
  manager_id  INT,
  FOREIGN KEY (manager_id) REFERENCES employee(employee_id) ON DELETE CASCADE
);

INSERT INTO employee (employee_id, first_name, last_name, manager_id)
VALUES
  (1, 'Windy',  'Hays',        NULL),
  (2, 'Ava',    'Christensen',  1),
  (3, 'Hassan', 'Conner',       1),
  (4, 'Anna',   'Reeves',       2),
  (5, 'Sau',    'Norman',       2),
  (6, 'Kelsie', 'Hays',         3),
  (7, 'Tory',   'Goff',         3),
  (8, 'Salley', 'Lester',       3);

Use an INNER JOIN self-join to find who reports to whom:

SELECT
  e.first_name || ' ' || e.last_name AS employee,
  m.first_name || ' ' || m.last_name AS manager
FROM employee e
INNER JOIN employee m ON m.employee_id = e.manager_id
ORDER BY manager;

Output:

     employee     |      manager
------------------+-----------------
 Anna Reeves      | Ava Christensen
 Sau Norman       | Ava Christensen
 Kelsie Hays      | Hassan Conner
 Salley Lester    | Hassan Conner
 Tory Goff        | Hassan Conner
 Ava Christensen  | Windy Hays
 Hassan Conner    | Windy Hays
(7 rows)

The top manager (Windy Hays) is absent because INNER JOIN requires a match on both sides — there is no manager row for manager_id = NULL. Switch to LEFT JOIN to include the top-level employee:

SELECT
  e.first_name || ' ' || e.last_name AS employee,
  m.first_name || ' ' || m.last_name AS manager
FROM employee e
LEFT JOIN employee m ON m.employee_id = e.manager_id
ORDER BY manager;

Output now includes Windy Hays with a null manager.

Comparing rows within the same table

A self-join can also find pairs of rows that share a common attribute. The following query finds all pairs of films with the same running length:

SELECT
  f1.title,
  f2.title,
  f1.length
FROM film f1
INNER JOIN film f2
  ON f1.film_id > f2.film_id
  AND f1.length = f2.length;

Sample output:

        title          |            title            | length
-----------------------+-----------------------------+--------
 Chamber Italian       | Affair Prejudice            |    117
 Grosse Wonderful      | Doors President             |     49
 Bright Encounters     | Bedazzled Married           |     73
...

The condition f1.film_id > f2.film_id prevents matching a film to itself and ensures each pair appears only once.

Self-join tips

• Always assign meaningful aliases (e.g., e for employee, m for manager) to keep the query readable.
• Use LEFT JOIN instead of INNER JOIN when the root node of a hierarchy has a NULL foreign key and must appear in results.
• When finding unique pairs within a table, use t1.id > t2.id (not !=) to avoid duplicates like (A,B) and (B,A).
• Self-joins on large tables benefit from an index on the join column (e.g., manager_id).
• For deep hierarchies (many levels), consider a recursive CTE instead of a series of self-joins.