Self Join SQL : interroger une table sur elle-même

Le self join SQL consiste à joindre une table avec elle-même — une technique qui déroute beaucoup de candidats en entretien, alors qu'elle résout des problèmes très concrets : hiérarchies, comparaisons entre lignes, doublons, séquences temporelles.

Contrairement aux jointures classiques qui relient deux tables distinctes, le self join exploite deux alias d'une même table pour créer des paires de lignes comparables. Cette subtilité syntaxique cache une puissance analytique réelle : identifier le manager d'un employé, détecter des doublons, comparer des ventes consécutives ou construire des arbres d'organisation.

Dans cet article, vous allez apprendre la syntaxe exacte du self join, découvrir ses cas d'usage les plus fréquents en entretien data, comprendre quand le préférer aux fonctions fenêtre, et éviter les pièges classiques qui font perdre des points lors d'un test technique.

Qu'est-ce qu'un self join et pourquoi l'utiliser ?

Un self join (ou auto-jointure) est une jointure ordinaire — INNER JOIN, LEFT JOIN, etc. — appliquée à une seule et même table, référencée deux fois sous des alias distincts. SQL traite chaque alias comme une table virtuelle indépendante, ce qui permet de comparer ou de relier n'importe quelles lignes de cette table entre elles.

La vraie question est : pourquoi aurait-on besoin de ça ? La réponse tient dans la structure de certaines données réelles. Dans une table employees, la colonne manager_id référence une autre ligne de la même table. Pour afficher le nom du manager à côté du nom de l'employé, vous avez besoin de lire la table deux fois simultanément — d'où le self join.

Sans cette technique, vous seriez obligé de faire deux requêtes séparées et de reconstituer le résultat en dehors de SQL, ce qui est inefficace et souvent impossible dans un contexte de rapport automatisé.

Syntaxe de base du self join

La syntaxe ne diffère en rien d'un JOIN classique, sauf que la même table apparaît deux fois avec des alias différents.

SELECT
    e.employee_id,
    e.first_name        AS employe,
    m.first_name        AS manager
FROM employees AS e
INNER JOIN employees AS m
    ON e.manager_id = m.employee_id;

Ici, e représente chaque employé et m représente la ligne correspondant à son manager. La clause ON relie la clé étrangère manager_id de l'employé à la clé primaire employee_id du manager.

Utiliser LEFT JOIN pour inclure les employés sans manager

Avec un INNER JOIN, les lignes sans manager (c'est-à-dire celles dont manager_id est NULL — souvent le PDG) disparaissent du résultat. Un LEFT JOIN corrige ça :

SELECT
    e.employee_id,
    e.first_name        AS employe,
    m.first_name        AS manager
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.employee_id;

Le PDG apparaît maintenant avec NULL dans la colonne manager. C'est le comportement attendu dans la quasi-totalité des exercices d'entretien sur ce thème. Pour aller plus loin sur les différences entre INNER et LEFT JOIN, consultez notre guide INNER JOIN vs LEFT JOIN : différences et guide complet SQL.

Les 5 cas d'usage les plus fréquents en entretien

1. Hiérarchie employé-manager

C'est le cas canonique, celui que tout recruteur data connaît. Le schéma type est une table employees avec une colonne manager_id qui pointe vers employee_id dans la même table (relation auto-référentielle).

-- Afficher tous les employés avec le nom de leur manager
SELECT
    e.employee_id,
    CONCAT(e.first_name, ' ', e.last_name)  AS employe,
    CONCAT(m.first_name, ' ', m.last_name)  AS manager,
    e.department
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.employee_id
ORDER BY m.last_name, e.last_name;

Ce résultat permet de produire un organigramme complet directement depuis SQL.

2. Comparer des lignes successives dans le temps

Supposons une table daily_sales avec les colonnes sale_date et revenue. Vous voulez calculer la croissance jour après jour :

SELECT
    d1.sale_date                                        AS jour,
    d1.revenue                                          AS ca_jour,
    d2.revenue                                          AS ca_veille,
    ROUND((d1.revenue - d2.revenue) / d2.revenue * 100, 2) AS variation_pct
FROM daily_sales AS d1
LEFT JOIN daily_sales AS d2
    ON d2.sale_date = d1.sale_date - INTERVAL '1 day'
ORDER BY d1.sale_date;

Cette approche est particulièrement utile quand les fonctions fenêtre ne sont pas disponibles ou quand la logique de décalage est complexe. Pour une approche alternative avec les fonctions fenêtre, voir notre article sur l'exercice SQL : moyenne mobile avec les fonctions fenêtre.

3. Détecter des doublons ou des paires similaires

Un self join permet de trouver des paires de lignes qui partagent une caractéristique commune, par exemple des clients avec le même email mais des IDs différents :

SELECT
    a.customer_id   AS id_1,
    b.customer_id   AS id_2,
    a.email
FROM customers AS a
INNER JOIN customers AS b
    ON  a.email = b.email
    AND a.customer_id < b.customer_id;

La condition a.customer_id < b.customer_id est cruciale : elle évite d'obtenir les doublons en miroir (paire A-B et paire B-A) ainsi que les auto-correspondances d'une ligne avec elle-même.

4. Trouver les produits dans la même catégorie

Imaginez une table products avec product_id, product_name et category_id. Pour lister toutes les paires de produits appartenant à la même catégorie :

SELECT
    p1.product_name AS produit_1,
    p2.product_name AS produit_2,
    p1.category_id
FROM products AS p1
INNER JOIN products AS p2
    ON  p1.category_id = p2.category_id
    AND p1.product_id  < p2.product_id
ORDER BY p1.category_id, p1.product_name;

Ce type de requête est utilisé pour générer des recommandations "vous aimerez aussi" ou pour valider la cohérence d'un catalogue.

5. Construire une matrice de distances ou de comparaisons

Dans une table cities avec les colonnes city_id, city_name, latitude et longitude, un self join permet de calculer la distance entre chaque paire de villes :

SELECT
    c1.city_name    AS ville_depart,
    c2.city_name    AS ville_arrivee,
    ROUND(
        6371 * ACOS(
            SIN(RADIANS(c1.latitude)) * SIN(RADIANS(c2.latitude)) +
            COS(RADIANS(c1.latitude)) * COS(RADIANS(c2.latitude)) *
            COS(RADIANS(c2.longitude - c1.longitude))
        )
    , 0) AS distance_km
FROM cities AS c1
CROSS JOIN cities AS c2
WHERE c1.city_id < c2.city_id
ORDER BY distance_km;

Ici, on utilise un CROSS JOIN (qui est aussi une forme de self join) combiné à un filtre pour éviter les doublons.

Self join vs fonctions fenêtre : quand choisir quoi ?

Cette question revient régulièrement en entretien. Les deux approches peuvent résoudre des problèmes similaires, mais leurs performances et leur lisibilité diffèrent.