Jointures : lire à travers les relations

Objectifs

À la fin de cette leçon, vous saurez :

• assembler deux tables avec JOIN ;
• distinguer JOIN et LEFT JOIN par leur comportement sur les absences ;
• modéliser une relation many-to-many avec table intermédiaire.

Le besoin

Les tâches ne stockent que user_id. Pour afficher « Ana — Courses », il faut rejoindre les deux tables :

SELECT users.nom, tasks.titre
FROM tasks
JOIN users ON tasks.user_id = users.id;

nom   titre
Ana   Courses
Ana   Réviser SQL
Bob   Dormir

ON exprime la condition de liaison : la FK rencontre sa PK. C'est le motif universel des lectures d'applications relationnelles.

INNER vs LEFT JOIN

Ajoutons Claire, sans aucune tâche. Que donne FROM users JOIN tasks ... ?

→ Claire est absente du résultat : le JOIN (dit inner) exige une correspondance.

Pour lister TOUS les utilisateurs, tâches ou non :

SELECT users.nom, tasks.titre
FROM users
LEFT JOIN tasks ON tasks.user_id = users.id;

nom    titre
Ana    Courses
Ana    Réviser SQL
Bob    Dormir
Claire NULL          ← présente, sans tâche

LEFT JOIN garde toute la table de gauche et complète par NULL à droite. Cas d'usage type :

-- utilisateurs SANS tâche
SELECT nom FROM users
LEFT JOIN tasks ON tasks.user_id = users.id
WHERE tasks.id IS NULL;

Many-to-many : la table intermédiaire

Étudiants ↔ cours : un étudiant a plusieurs cours, un cours a plusieurs étudiants. On crée inscriptions :

CREATE TABLE inscriptions (
  user_id integer REFERENCES users(id),
  cours_id integer REFERENCES cours(id),
  PRIMARY KEY (user_id, cours_id)      -- pas de doublon d'inscription
);

-- les cours de Bob :
SELECT cours.titre
FROM inscriptions
JOIN cours ON cours.id = inscriptions.cours_id
WHERE inscriptions.user_id = 2;

Deux one-to-many composent un many-to-many : c'est LE pattern des systèmes de rôles, tags, favoris...

Exercice

1. Listez chaque tâche avec le nom de son propriétaire.
2. Comptez les tâches par utilisateur (indice : COUNT(tasks.id) + GROUP BY users.nom).
3. Trouvez les utilisateurs sans tâche.
4. Ajoutez la table intermédiaire pour étudiant↔cours et écrivez la requête « cours suivis par Ana ».

Résumé

• JOIN = PK rencontre FK ; LEFT JOIN préserve les orphelins.
• IS NULL après LEFT JOIN = test d'absence.
• Many-to-many = table pivot avec double FK.

SELECT users.nom, COUNT(tasks.id) AS nombre
FROM users
LEFT JOIN tasks ON tasks.user_id = users.id
GROUP BY users.nom;

SELECT cours.titre FROM cours
JOIN inscriptions ON inscriptions.cours_id = cours.id
JOIN users ON users.id = inscriptions.user_id
WHERE users.nom = 'Ana';