Joins Updated November 2026

Self join

Q: When do I need a self join?

When rows in a table relate to other rows in the same table: employee and manager, a category and its parent category, or any comparison between two rows such as consecutive readings.

Q: How do I get the full chain of managers?

A self join only goes one level up. For the whole hierarchy use a recursive CTE: WITH RECURSIVE chain AS (...) UNION ALL ..., supported by SQLite and PostgreSQL.

A self join is a normal join where both sides are the same table under two different aliases: SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON m.id = e.manager_id pairs each employee with their manager. It is the standard way to query a table that references itself.

Syntax

SELECT a.col, b.col FROM table a JOIN table b ON a.key = b.other_key;

Examples

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

Result: employee | manager Ana | NULL Ben | Ana Carl | Ana

LEFT JOIN keeps the top person whose manager_id is NULL.

SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary;

Result: Employees who earn more than their manager

SELECT a.name, b.name, a.country
FROM customers a
JOIN customers b ON a.country = b.country AND a.id < b.id;

Result: Each pair of customers from the same country, listed once

a.id < b.id avoids pairing a row with itself and avoids listing each pair twice.

Common mistakes

Omitting aliases (FROM employees JOIN employees) raises an ambiguous table error; both copies need distinct aliases.
Using a.id <> b.id instead of a.id < b.id lists every pair twice (A,B and B,A).
Using INNER JOIN for the employee-manager list drops the person at the top who has no manager; use LEFT JOIN.

Practice Self join

These drills check your answer on live data, free, no account needed.

Employee and managerReturn each employee's name and their manager's name. Employees without a manager show NULL.
Earns more than the managerReturn the names of employees who earn more than their own manager.

Questions

When do I need a self join?