Self-join

Joins a table to itself, using two aliases, to relate rows of the same table.

Syntax

SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id

A self-join is an ordinary join where both sides are the same table under different aliases. Typical uses:

Hierarchies: employee → manager, category → parent category, customer → referrer.
Comparing rows: pairs of rows within the same table (same customer, different orders).

Tips:

Aliases are mandatory and should say which role each copy plays (e employee, m manager).
Use LEFT JOIN when the parent can be NULL (the CEO has no manager), or those rows disappear.
When pairing rows, add a.id < b.id to avoid matching a row with itself and listing each pair twice.
For hierarchies of unknown depth (all levels), use a recursive CTE.
For "previous/next row" comparisons, LAG/LEAD are simpler and faster than a self-join.

Examples

Employee and manager people

SELECT e.employee_id, e.first_name || ' ' || e.last_name AS employee,
       m.first_name || ' ' || m.last_name AS manager
FROM employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id
ORDER BY e.employee_id
LIMIT 10;

The top of the hierarchy keeps a NULL manager thanks to LEFT JOIN.

Referrers and their referrals shop

SELECT r.customer_id AS referrer_id, r.first_name AS referrer, count(*) AS referrals
FROM customers c
JOIN customers r ON r.customer_id = c.referred_by_customer_id
GROUP BY r.customer_id, r.first_name
ORDER BY referrals DESC, referrer_id
LIMIT 5;

c is the referred customer, r the referrer.

Pairs of products in the same category starter

SELECT a.product_name AS product_a, b.product_name AS product_b, a.category
FROM products a
JOIN products b ON b.category = a.category AND a.product_id < b.product_id
ORDER BY a.category, a.product_id, b.product_id;

a.product_id < b.product_id lists each pair once and never pairs a product with itself.

Pitfalls

INNER self-join on a nullable parent key, dropping top-level rows.
Pairing without a.id < b.id (self-pairs and every pair twice).
Using a self-join for previous-row lookups where LAG would do.