Jointures, agrégats, CTE et fenêtres

Comprendre le mécanisme

Une jointure relie des lignes ; sa cardinalité peut multiplier un résultat avant agrégation. WHERE filtre les lignes avant GROUP BY, HAVING filtre les groupes après agrégation. Une fonction fenêtre calcule sur un ensemble lié tout en conservant les lignes d’origine. PARTITION BY organise les groupes de fenêtre et ORDER BY leur ordre logique ; il faut aussi expliciter les égalités lorsque le résultat dépend d’un classement déterministe.

Raisonner et vérifier

Une CTE structure une requête mais n’est pas automatiquement une optimisation magique. Définissez d’abord la granularité de sortie : une ligne par réservation, produit ou tenant. Testez avec doublons, groupes vides et valeurs null. NOT IN peut devenir inconnu en présence de null dans la sous-requête ; un NOT EXISTS correctement corrélé exprime souvent plus clairement l’absence recherchée.

Filtrer des lignes ou des groupes

La logique SQL distingue le filtre de lignes effectué par WHERE et les conditions appliquées aux groupes avec HAVING. Commencez par déterminer quelles lignes entrent dans l’agrégation, puis quelle propriété du groupe doit être vérifiée. Déplacer une condition sans analyser son niveau peut changer le résultat. Un jeu d’essai contenant plusieurs groupes, dont un vide de valeurs utiles, rend cette différence visible.

Une fenêtre conserve le contexte des lignes

Une fonction de fenêtre calcule une information sur un ensemble lié à chaque ligne sans nécessairement réduire ce résultat à une ligne par groupe. C’est utile pour afficher une réservation et son rang ou un cumul. Comparez le nombre et l’identité des lignes avant et après le calcul. Une fonction agrégée avec GROUP BY répond à un autre besoin et ne peut pas être substituée sans revoir le contrat.

L’ordre appartient à chaque calcul concerné

L’ordre utilisé dans une fenêtre définit le sens d’un rang ou d’un cumul. Un ORDER BY final organise l’affichage du résultat, mais ne remplace pas automatiquement cet ordre de calcul. Si plusieurs événements ont la même date, ajoutez un départage pertinent comme un identifiant stable. Sans règle explicite sur les égalités, une sortie observée sur quelques lignes ne démontre pas une sélection reproductible.

Lire le cadre de fenêtre

La frame détermine quelles lignes participent à certains calculs de fenêtre autour de la ligne courante. Un cumul et une valeur de fin de partition ne demandent pas forcément le même cadre. Lisez la définition complète avant de conclure depuis le nom de la fonction. Testez notamment des valeurs de tri égales : elles peuvent révéler une différence que des dates toutes distinctes ne montrent pas.

Préserver une absence avec LEFT JOIN

Une jointure externe peut conserver une ligne de gauche sans correspondance à droite. Ajouter ensuite dans WHERE une condition qui rejette la valeur nulle de droite peut supprimer cette ligne conservée. Déterminez si le filtre appartient à la condition de jointure ou au résultat final selon le besoin. Vérifiez les cas sans correspondance, avec une correspondance et avec plusieurs correspondances avant de valider le total obtenu.

La logique de NULL change les exclusions

Une comparaison ordinaire avec NULL peut produire un résultat inconnu, que WHERE ne conserve pas comme vrai. Cela explique le piège de NOT IN lorsqu’une sous-requête contient NULL : l’exclusion peut éliminer des lignes attendues. Étudiez les valeurs une par une et choisissez une formulation conforme au contrat, souvent une recherche d’absence avec NOT EXISTS. Filtrer les nulls est une autre décision qui doit être explicitement justifiée.

Stabiliser la sélection avec LIMIT

LIMIT borne le nombre de lignes renvoyées, sans créer leur ordre. Un tri par date seule peut encore laisser une ambiguïté lorsque des dates sont égales. Définissez un ordre total pertinent et testez ces égalités. Pour une pagination concurrente, documentez aussi l’effet des insertions entre appels : une sélection stable à un instant ne promet pas automatiquement un parcours identique sur une base qui évolue.

