SQL 39 🛢️ Joining a Table to Itself with SELF JOIN

A self join is a join in which a table is joined to itself. The table appears twice in the FROM clause, and the two instances are given different aliases so the query can distinguish them. The join condition relates a row in one instance to a row in the other, which is how the query compares rows within the same table. A self join is not a separate join type; it is an inner, left, right, or full outer join in which both sides are the same table.

The self join is the tool for hierarchical data, comparisons within a table, and finding pairs or sequences. An employee table that contains a manager_id referencing another row in the same table uses a self join to list each employee with their manager. A table of events uses a self join to find consecutive events or to compare a row with the previous row. A table of people uses a self join to find pairs who share a city. The self join is the mechanism for these queries, and the alias is what makes it possible.

This chapter covers the syntax of the self join, the alias requirement, the inner self join, the outer self join, hierarchical queries, comparisons between rows, self-pair queries, and the performance considerations.

Key point: A self join joins a table to itself using two aliases. The alias is required because the table appears twice, and the query must distinguish the two instances. The join condition relates a row in one instance to a row in the other, typically a foreign key to a primary key. Use an inner self join when both rows must exist, and a left self join when the first row must exist even if the second does not.

---

Why self joins exist

The hierarchical problem. Some data is recursive: an employee reports to a manager who reports to a director. A category contains a subcategory that contains a sub-subcategory. The relationship is stored in the same table, with a foreign key pointing to another row. The self join traverses the relationship.

The comparison problem. Some queries need to compare a row with another row in the same table. Which employees earn more than their manager? Which products are priced higher than the average of their category? The self join makes both rows available in the same query.

The pairing problem. Some queries need to find pairs of rows that satisfy a condition. Which pairs of employees share a city? Which pairs of events occurred within an hour of each other? The self join produces the pairs.

The sequencing problem. Some queries need to find the previous or next row. What is the difference between the current value and the previous value? The self join relates a row to the row that precedes it.

The alias problem. A table cannot appear twice in the FROM clause without aliases. The database needs a way to distinguish which instance a column reference belongs to. The alias is the mechanism, and it is what makes the self join possible.

---

a. Basic self join syntax

The table is listed twice, and each instance is given a different alias.

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;

The aliases e and m distinguish the two instances. e is the employee row, and m is the manager row. The join condition matches the employee’s manager_id to the manager’s employee_id.

Without aliases, the query is invalid because the column references are ambiguous.

-- Invalid: ambiguous column reference
SELECT name FROM employees JOIN employees ON manager_id = employee_id;

The alias requirement is not a convention; it is a rule. When the same table appears twice, every column reference must be qualified with the alias of the instance it belongs to.

---

b. Inner self join

An inner self join returns rows where the join condition matches in both instances. In the employee-manager example, this returns employees who have a manager.

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;

The result excludes employees whose manager_id is NULL, because there is no matching manager row. The top-level employee — the one with no manager — does not appear.

employee	manager
Bob	Alice
David	Carol
Eve	Carol

Alice, the top-level employee, is not in the result because she has no manager.

---

c. Left self join

A left self join returns all rows from the first instance and the matching rows from the second. In the employee-manager example, this includes employees who have no manager.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

employee	manager
Alice	NULL
Bob	Alice
Carol	NULL
David	Carol
Eve	Carol

Alice and Carol have NULL for the manager because they are the top-level employees. The left join preserves them even though there is no matching manager row.

The left self join is the correct choice for hierarchical data, because it includes the rows at the top of the hierarchy where the parent reference is NULL.

---

d. Hierarchical queries

A self join traverses one level of a hierarchy. To traverse multiple levels, the query joins the table multiple times or uses a recursive CTE.

One level. Each employee with their manager:

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Two levels. Each employee with their manager and their manager’s manager:

SELECT e.name AS employee, m.name AS manager, g.name AS grand_manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
LEFT JOIN employees g ON m.manager_id = g.employee_id;

The third alias g represents the grand-manager. Each additional level adds another self join.

Unlimited levels. A recursive CTE traverses the hierarchy to any depth:

