Docs SQLite exécutables : Self Join

Q: C'est quoi un self join en SQLite ?

Un self join, c'est tout simplement un JOIN classique où une table est jointe avec elle-même. On lui donne deux alias différents pour que SQLite la traite comme deux sources de lignes distinctes, puis on relie les lignes via une colonne qui exprime un lien interne — typiquement une relation parent/enfant comme employé/manager.

Q: Pourquoi les alias sont-ils obligatoires dans un self join ?

Sans alias, SQLite ne peut pas savoir de quelle « copie » de la table vous parlez quand vous écrivez un nom de colonne. En attribuant un alias à chaque instance (par exemple e pour l'employé et m pour le manager), vous pouvez écrire e.manager_id = m.id sans ambiguïté. Et ce ne sont pas des alias optionnels : la requête ne passera même pas le parseur sans eux.

Q: Self join ou sous-requête : que choisir ?

Le self join s'impose dès que vous voulez récupérer des colonnes des deux lignes dans le résultat — par exemple le nom de l'employé et celui de son manager sur la même ligne. La sous-requête suffit quand vous voulez juste filtrer ou aller chercher une seule valeur. Pour des hiérarchies imbriquées en profondeur, ni l'un ni l'autre ne convient vraiment : c'est le moment de sortir une CTE récursive.

Une self join, c'est juste une jointure avec des alias

Il n'y a rien de magique dans une self join SQLite. C'est un JOIN tout ce qu'il y a de plus classique, sauf que les deux côtés pointent vers la même table. Le seul détail, c'est que SQLite a besoin de distinguer les deux copies : on attribue donc un alias à chacune.

On y a recours dès qu'une ligne d'une table fait référence à une autre ligne de la même table. L'exemple typique : une table employees où chaque ligne possède un manager_id qui pointe vers un autre employé :

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT * FROM employees;

Ada n'a pas de manager. Boris et Cleo dépendent d'Ada. Diego et Esme dépendent de Boris. Toute la hiérarchie tient dans une seule table — et c'est précisément là qu'un self join SQLite prend tout son sens.

La structure de base

Pour associer chaque employé au nom de son manager, on joint la table employees à elle-même. Une copie joue le rôle de « l'employé », l'autre celui du « manager » :

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

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

Voyez ça comme deux tables qui partagent simplement le même stockage. e représente la ligne de l'employé ; m celle du manager. La condition de jointure e.manager_id = m.id fait le lien entre les deux : pour chaque employé, on cherche la ligne dans m dont l'id correspond au manager_id de l'employé.

Vous avez remarqué qu'Ada n'apparaît pas ? Son manager_id vaut NULL, et INNER JOIN écarte les lignes qui n'ont pas de correspondance.

Conserver les lignes sans correspondance : LEFT JOIN

Si vous voulez retrouver tout le monde dans le résultat, y compris les personnes sans manager, passez à LEFT JOIN :

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

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

Et voilà, Ada apparaît avec NULL dans la colonne manager. La mécanique du self join reste identique, c'est juste le type de jointure qui fait ce que LEFT JOIN fait toujours : conserver toutes les lignes de la table de gauche et remplir avec des valeurs vides quand il n'y a pas de correspondance.

C'est généralement la forme que vous voulez quand vous affichez une liste de personnes. « Pas de manager », c'est aussi une information ; supprimer la ligne reviendrait à perdre ça.

Les alias de table ne sont pas optionnels

Tentez la jointure sans alias et SQLite n'a aucune idée de ce que vous voulez dire :

SELECT name, manager_id FROM employees JOIN employees ON manager_id = id;
-- Erreur : nom de colonne ambigu : name

Chaque colonne apparaît en double — une fois pour chaque copie de la table — et SQLite ne sait pas laquelle choisir. Les alias règlent le problème en donnant un nom propre à chaque instance. Choisissez des alias qui décrivent le rôle joué par la ligne, plutôt que la table elle-même :

e et m pour employé/manager.
parent et child pour les hiérarchies.
a et b quand vous comparez des paires quelconques.

C'est l'alias qui rend une self join lisible, point final.

Trouver des paires dans une même table

Les self joins ne servent pas qu'aux hiérarchies. Dès que vous avez besoin de comparer des lignes d'une même table, le motif s'applique. Voici une liste de produits, où l'on veut récupérer toutes les paires affichant le même prix :

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products (name, price) VALUES
    ('Tasse',     9.99),
    ('Carnet', 9.99),
    ('Stylo',     2.50),
    ('Crayon',  2.50),
    ('Lampe',   29.00);

SELECT a.name AS product_a, b.name AS product_b, a.price
FROM products AS a
INNER JOIN products AS b
    ON a.price = b.price
   AND a.id < b.id;

Deux choses à repérer ici. Premièrement, a.price = b.price est la véritable condition de correspondance. Deuxièmement, a.id < b.id empêche la requête de renvoyer chaque paire deux fois (une fois en (Tasse, Carnet), puis en (Carnet, Tasse)) et évite aussi qu'une ligne soit appariée avec elle-même. Ce petit truc du < mérite d'être retenu — il revient dès qu'on doit lister des paires.

Remonter de deux niveaux dans la hiérarchie

Un self join sur SQLite gère un seul saut dans une hiérarchie. Vous voulez le manager du manager de chaque employé ? Il suffit de joindre la table trois fois :

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name      AS employe,
    m.name      AS manager,
    gm.name     AS grand_manager
FROM employees AS e
LEFT JOIN employees AS m  ON e.manager_id = m.id
LEFT JOIN employees AS gm ON m.manager_id = gm.id
ORDER BY e.id;

Chaque nouvel alias représente un niveau de plus en remontant l'arbre. Ça marche bien pour deux ou trois sauts, mais ça atteint vite ses limites : il faudrait connaître la profondeur de la hiérarchie au moment d'écrire la requête, et rajouter une jointure pour chaque niveau. C'est précisément ce mur que les CTE récursives sont venues faire tomber.

Quand éviter le self join

Le self join est l'outil adéquat lorsque vous avez besoin de colonnes des deux côtés de la relation dans le résultat. Si vous voulez seulement filtrer — par exemple, retrouver tous les employés dont le manager est Ada — une sous-requête se lit souvent mieux :

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT name
FROM employees
WHERE manager_id = (SELECT id FROM employees WHERE name = 'Ada');

Pas besoin de jongler avec les alias, et l'intention saute aux yeux. La règle de base : vous voulez récupérer les données des deux lignes dans le résultat ? Self join. Vous avez juste besoin d'une valeur pour faire une comparaison ? Sous-requête.

Pour les hiérarchies de profondeur arbitraire (organigrammes, arborescences de fichiers, fils de commentaires), aucun des deux ne tient la route. Là, on bascule sur les CTE récursives.

Et maintenant : les sous-requêtes

Le self join et les sous-requêtes se recoupent souvent, et savoir lequel choisir t'évitera de te casser la tête sur votre SQL plus tard. La page suivante creuse en profondeur les sous-requêtes — scalaires, corrélées et avec IN — en montrant quand chacune brille vraiment.

Questions fréquentes

C'est quoi un self join en SQLite ?

Un self join, c'est tout simplement un JOIN classique où une table est jointe avec elle-même. On lui donne deux alias différents pour que SQLite la traite comme deux sources de lignes distinctes, puis on relie les lignes via une colonne qui exprime un lien interne — typiquement une relation parent/enfant comme employé/manager.

Pourquoi les alias sont-ils obligatoires dans un self join ?

Sans alias, SQLite ne peut pas savoir de quelle « copie » de la table vous parlez quand vous écrivez un nom de colonne. En attribuant un alias à chaque instance (par exemple e pour l'employé et m pour le manager), vous pouvez écrire e.manager_id = m.id sans ambiguïté. Et ce ne sont pas des alias optionnels : la requête ne passera même pas le parseur sans eux.

Self join ou sous-requête : que choisir ?