SQL self join: join a table with itself

Sometimes the relationship lives inside a single table. In the employes (employees) table, the manager_id column holds the identifier of… another employee. To show “Léa reports to Hugo”, you need to join the employes table with itself: that is a self join.

Syntax

SELECT e.nom, m.nom AS manager
FROM employes e
LEFT JOIN employes m ON m.id = e.manager_id;

How it works

A self join has no special keyword: it is a regular JOIN where the same table appears twice. To tell them apart, aliases are required: here e is the employee and m their manager.

A LEFT JOIN is often used, to keep the rows that have no “parent” (the CEO has no manager).

Self joins are also used to compare rows with each other: employees who earn more than their manager, products in the same category, customers from the same city…

Diagram

Simplified diagram to show the idea. The examples below use the real SQL Quest practice database (a fictional online shop: see the full schema).

Examples with their results

SELECT e.prenom || ' ' || e.nom AS employe, e.poste,
       m.prenom || ' ' || m.nom AS manager
FROM employes e
LEFT JOIN employes m ON m.id = e.manager_id
LIMIT 8;

SELECT m.prenom, m.nom, m.poste, COUNT(e.id) AS equipe
FROM employes m
JOIN employes e ON e.manager_id = m.id
GROUP BY m.id
ORDER BY equipe DESC;

SELECT e.nom, e.salaire, m.nom AS manager, m.salaire AS salaire_manager
FROM employes e
JOIN employes m ON m.id = e.manager_id
WHERE e.salaire > m.salaire / 2
LIMIT 8;

Common mistakes

SELECT nom, nom FROM employes JOIN employes ON id = manager_id;

SELECT e.nom, m.nom FROM employes e JOIN employes m ON m.id = e.manager_id;

SELECT e.nom, m.nom AS manager FROM employes e JOIN employes m ON e.id = m.manager_id;

SELECT e.nom, m.nom AS manager FROM employes e JOIN employes m ON m.id = e.manager_id;

SELECT e.nom, m.nom FROM employes e JOIN employes m ON m.id = e.manager_id;

SELECT e.nom, m.nom FROM employes e LEFT JOIN employes m ON m.id = e.manager_id;

Exercises

Try writing the query yourself before opening the solution.

SELECT e.prenom, e.nom FROM employes e JOIN employes m ON m.id = e.manager_id WHERE m.poste = 'Directeur commercial';

SELECT m.nom, ROUND(AVG(e.salaire)) AS salaire_moyen_equipe FROM employes m JOIN employes e ON e.manager_id = m.id GROUP BY m.id;

Frequently asked questions