La cardinalité des jointures influence les agrégats

Une ligne de gauche peut correspondre à plusieurs lignes de droite. Si vous additionnez ensuite une valeur de gauche, elle peut être comptée plusieurs fois. Dessinez la cardinalité attendue et vérifiez le nombre de lignes après la jointure avant d’interpréter un total. Un résultat numérique plausible n’est pas une preuve de justesse ; préparez un cas où une seule référence possède plusieurs dépendances.

Structurer avec un CTE sans promettre la vitesse

Un CTE peut rendre une requête plus lisible en nommant une étape. Il ne garantit pas une amélioration de performance universelle. La version du moteur, la forme de la requête et les options influencent le plan. Validez d’abord le résultat, puis mesurez le comportement sur des données représentatives. Une refactorisation de lisibilité doit conserver les prédicats, cardinalités et conventions de NULL attendus.

Un jeu d’essai qui discrimine réellement

Un petit jeu utile doit inclure les cas qui distinguent deux interprétations possibles : doublons, égalités, lignes sans correspondance, valeurs nulles et limites de sélection. Écrivez à la main le résultat attendu avant d’exécuter la requête. Si deux requêtes incorrectement supposées équivalentes donnent le même résultat sur votre fixture, ajoutez un cas qui exerce la différence ; multiplier des lignes identiques n’améliore pas cette preuve.

Atelier guidé : lire, expliquer et vérifier le code

WITH lab(v) AS (VALUES (1), (NULL), (3))
SELECT count(*) AS row_count, count(v) AS value_count, sum(v) AS total
FROM lab;

La fixture de l’atelier comporte trois lignes, dont une valeur nulle. count(*) compte les lignes et donne trois. count(v) compte ici les valeurs non nulles et donne deux. sum(v) additionne les valeurs présentes, un et trois, ce qui donne quatre. Les trois colonnes répondent donc à des questions différentes sur le même ensemble.

Remplacez ensuite toutes les valeurs par NULL et étudiez le contrat de chaque agrégat ; ne généralisez pas le résultat du premier jeu. Ajoutez un groupe supplémentaire et comparez WHERE avec HAVING. Cet exercice construit une méthode de lecture : déterminer les lignes conservées, comprendre la règle de NULL, puis calculer chaque agrégat selon sa définition. Le bon résultat ne doit pas être deviné depuis la taille de la table seule.

SQL et NULL : pourquoi NOT IN peut ne rien retourner

SELECT id FROM invoices
WHERE id NOT IN (SELECT invoice_id FROM blocked);

SQL utilise une logique à trois valeurs : TRUE, FALSE et UNKNOWN. WHERE conserve seulement TRUE. Avec l’ensemble interdit {2,NULL}, l’expression 2 NOT IN retourne FALSE ; pour 1 ou 3, l’absence de comparaison déterminée avec NULL empêche d’établir TRUE et le résultat est UNKNOWN.

Dans l’exercice, aucune des factures 1, 2 et 3 n’est donc retenue. La présence d’une seule valeur NULL dans la sous-requête change le raisonnement habituel d’une liste de valeurs toutes connues. Il ne s’agit ni d’un hasard du plan ni d’un problème de casse.

Pour une exclusion par relation, un NOT EXISTS corrélé exprime souvent plus clairement l’absence d’une ligne correspondante. Une autre solution peut filtrer les NULL si cela respecte le contrat. Testez explicitement sous-requête vide, valeur correspondante et présence de NULL, puis choisissez la forme dont la sémantique correspond à vos données.

Points de vigilance : Pour 1 et 3, la présence de NULL rend le prédicat inconnu, donc rejeté par WHERE. L’identifiant 2 est exclu par sa correspondance ; les autres ont un résultat inconnu. 2 produit FALSE ; 1 et 3 produisent UNKNOWN. Aucun résultat ne vaut TRUE. NULL est autorisé ici ; il change la logique à trois valeurs sans provoquer d’erreur.