WITH RECURSIVE org_chart AS (
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart ORDER BY level, name;

The recursive CTE starts with the top-level rows and joins the table to itself repeatedly until no more rows match. This is the standard way to traverse a hierarchy of unknown depth.

---

e. Comparing rows within a table

A self join makes two rows of the same table available in a single query, which allows comparisons between them.

Employees who earn more than their manager:

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

The join condition relates the employee to the manager, and the WHERE clause compares their salaries. The result shows employees who earn more than their manager.

Comparing consecutive rows:

SELECT
    curr.event_date,
    curr.value,
    prev.value AS previous_value,
    curr.value - prev.value AS difference
FROM events curr
LEFT JOIN events prev ON prev.event_date = curr.event_date - INTERVAL '1 day';

The join relates each row to the row from the previous day. The difference column shows the change between the two rows. This pattern is used for time-series analysis, change detection, and running totals.

---

f. Self-pair queries

A self join can find pairs of rows that satisfy a condition. The classic example is finding pairs of people who share a city.

SELECT a.name AS person1, b.name AS person2, a.city
FROM people a
JOIN people b ON a.city = b.city AND a.id < b.id;

The condition a.id < b.id excludes three cases:

• A person paired with themselves (a.id = b.id)
• The reverse pair (a.id > b.id)

Each unordered pair appears exactly once. Without the a.id < b.id condition, the result would include each pair twice and each person paired with themselves.

person1	person2	city
Alice	Bob	Oslo
Alice	Carol	Oslo
Bob	Carol	Oslo

The result lists each pair once. The condition is what makes the query correct.

---

Complete Example Session

-- ============================================
-- PART 1: CREATE THE EMPLOYEES TABLE
-- ============================================
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    name        VARCHAR(50),
    manager_id  INTEGER,
    salary      NUMERIC(10, 2),
    city        VARCHAR(50)
);

INSERT INTO employees VALUES
(1, 'Alice', NULL, 95000, 'Oslo'),
(2, 'Bob',   1,    78000, 'Oslo'),
(3, 'Carol', NULL, 88000, 'Bergen'),
(4, 'David', 3,    65000, 'Bergen'),
(5, 'Eve',   3,    72000, 'Oslo');

-- ============================================
-- PART 2: INNER SELF JOIN — EMPLOYEE AND MANAGER
-- ============================================
SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;
-- Bob → Alice, David → Carol, Eve → Carol

-- ============================================
-- PART 3: LEFT SELF JOIN — INCLUDE TOP-LEVEL
-- ============================================
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
-- Alice → NULL, Carol → NULL, plus the matched rows

-- ============================================
-- PART 4: TWO LEVELS — EMPLOYEE, MANAGER, GRAND-MANAGER
-- ============================================
SELECT e.name AS employee, m.name AS manager, g.name AS grand_manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
LEFT JOIN employees g ON m.manager_id = g.employee_id;

