SELF JOIN

Introduction

A self join joins a table to itself. Used for hierarchies (employee → manager), pairs of related rows (find duplicates) or comparing rows in the same table.

Understanding the topic

Core concepts to understand:

• FROM employees e JOIN employees m ON e.manager_id = m.id.
• Use distinct aliases for the two copies.
• Common in org charts, threaded comments.
• Find duplicates: FROM t a JOIN t b ON a.email=b.email AND a.id<b.id.
• Recursive CTE often a cleaner alternative for hierarchies.

Syntax reference

Visual workflow / architecture:

employees           employees
   e (worker)    →    m (manager)
   id  manager_id     id name
   1   NULL           NULL
   2   1              1  CEO
   3   1              1  CEO
   4   2              2  CTO

SELECT <cols>
FROM   <t> AS a
JOIN   <t> AS b
       ON  <a.col> = <b.related_col>;

SELECT e.name AS worker, m.name AS manager
FROM   employees e
JOIN   employees m ON e.manager_id = m.id;

Real-world use

Org-chart pages, 'find duplicate emails' admin queries, and threaded forum replies all use self-joins.

Best practices

• Use clear aliases (e, m).
• Consider recursive CTEs for deep hierarchies.
• Index the join column on both sides (it's the same column!).

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Why use a self join?
• Q2. Find duplicate emails query.
• Q3. Self join vs recursive CTE.
• Q4. Aliasing rules.
• Q5. Org-chart query example.