What is a self-join, and what's a practical use case?

A self-join is just a regular join where both sides of the FROM/JOIN reference the same table, distinguished by aliases.

Classic example: employee/manager hierarchy

-- employees: id, name, manager_id (references employees.id)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

LEFT JOIN (rather than INNER JOIN) matters here so the CEO — who has no manager, i.e. manager_id IS NULL — still appears in the result, with manager as NULL instead of being dropped.

Finding duplicate rows

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

The a.id < b.id condition both prevents matching a row with itself (a.id = b.id) and prevents each duplicate pair from showing up twice (once as a, b and once as b, a).

Comparing adjacent rows (before window functions)

-- For each day, show today's sales vs. yesterday's sales, per store
SELECT today.store_id, today.sale_date, today.amount, yesterday.amount AS prev_day_amount
FROM sales today
LEFT JOIN sales yesterday
    ON yesterday.store_id = today.store_id
    AND yesterday.sale_date = today.sale_date - INTERVAL '1 day';

This is a real, still-valid pattern, though modern SQL usually solves it more cleanly with the LAG() window function (see the window functions topic) — the self-join version is worth knowing because not every engine/version supports window functions, and it demonstrates the underlying relational logic explicitly.

Key mechanics

• Always alias both references to the table — referencing an unaliased self-joined table is ambiguous and will error.
• Watch for whether you want INNER or LEFT JOIN — a self-join on a nullable foreign key (like manager_id) needs LEFT JOIN to avoid silently dropping rows with no self-reference.