SELF JOIN

Intermediate

Joins

A self-join joins a table to itself using different aliases, used for hierarchical data (employee-manager), sequential comparisons, and finding related rows within the same table.

Overview

A self-join is not a special join type — it is any join where both sides reference the same table, distinguished by aliases. The classic use case is hierarchical data: an employees table where manager_id is a foreign key back to employees.id. Self-joins can chain to traverse fixed-depth hierarchies. For variable-depth hierarchies (org charts, bill of materials, comment threads) use a recursive CTE instead, as self-joins require knowing the depth in advance. Self-joins also appear in finding rows that share an attribute (e.g. pairs of products in the same category) or comparing a row to adjacent rows (though window functions are usually cleaner for that).

Employee-Manager Self-Join

The canonical self-join pattern: join employees to itself using manager_id = id, assigning one alias for the employee and another for the manager.

SQL — self-join for employee-manager hierarchy

-- Employee with their manager's name
SELECT
    e.id          AS employee_id,
    e.full_name   AS employee_name,
    m.full_name   AS manager_name,
    d.name        AS department
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id   -- LEFT JOIN: top-level employees have NULL manager
JOIN departments d ON d.id = e.department_id;

-- Find employees who earn more than their manager
SELECT
    e.full_name    AS employee,
    e.salary       AS employee_salary,
    m.full_name    AS manager,
    m.salary       AS manager_salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary;

-- Two-level deep hierarchy (rigid depth — use recursive CTE for variable depth)
SELECT
    grandchild.full_name   AS employee,
    parent.full_name       AS manager,
    grandparent.full_name  AS director
FROM employees grandchild
JOIN employees parent      ON parent.id      = grandchild.manager_id
JOIN employees grandparent ON grandparent.id = parent.manager_id;

Finding Related Pairs Within a Table

Self-joins also find pairs of rows satisfying a relationship — e.g. products in the same category, or orders placed by the same user on the same day.

SQL — self-join for pair finding and duplicate detection

-- Pairs of products in the same category (avoid duplicates with id comparison)
SELECT
    p1.id    AS product1_id,
    p1.name  AS product1,
    p2.id    AS product2_id,
    p2.name  AS product2,
    p1.category
FROM products p1
JOIN products p2 ON p2.category = p1.category
               AND p2.id > p1.id   -- p2.id > p1.id avoids (A,B) and (B,A) duplicates
WHERE p1.is_active = TRUE;

-- Orders placed by the same user within 5 minutes of each other (duplicate detection)
SELECT
    a.id        AS order1,
    b.id        AS order2,
    a.user_id,
    ABS(EXTRACT(EPOCH FROM (a.created_at - b.created_at))) AS seconds_apart
FROM orders a
JOIN orders b ON b.user_id = a.user_id
             AND b.id > a.id
             AND ABS(EXTRACT(EPOCH FROM (a.created_at - b.created_at))) < 300;

Key Points to Remember

1A self-join joins a table to itself using two different aliases.
2Use LEFT self-join for hierarchy queries so root nodes (no manager) are included.
3Use id comparison (p2.id > p1.id) to avoid generating duplicate pairs (A,B) and (B,A).
4Fixed-depth hierarchies use self-joins; variable-depth hierarchies need recursive CTEs.
5Self-joins can be expensive on large tables — ensure the join column is indexed.
6Window functions (LAG/LEAD) are often cleaner than self-joins for comparing adjacent rows.

Interview Questions

Write a query to find all employees who earn more than their direct manager.

MediumAmazon

What is the difference between a self-join and a recursive CTE for hierarchical data?

HardGoogle

How do you prevent duplicate pairs when doing a self-join?

MediumMicrosoft

How would you display each employee alongside their manager's name?

EasyInfosys

Ask Aria about SELF JOIN

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

FULL OUTER JOIN & UNION Emulation

CROSS JOIN