SQL Self Joins: Managers, Years, and Duplicate Pairs

Quick summary

Summarize this blog with AI

A self join gives one table two roles

A SQL self join is an ordinary join that references the same table twice. Use it when a row must be compared with another row from that table: employee and manager, current and prior year, or the two members of a candidate duplicate pair. The join condition must describe that relationship. A greater row ID does not mean the next date or the next year.

The examples below use PostgreSQL 16 and fictional rows supplied by VALUES. They are complete read-only queries with expected results.

Three questions determine the join

What does each alias represent?
Which columns express the relationship?
Should unmatched rows or mirrored pairs appear?

Understand the row roles

Employee and manager are different roles

An employee's manager_id points to another employee's employee_id. Alias the table as e for the employee and m for the manager. Joining on matching employee IDs would pair a person with themselves and answer a different question.

Keep the relationship in the data

Self joins also compare rows sharing a product, account, or normalized address. Separate the entity key from the ordering key: product identifies the series, while year or event time identifies the comparison period.

Use explicit JOIN syntax

FROM employees AS e LEFT JOIN employees AS m ON e.manager_id = m.employee_id states both roles and their relationship. A comma join spreads that relationship into the WHERE clause, where it is easier to omit accidentally. PostgreSQL's table-expression documentation explains join types and aliases.

Match employees to managers

Preserve employees without managers

WITH employees(employee_id, manager_id, full_name) AS (
  VALUES (101, NULL::integer, 'Ari'), (102, 101, 'Bea'),
         (103, 101, 'Chen'), (104, 102, 'Dia')
)
SELECT e.employee_id, e.full_name AS employee,
       m.full_name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON m.employee_id = e.manager_id
ORDER BY e.employee_id;

employee_id | employee | manager
101         | Ari      | NULL
102         | Bea      | Ari
103         | Chen     | Ari
104         | Dia      | Bea

A LEFT JOIN keeps Ari, whose manager ID is null. An INNER JOIN would return only the other three rows. A null manager name can also mean an invalid reference in unconstrained data; it does not prove the employee is a legitimate root. Use a primary key and an appropriate foreign key in the real schema.

Join on the actual previous calendar year

Suppose there is one row per product and calendar year. Product B has a missing year. The condition must compare years within each product, regardless of insertion order or row IDs.

WITH sales(product, sales_year, revenue) AS (
  VALUES ('A', 2023, 100), ('A', 2024, 120), ('A', 2025, 150),
         ('B', 2023, 80), ('B', 2025, 110)
)
SELECT cur.product, cur.sales_year, cur.revenue,
       prev.revenue AS prior_year_revenue,
       cur.revenue - prev.revenue AS revenue_change
FROM sales AS cur
LEFT JOIN sales AS prev
  ON prev.product = cur.product
 AND prev.sales_year = cur.sales_year - 1
ORDER BY cur.product, cur.sales_year;

product | sales_year | revenue | prior_year_revenue | revenue_change
A       | 2023       | 100     | NULL               | NULL
A       | 2024       | 120     | 100                | 20
A       | 2025       | 150     | 120                | 30
B       | 2023       | 80      | NULL               | NULL
B       | 2025       | 110     | NULL               | NULL

B's 2025 comparison is missing because there is no B row for 2024. Replacing it with zero would invent a previous-year value. A predicate such as cur.id > prev.id pairs a row with every smaller ID, while cur.id = prev.id + 1 assumes an ordering that IDs need not encode.

Aggregate to a unique comparison grain first

If the source has daily transactions, group by product and year before self-joining. Two current-year rows joined to three prior-year rows produce six pairs. Summing those pairs can multiply both totals. A uniqueness constraint on a stored annual table, or a grouped CTE in the query, makes the intended one-to-one year match explicit.

Choose the right comparison

Previous observed row differs from previous year

WITH sales(product, sales_year, revenue) AS (
  VALUES ('B', 2023, 80), ('B', 2025, 110)
)
SELECT product, sales_year, revenue,
       LAG(sales_year) OVER w AS prior_observed_year,
       LAG(revenue) OVER w AS prior_observed_revenue
FROM sales
WINDOW w AS (PARTITION BY product ORDER BY sales_year)
ORDER BY product, sales_year;

product | sales_year | revenue | prior_observed_year | prior_observed_revenue
B       | 2023       | 80      | NULL                | NULL
B       | 2025       | 110     | 2023                | 80

LAG returns the previous available row in this ordering. If the requirement is exactly the previous year, validate the returned year or use the year-based join. Use a unique ordering when multiple observations can share the same date. The window-functions guide covers this alternative.

Return each candidate duplicate pair once

WITH contacts(contact_id, email) AS (
  VALUES (1, '[email protected]'), (2, '[email protected]'),
         (3, '[email protected]'), (4, '[email protected]'),
         (5, NULL::text), (6, NULL::text)
)
SELECT a.contact_id AS first_id, b.contact_id AS second_id
FROM contacts AS a
JOIN contacts AS b
  ON a.email = b.email AND a.contact_id < b.contact_id
ORDER BY first_id, second_id;

first_id | second_id
1        | 2
1        | 3
2        | 3

The strict ID comparison removes self-pairs and mirrored pairs. Using != would return both (1,2) and (2,1). Three matching contacts still produce three distinct pairs; finding pairs is different from returning one representative per group. Null email values do not match under ordinary equality. Define case, whitespace, and null rules before treating a match as evidence that two records represent the same person.

Control row counts and work

Inspect keys and the plan

The employee lookup needs a unique employee key. Annual comparisons benefit from a unique product-year key. Appropriate indexes may help, but an index does not prevent a logically broad join from generating many pairs. Inspect an execution plan and test realistic group sizes before adding indexes by habit.

Predict the output cardinality

For a group of n rows, an all-pairs self join can produce n × n rows. Unordered distinct pairs produce n × (n - 1) / 2. Neither is a cheap substitute for a grouped count when the question only asks which values repeat. A three-row fixture is often enough to expose a missing or overly broad condition.

A review checklist

Explain the aliases aloud

Each alias has a clear role.
The join includes the entity key as well as the comparison condition.
The expected uniqueness is true in the data.
Null and missing-period behavior is deliberate.
The output contains the expected number of matches for a tiny fixture.

Use recursion only for variable-depth traversal

A single employee-manager join finds one level. Several fixed joins can find a known number of levels. For an arbitrary-depth hierarchy, use a recursive CTE with cycle handling, as shown in the recursive CTE tutorial.

Make the join express the question

Use relationship keys for hierarchy, actual periods for calendar comparisons, and ordered unique IDs for distinct pairs. The same table can play many roles; correctness comes from specifying which role each alias plays and testing the resulting matches.

FAQ

Is SELF JOIN a separate SQL keyword?

No. It describes a query that joins a table to itself using aliases and an ordinary join operator.

Can I find last year's row by subtracting one from its ID?

Only if the schema explicitly guarantees that unusual relationship. Normally compare the year within the entity instead. IDs can have gaps and may reflect insertion order rather than business time.

When should I use LAG?

Use it for a preceding row within a defined ordering. For a previous calendar period, also account for missing periods and duplicate period rows.