SQL Self JOIN — Joining a Table to Itself

Q: SQL Self JOIN — Joining a Table to Itself

## What Is a Self JOIN? A self join is a regular join where a table is joined to itself. You use table aliases to distinguish between the two "copies" of the same table. Self joins are not a separate join type — you can use INNER, LEFT, or any other join keyword. ### Syntax — Employee-Manager Example sql SELECT emp.name AS employee, mgr.name AS manager FROM employees AS emp LEFT JOIN employees AS mgr ON emp.manager_id = mgr.id; Here, employees appears twice: once as emp (to get the em

Q: When is this useful?

Use self joins for employee-manager hierarchies, finding duplicate records, comparing sequential time-series rows, or any scenario where relationships exist between rows in the same table.

Learn how to use a SQL self join to compare rows within the same table. Covers employee-manager hierarchies, finding duplicates, and recursive patterns.

Practical Examples

Detailed Explanation

What Is a Self JOIN?

A self join is a regular join where a table is joined to itself. You use table aliases to distinguish between the two "copies" of the same table. Self joins are not a separate join type — you can use INNER, LEFT, or any other join keyword.

Syntax — Employee-Manager Example

SELECT
  emp.name AS employee,
  mgr.name AS manager
FROM employees AS emp
LEFT JOIN employees AS mgr
  ON emp.manager_id = mgr.id;

Here, employees appears twice: once as emp (to get the employee) and once as mgr (to look up the manager). The LEFT JOIN ensures employees without a manager (like the CEO) still appear with NULL in the manager column.

Finding Duplicates

Self joins are excellent for detecting duplicate data:

SELECT a.id, b.id, a.email
FROM users AS a
INNER JOIN users AS b
  ON a.email = b.email
  AND a.id < b.id;

The a.id < b.id condition prevents each pair from appearing twice and prevents a row from matching itself.

Hierarchical Data

Self joins power organizational charts, category trees, and threaded comments. For deep hierarchies (more than 2-3 levels), consider using a recursive CTE (WITH RECURSIVE) instead, which handles arbitrary depth without nesting multiple self joins.

Performance Considerations

A self join doubles the read load on the same table. Ensure the join columns are indexed. For very large tables, consider materializing the hierarchy into a separate mapping table or using database-specific features like PostgreSQL's ltree extension.

When Not to Use

If you find yourself writing three or more self joins to traverse a hierarchy, it is a strong signal that a recursive CTE or a graph database would be more appropriate.

Use Case