SELF JOIN

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.

Copy code
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.

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.

Copy code
-- 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.