-- ============================================
-- PART 5: RECURSIVE CTE FOR UNLIMITED LEVELS
-- ============================================
WITH RECURSIVE org_chart AS (
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart ORDER BY level, name;

-- ============================================
-- PART 6: COMPARE SALARIES — EMPLOYEE VS MANAGER
-- ============================================
SELECT e.name AS employee, e.salary, m.name AS manager, m.salary
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;
-- Employees earning more than their manager

-- ============================================
-- PART 7: SELF-PAIR — SHARED CITY
-- ============================================
SELECT a.name AS person1, b.name AS person2, a.city
FROM employees a
JOIN employees b ON a.city = b.city AND a.employee_id < b.employee_id;
-- Each unordered pair once

-- ============================================
-- PART 8: CONSECUTIVE ROWS
-- ============================================
SELECT
    curr.event_date,
    curr.value,
    prev.value AS previous_value,
    curr.value - prev.value AS difference
FROM events curr
LEFT JOIN events prev ON prev.event_date = curr.event_date - INTERVAL '1 day'
ORDER BY curr.event_date;

-- ============================================
-- PART 9: FIND EMPLOYEES WITH NO SUBORDINATES
-- ============================================
SELECT e.name
FROM employees e
LEFT JOIN employees sub ON sub.manager_id = e.employee_id
WHERE sub.employee_id IS NULL;
-- Employees who manage no one

-- ============================================
-- PART 10: FIND EMPLOYEES WITH THE SAME MANAGER
-- ============================================
SELECT a.name AS employee1, b.name AS employee2, m.name AS manager
FROM employees a
JOIN employees b ON a.manager_id = b.manager_id AND a.employee_id < b.employee_id
JOIN employees m ON a.manager_id = m.employee_id;
-- Pairs of employees who share a manager

These ten parts cover creating the table, an inner self join, a left self join, a two-level join, a recursive CTE, comparing salaries, a self-pair query, consecutive rows, finding employees with no subordinates, and finding employees with the same manager.

---

Quick Reference

Self Join Syntax

Form	Example
Inner	FROM t a JOIN t b ON a.id = b.parent_id
Left	FROM t a LEFT JOIN t b ON a.id = b.parent_id
Multiple	FROM t a JOIN t b ON ... JOIN t c ON ...

Alias Requirement

Rule	Explanation
Two aliases required	The table appears twice
Qualify every column	a.col, b.col
Alias is mandatory	Not a convention

Common Patterns

Pattern	Purpose
Employee and manager	Hierarchy traversal
Two levels	Manager and grand-manager
Recursive CTE	Unlimited depth
Salary comparison	Compare rows
Self-pair	Find pairs
Consecutive rows	Time series

Self-Pair Conditions

Condition	Effect
a.id < b.id	Each unordered pair once
a.id > b.id	Each unordered pair once (reversed)
a.id <> b.id	Each ordered pair twice
No condition	Includes self-pairs and both orders

Hierarchy Traversal

Depth	Method
One level	One self join
Two levels	Two self joins
Unlimited	Recursive CTE

---

Best Practices

✅ Do This:

-- Use meaningful aliases
SELECT e.name, m.name
FROM employees e JOIN employees m ON e.manager_id = m.employee_id;

-- Use LEFT JOIN to include top-level rows
FROM employees e LEFT JOIN employees m ON e.manager_id = m.employee_id;

-- Exclude self-pairs and duplicates
WHERE a.id < b.id;

-- Use a recursive CTE for unlimited depth
WITH RECURSIVE org_chart AS (...);

❌ Don’t Do This:

-- Forget the aliases
FROM employees JOIN employees ON ...  -- ❌ ambiguous

-- Use inner join for hierarchical data
FROM employees e JOIN employees m ON ...  -- ❌ drops top-level rows

-- Omit the pair condition
FROM people a JOIN people b ON a.city = b.city  -- ❌ duplicates and self-pairs

-- Hardcode the number of levels for deep hierarchies
-- Use a recursive CTE instead  -- ❌ limited depth

---

Common Pitfalls

Pitfall	Why It Happens	Fix
Ambiguous column error	No aliases	Add aliases and qualify columns
Top-level rows missing	Used inner join	Use left join
Duplicate pairs	No a.id < b.id condition	Add the condition
Self-pairs included	No condition to exclude	Add a.id < b.id
Deep hierarchy truncated	Fixed number of joins	Use a recursive CTE
Slow query	Large table with no index	Index the join columns

---

Real-World Examples

1. Employee and Manager

SELECT e.name, m.name FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;

2. Include Top-Level

SELECT e.name, m.name FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

3. Grand-Manager

SELECT e.name, m.name, g.name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
LEFT JOIN employees g ON m.manager_id = g.employee_id;

4. Recursive Org Chart

WITH RECURSIVE org_chart AS (
  SELECT employee_id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.name, e.manager_id, oc.level + 1
  FROM employees e JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart;

5. Employees Earning More Than Manager

SELECT e.name, e.salary, m.name, m.salary
FROM employees e JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

6. Shared City

SELECT a.name, b.name, a.city FROM people a
JOIN people b ON a.city = b.city AND a.id < b.id;

7. No Subordinates

SELECT e.name FROM employees e
LEFT JOIN employees sub ON sub.manager_id = e.employee_id
WHERE sub.employee_id IS NULL;

8. Same Manager

SELECT a.name, b.name, m.name
FROM employees a
JOIN employees b ON a.manager_id = b.manager_id AND a.employee_id < b.employee_id
JOIN employees m ON a.manager_id = m.employee_id;

9. Consecutive Rows

SELECT curr.date, curr.value, prev.value
FROM events curr
LEFT JOIN events prev ON prev.date = curr.date - INTERVAL '1 day';

10. Category Hierarchy

SELECT c.name AS category, p.name AS parent
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.category_id;

---

Visual

Self Join with Aliases

┌──────────────────────────────────────────────────────────────┐
│  employees e              employees m                        │
│  ┌────┬────────┬──────────┐ ┌────┬────────┬──────────┐      │
│  │ id │ name   │manager_id│ │ id │ name   │manager_id│      │
│  ├────┼────────┼──────────┤ ├────┼────────┼──────────┤      │
│  │ 1  │ Alice  │ NULL     │ │ 1  │ Alice  │ NULL     │      │
│  │ 2  │ Bob    │ 1        │ │ 2  │ Bob    │ 1        │      │
│  │ 3  │ Carol  │ NULL     │ │ 3  │ Carol  │ NULL     │      │
│  │ 4  │ David  │ 3        │ │ 4  │ David  │ 3        │      │
│  └────┴────────┴──────────┘ └────┴────────┴──────────┘      │
│                                                              │
│  JOIN ON e.manager_id = m.employee_id:                       │
│  ┌────────┬────────┐                                         │
│  │ Bob    │ Alice  │                                         │
│  │ David  │ Carol  │                                         │
│  └────────┴────────┘                                         │
└──────────────────────────────────────────────────────────────┘

Inner vs Left Self Join

┌──────────────────────────────────────────────────────────────┐
│  INNER:                                                      │
│  ┌────────┬────────┐                                         │
│  │ Bob    │ Alice  │                                         │
│  │ David  │ Carol  │                                         │
│  └────────┴────────┘                                         │
│  Top-level rows excluded (no manager)                        │
│                                                              │
│  LEFT:                                                       │
│  ┌────────┬────────┐                                         │
│  │ Alice  │ NULL   │                                         │
│  │ Bob    │ Alice  │                                         │
│  │ Carol  │ NULL   │                                         │
│  │ David  │ Carol  │                                         │
│  └────────┴────────┘                                         │
│  Top-level rows included                                     │
└──────────────────────────────────────────────────────────────┘

Self-Pair Condition

┌──────────────────────────────────────────────────────────────┐
│  WITH a.id < b.id:                                           │
│  ┌────────┬────────┐                                         │
│  │ Alice  │ Bob    │                                         │
│  │ Alice  │ Carol  │                                         │
│  │ Bob    │ Carol  │                                         │
│  └────────┴────────┘                                         │
│  Each unordered pair once                                    │
│                                                              │
│  WITHOUT the condition:                                      │
│  ┌────────┬────────┐                                         │
│  │ Alice  │ Alice  │  ← self-pair                            │
│  │ Alice  │ Bob    │                                         │
│  │ Bob    │ Alice  │  ← reverse pair                        │
│  │ Bob    │ Bob    │  ← self-pair                            │
│  │ ...    │ ...    │                                         │
│  └────────┴────────┘                                         │
│  Self-pairs and both orders included                         │
└──────────────────────────────────────────────────────────────┘

Hierarchy Traversal

┌──────────────────────────────────────────────────────────────┐
│  ONE LEVEL:                                                  │
│  JOIN employees m ON e.manager_id = m.employee_id            │
│                                                              │
│  TWO LEVELS:                                                 │
│  JOIN employees m ON e.manager_id = m.employee_id            │
│  JOIN employees g ON m.manager_id = g.employee_id            │
│                                                              │
│  UNLIMITED:                                                  │
│  WITH RECURSIVE org_chart AS (...)                           │
│  └── Repeats until no more rows match                        │
└──────────────────────────────────────────────────────────────┘

---

Summary

Item	Value
Self join	A table joined to itself
Aliases	Required; distinguish the two instances
Inner self join	Both rows must exist
Left self join	First row must exist
Hierarchy traversal	One join per level
Recursive CTE	Unlimited depth
Self-pair condition	a.id < b.id
Compare rows	Join on the relationship, compare in WHERE
Consecutive rows	Join on date arithmetic
Performance	Index the join columns

Key takeaways:

• A self join joins a table to itself using two aliases. The aliases distinguish the two instances, and the join condition relates a row in one instance to a row in the other. The alias is required, not optional.
• The join condition typically matches a foreign key to a primary key. In an employee table, the manager_id in the employee row matches the employee_id in the manager row.
• Use an inner self join when both rows must exist. This excludes top-level rows where the parent reference is NULL. Use a left self join when the first row must appear even if the second does not.
• One self join traverses one level of a hierarchy. Two self joins traverse two levels. For unlimited depth, use a recursive CTE, which joins the table to itself repeatedly until no more rows match.
• The self join makes two rows of the same table available for comparison. Employees who earn more than their manager, products priced higher than the category average, and consecutive time-series values are all self-join patterns.
• The a.id < b.id condition makes self-pair queries correct. It excludes self-pairs and reverse pairs, leaving each unordered pair exactly once. Without it, the result includes each pair twice and each row paired with itself.
• Index the join columns for performance. A self join on an unindexed column requires a full scan of the table for each row, which is expensive on large tables.

Remember: A self join is not a separate join type. It is an inner, left, right, or full outer join in which both sides are the same table. The alias is what makes it possible, and the join condition is what relates the two instances. The self join is the tool for hierarchical data, row comparisons, pairs, and sequences. Use an inner join when both rows must exist, and a left join when the first row must be preserved. Use a recursive CTE for hierarchies of unknown depth. And always qualify the columns with the alias, because the table appears twice and the database needs to know which instance each column belongs to.

---

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!