JOIN ou LEFT JOIN : la moyenne de chaque élève

Les deux requêtes ne diffèrent que d'un mot, et rendent trois ou quatre lignes.

SELECT e.nom, AVG(n.valeur) AS moyenne
FROM Eleve e JOIN Note n ON n.eleve = e.id       -- 3 lignes
GROUP BY e.id, e.nom ORDER BY e.id;

SELECT e.nom, AVG(n.valeur) AS moyenne
FROM Eleve e LEFT JOIN Note n ON n.eleve = e.id  -- 4 lignes
GROUP BY e.id, e.nom ORDER BY e.id;

Oui, la réponse dépend de la jointure, et il n'y a pas de « bonne » réponse dans l'absolu : il y a deux questions différentes.

• « Quelle est la moyenne des élèves qui ont des notes ? » — JOIN, trois lignes.
• « Quelle est la moyenne de chaque élève ? » — LEFT JOIN, quatre lignes, dont une moyenne indéterminée.

Le NULL de Dupont est une information, pas une erreur. Il dit : « cet élève existe et n'a pas de moyenne ». Un $0$ à sa place serait un mensonge — il ferait chuter la moyenne de la classe et le classerait dernier alors qu'il n'a rien passé.

Pourquoi le défaut est difficile à voir, comme le cours le signale : avec JOIN, le résultat est plausible. Trois lignes de moyennes correctes, aucune erreur, aucun avertissement. Seul un contrôle du nombre de lignes le révèle — et c'est pourquoi une requête d'inventaire (« combien d'élèves ? ») vaut d'être écrite à côté de toute requête de synthèse. C'est la même discipline que les tests du chapitre chap:discipline : on vérifie la cardinalité attendue, pas seulement les valeurs.