Joindre une table dérivée

Un rapport des adhérents doit souvent afficher, sur la même ligne, leurs informations et un indicateur calculé à partir de leurs emprunts. Si vous calculez cet indicateur directement dans la requête principale, vous devez aussi gérer le regroupement de ses autres colonnes. Une table dérivée permet de séparer le calcul du nombre d’emprunts de la présentation des adhérents.

Nous supposons ici que adherents possède les colonnes adherent_id et nom, et que emprunts possède emprunt_id et adherent_id. Comme dans les leçons précédentes, la clé étrangère emprunts.adherent_id relie chaque emprunt à son adhérent. La requête ci-dessous compte les emprunts par adhérent, puis rattache ces totaux à la liste complète des adhérents.

Calculer les emprunts dans une table dérivée

Une table dérivée est une sous-requête placée dans la clause FROM : son résultat temporaire est traité comme une source de lignes par la requête extérieure. Elle n’ajoute pas de table permanente à la base. Dans cet exemple, la sous-requête produit une ligne par adhérent ayant au moins un emprunt, avec son identifiant et son total.

SELECT
    a.adherent_id,
    a.nom,
    COALESCE(stats.nombre_emprunts, 0) AS nombre_emprunts
FROM adherents AS a
LEFT JOIN (
    SELECT
        e.adherent_id,
        COUNT(e.emprunt_id) AS nombre_emprunts
    FROM emprunts AS e
    GROUP BY e.adherent_id
) AS stats
    ON stats.adherent_id = a.adherent_id
ORDER BY a.nom, a.adherent_id;

Une ligne par adhérent, triée par nom puis par identifiant. La colonne nombre_emprunts contient le nombre d’emprunts de l’adhérent ; elle vaut 0 si celui-ci n’en a aucun.

La sous-requête entre parenthèses compte les identifiants d’emprunt et regroupe les lignes par e.adherent_id. Son résultat peut ressembler à une petite table composée de deux colonnes : adherent_id et nombre_emprunts. L’alias stats lui donne un nom utilisable par la requête extérieure ; MariaDB exige qu’une table dérivée ait un alias.

La requête principale part de adherents et utilise LEFT JOIN pour associer les totaux calculés. Ainsi, les adhérents sans emprunt restent présents même s’ils n’ont aucune ligne correspondante dans stats. Pour eux, stats.nombre_emprunts est NULL après la jointure ; COALESCE remplace cette valeur par zéro dans le résultat affiché.

Comprendre la séparation des calculs

La table dérivée résout ici une difficulté pratique : le comptage est effectué uniquement à partir des emprunts, avant que le résultat soit joint aux données des adhérents. La requête extérieure peut donc sélectionner le nom de l’adhérent sans le faire entrer dans le regroupement de la sous-requête. Vous avez déjà étudié les règles de regroupement dans « Regrouper les résultats » ; cette leçon les applique au calcul intermédiaire, sans les reprendre en détail.

La condition de jointure rapproche ensuite les deux résultats à l’aide de l’identifiant d’adhérent. Le sous-résultat contient au plus une ligne par identifiant, car il est regroupé par cette colonne. La jointure ne multiplie donc pas les adhérents en fonction du nombre d’emprunts : ce nombre est déjà ramené à une seule valeur par personne avant le rapprochement.

Cette forme est utile lorsque vous souhaitez joindre un indicateur calculé à une liste principale : un total par adhérent, par exemple, ou un nombre d’exemplaires par œuvre. Le principe reste le même : la sous-requête expose les colonnes nécessaires, la requête extérieure les relie au bon identifiant et construit les colonnes de sortie. Les œuvres et exemplaires ne sont pas nécessaires à ce rapport, car le nombre demandé porte directement sur les emprunts.

Vérifiez les cas limites en lisant le résultat : un adhérent avec plusieurs emprunts doit apparaître une fois avec le total correspondant, et un adhérent sans emprunt doit apparaître une fois avec zéro. Si une colonne de la table dérivée est introuvable, contrôlez qu’elle est bien produite par la sous-requête et référencée avec le bon alias. La clause ORDER BY finale trie les lignes du rapport, sans modifier les comptes calculés.

À retenir

• Une sous-requête dans FROM forme une table dérivée dont la requête extérieure peut exploiter les colonnes.
• En MariaDB, vous devez attribuer un alias à chaque table dérivée.
• Regroupez les emprunts par adhérent dans la sous-requête, puis joignez ce résultat sur l’identifiant correspondant.
• Utilisez LEFT JOIN pour conserver les adhérents sans emprunt et COALESCE pour afficher leur total comme zéro.

Dans la prochaine leçon, vous définirez les indicateurs et la granularité d’un rapport de fréquentation et de retards avant d’en assembler la requête complète.