Leçon 5.7 : SELF JOIN — joindre une table à elle-même

Un SELF JOIN n'est pas du tout un mot-clé SQL, mais simplement un terme courant pour désigner la situation où une table est jointe à elle-même. En pratique, cela se réalise à l'aide des types de JOIN habituels, le plus souvent INNER JOIN ou LEFT JOIN, selon la logique recherchée. Cette technique est utile pour interroger des données hiérarchiques ou pour comparer des lignes au sein d'une même table.

Qu'est-ce qu'un SELF JOIN ?

Pour effectuer une self join, vous devez traiter une même table comme s'il s'agissait de deux tables différentes. Pour cela, vous devez utiliser des alias de table afin de donner un nom unique à chaque instance de la table. Sans alias, la base de données ne saura pas à quelle instance appartient chaque colonne.

Visualisation (hiérarchie des employés) : Imaginez une table employee dans laquelle chaque ligne contient un manager_id pointant vers le employee_id de son supérieur.

   Table A (Employés)           Table B (Managers)
   +----+-------+---------+     +----+-------+
   | id | name  | mgr_id  |     | id | name  |
   +----+-------+---------+     +----+-------+
   | 1  | Alice | NULL    |     | 1  | Alice |
   | 2  | Bob   | 1       | <-> | 1  | Alice | (La manager de Bob est Alice)
   | 3  | Carol | 1       | <-> | 1  | Alice | (La manager de Carol est Alice)
   +----+-------+---------+     +----+-------+

Syntaxe du SELF JOIN

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

• employee AS e : la première instance de la table, qui représente les employés.
• employee AS m : la deuxième instance, qui représente les managers.
• ON e.manager_id = m.id : la condition qui relie les deux instances.

Exemples pratiques (base de données Sakila)

1. Trouver des films ayant la même durée

Supposons que nous voulions trouver des paires de films ayant exactement la même durée (length). Nous pouvons joindre la table film à elle-même.

SELECT
    f1.title AS film_1,
    f2.title AS film_2,
    f1.length
FROM
    film AS f1
INNER JOIN
    film AS f2 ON f1.length = f2.length
WHERE
    f1.film_id <> f2.film_id -- Évite d'associer un film avec lui-même
LIMIT 10;

La condition f1.film_id <> f2.film_id est essentielle. Sans elle, chaque film correspondrait à lui-même, puisqu'un film a évidemment la même durée que lui-même.

2. Trouver des clients habitant dans la même ville

Si nous voulons voir quels clients habitent dans la même ville, en nous basant sur address_id dans cet exemple simplifié :

SELECT
    c1.first_name AS cust_1_first,
    c1.last_name AS cust_1_last,
    c2.first_name AS cust_2_first,
    c2.last_name AS cust_2_last,
    c1.address_id
FROM
    customer AS c1
INNER JOIN
    customer AS c2 ON c1.address_id = c2.address_id
WHERE
    c1.customer_id < c2.customer_id; -- Utilise '<' au lieu de '<>' pour éviter les doublons (A-B et B-A)

Points clés de cette leçon

• Un SELF JOIN est un terme pour désigner une table jointe avec elle-même, et non un mot-clé SQL distinct.
• Ce type de jointure est généralement réalisé avec des JOIN classiques, par exemple INNER JOIN ou LEFT JOIN.
• Les alias de table sont indispensables pour distinguer les deux instances de la même table.
• Utilisez les conditions ON pour définir la relation entre les lignes, par exemple une hiérarchie ou des attributs partagés.
• Utilisez des conditions de filtrage comme id1 <> id2 ou id1 < id2 afin d'éviter d'associer une ligne avec elle-même ou de retourner des paires en double. Dans le cas d'un LEFT JOIN, une partie de cette logique peut être placée non seulement dans WHERE, mais aussi dans la condition ON.