Self JOIN joins a table to itself. Useful for hierarchical data and comparing rows within the same table.

Employee-Manager Hierarchy

SELECT e.name as employee, m.name as manager

FROM employees e

LEFT JOIN employees m ON e.manager_id = m.id;

Find Duplicates

SELECT a.id, a.email

FROM customers a

JOIN customers b ON a.email = b.email AND a.id != b.id;

Compare Sequential Records

SELECT

a.date,

a.value as today,

b.value as yesterday,

a.value - b.value as change

FROM daily_stats a

LEFT JOIN daily_stats b ON a.date = DATE_ADD(b.date, INTERVAL 1 DAY);

Find Related Products

SELECT a.name as product, b.name as related

FROM products a

JOIN product_relations r ON a.id = r.product_id

JOIN products b ON r.related_id = b.id;

Organizational Chart

SELECT

e.name,

e.title,

m.name as reports_to

FROM employees e

LEFT JOIN employees m ON e.manager_id = m.id

ORDER BY m.name, e.name;

Generate Self JOIN Queries

AI2sql creates self-referencing queries easily.