---
title: "SELF JOIN"
description: "A self join joins a table to itself, typically using table aliases to compare rows within the same table to each other."
canonical: "https://motherduck.com/glossary/self-join/"
related:
  - title: "Running dual execution (or hybrid) queries | MotherDuck Docs"
    url: "https://motherduck.com/docs/key-tasks/running-hybrid-queries/"
  - title: "Internal vs. External Storage: What's the Limit of External Tables?"
    url: "https://motherduck.com/blog/internal-vs-external-storage-whats-the-limit-of-external-tables/"
  - title: "DuckLake: The Definitive Guide — Live Author Q&A with Matt Martin & Alex Monahan | MotherDuck"
    url: "https://motherduck.com/videos/ducklake-definitive-guide-oreilly-book/"
---

# SELF JOIN

> A self join joins a table to itself, typically using table aliases to compare rows within the same table to each other.

## Overview
A self join is any join where a table is joined to another copy of itself. There's no special SQL syntax for it -- it's just a regular `JOIN` (`INNER`, `LEFT`, etc.) where the table on both sides happens to be the same one, distinguished by table aliases so column references aren't ambiguous.

```sql
SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;
```
Here, `employees` is aliased as `e` (the employee's own row) and `m` (that employee's manager's row), letting the query compare each employee's `manager_id` to another row's `employee_id` within the same table.

<glossary-callout guide="duckdb-cheatsheet-full" />

## Common use cases
- **Hierarchical data**: employee/manager relationships, category/parent-category trees, org charts (for deep, arbitrary-depth hierarchies, a recursive `WITH RECURSIVE` CTE is typically layered on top).
- **Comparing rows within the same entity**: finding pairs of events, transactions, or records that relate to each other, e.g., finding consecutive orders by the same customer.
- **Finding duplicates**: joining a table to itself on a natural key to surface rows that share the same value but differ in some other column.

```sql
-- find products with the same SKU but different prices
SELECT a.product_id, b.product_id, a.price, b.price
FROM products a
JOIN products b ON a.sku = b.sku AND a.product_id < b.product_id AND a.price <> b.price;
```
The `a.product_id < b.product_id` condition avoids matching each pair twice (once as `(a,b)` and once as `(b,a)`) and prevents a row from matching itself.

## DuckDB notes
DuckDB has no special handling for self joins -- they use the same join execution (hash join, etc.) as joining two distinct tables, since aliasing makes the two references behave as independent relations during query planning.