SQL avancé

Introduction

Le chapitre précédent a posé les bases du SQL : créer des tables, insérer des données, écrire des sélections simples et des jointures. Celui-ci interroge plus finement, avec des conditions élaborées, des calculs statistiques et des requêtes qui s'appellent entre elles. Ce sont les écritures les plus employées en pratique, et toutes reposent sur un seul fait : le moteur n'exécute pas les clauses dans l'ordre où elles s'écrivent. La moitié des erreurs du chapitre en découlent, et c'est donc par là qu'il faut commencer.

Les exemples portent sur la base FILM du module : huit films, trois pays, sept réalisateurs, et une table ACTEURS de six lignes. Les blocs sql s'exécutent sur elle, et les nombres annoncés sont ceux qu'elle rend.

La grammaire d'une requête SQL

Chaque clause joue un rôle précis. Ce qui rend SQL particulier : l'ordre d'écriture n'est pas l'ordre d'exécution.

Le tableau ci-dessus se lit mieux en le voyant tourner. La figure suivante prend une requête qui emploie les sept clauses et montre la relation obtenue après chacune, dans l'ordre où le moteur les applique. Cliquer sur une étape affiche ce qu'elle produit.

SELECT Pays, COUNT(*) AS nbFROM FILMWHERE Budget > 10000000GROUP BY PaysHAVING COUNT(*) >= 2ORDER BY nb DESCLIMIT 2

Trois choses sautent aux yeux sur cette figure, et chacune corrige une idée fausse.

• FROM passe avant WHERE, donc le filtre ne s'applique jamais à une table déjà réduite : il réduit lui-même.
• GROUP BY transforme des lignes en groupes. Après lui, les lignes individuelles n'existent plus, et c'est pour cela que HAVING parle d'agrégats quand WHERE parle de valeurs.
• SELECT n'arrive qu'en cinquième position. Un alias qu'il définit est donc inconnu de WHERE, et connu de ORDER BY.

Filtres avec WHERE

Les films américains sortis entre 2000 et 2010, bornes comprises. Trois lignes : Inception (2010), Avatar et District 9 (2009). Le AND impose les deux conditions à la fois, et les cinq autres films de la base tombent sur l'une ou sur l'autre.

Plusieurs conditions se combinent avec AND, OR, BETWEEN, LIKE :

Trois lignes ici aussi, mais pour deux raisons différentes : MacGregor et Macdonald satisfont le motif du nom, Bullock satisfait l'intervalle de dates. Un OR réunit deux populations, et rien dans le résultat ne dit laquelle des deux conditions a retenu chaque ligne.

Opérateurs disponibles dans WHERE

Utilisation de jokers avec LIKE

Combiner plusieurs conditions avec des parenthèses

-- Tous les films américains ou britanniques sortis après 2010
WHERE (Pays = 'USA' OR Pays = 'UK') AND AnneeSortie > 2010

Les parenthèses sont essentielles, car AND est prioritaire sur OR. Sans elles, la condition se lirait « les films américains, ou les films britanniques sortis après 2010 », et laisserait passer les américains de toutes les années.

Ces opérateurs supposent tous qu'une valeur existe. La suite traite le cas où elle manque, qui échappe à la logique habituelle.

Le piège des valeurs NULL

-- ❌ Ne retourne RIEN, même si des lignes ont DateSortie = NULL
WHERE DateSortie = NULL

-- ✅ Correct
WHERE DateSortie IS NULL

-- ❌ N'inclut pas les lignes avec NULL dans DateSortie
WHERE DateSortie != '2020-01-01'

-- ✅ Inclut les NULL
WHERE DateSortie != '2020-01-01' OR DateSortie IS NULL

Fonctions d'agrégation

WHERE choisit des lignes ; il ne sait pas les résumer. Dès que la question porte sur un total, une moyenne ou un compte, il faut un autre outil.

Les fonctions d'agrégation calculent une valeur à partir de plusieurs lignes. Elles remplacent un paquet de lignes par un seul nombre, et c'est cette disparition des lignes individuelles qui explique tout le reste du chapitre.

Exemples sur l'ensemble de la table

Les trois requêtes rendent chacune une seule ligne, quel que soit le nombre de films concernés. Elles annoncent deux films sortis en 2009, un budget minimum de 9 500 000 et un maximum de 237 000 000, une moyenne américaine de 138 400 000 sur cinq films. Dans la troisième, le WHERE s'applique aux films avant la moyenne : c'est la moyenne des américains, et non la moyenne générale restreinte ensuite.

Regrouper avec GROUP BY

GROUP BY divise les lignes en groupes selon une colonne, et les fonctions d'agrégation s'appliquent à chaque groupe séparément.

Visualiser l'effet de GROUP BY

Table FILM, les huit lignes de la base :

Résultat :

Huit lignes au départ, trois à l'arrivée : chaque groupe produit une seule ligne. Ce qu'il faut regarder, c'est ce qui a disparu. Aucun titre ne figure plus dans le résultat, et la ligne USA ne permet plus de retrouver le budget d'Avatar. Après un GROUP BY, les lignes individuelles n'existent plus : seules subsistent la colonne de regroupement et les valeurs calculées sur le groupe.

Sept lignes : les huit films se répartissent sur sept années, 2009 en comptant deux. Changer la colonne de regroupement change donc le découpage, et rien d'autre.

Règle fondamentale du GROUP BY

Dans un SELECT avec GROUP BY, chaque colonne affichée doit être soit dans la clause GROUP BY, soit enveloppée dans une fonction d'agrégation (COUNT, AVG, etc.).

Sinon, la requête est invalide. Certains moteurs, dont SQLite et MySQL, l'acceptent pourtant et rendent une valeur quelconque prise dans le groupe. C'est le cas le plus désagréable : SELECT TitreFilm, Pays, MAX(Budget) FROM FILM GROUP BY Pays affiche un titre, et rien ne garantit que ce soit celui du film le plus cher.

Filtrer après regroupement : HAVING

HAVING s'applique après GROUP BY pour filtrer les groupes selon une condition sur les agrégats.

Deux lignes : France avec 2 films, USA avec 5. Le groupe Coree du Sud, qui n'en compte qu'un, a bien été formé puis écarté. HAVING ne supprime pas des films, il supprime des groupes déjà constitués.

Trois lignes sur sept années : 2009 (267 000 000), 2014 (165 000 000) et 2010 (160 000 000). L'année 2009 doit son total à deux films, les deux autres à un seul : un cumul ne dit rien du nombre de lignes qui le composent.

Combiner WHERE et HAVING

Les deux clauses peuvent coexister : elles n'agissent pas au même moment.

Une seule ligne : 2009, deux films, 133 500 000 de moyenne. Les deux clauses ne font pas le même travail. WHERE a écarté les trois films non américains, donc des lignes ; HAVING a écarté les années 2010, 2013 et 2014, qui ne comptent qu'un film américain chacune, donc des groupes.

Différence entre WHERE et HAVING

Le tableau se retient mal tant qu'on n'a pas vu les deux clauses donner des résultats différents. Voici le cas, sur la même base et autour du même seuil de 100 millions.

La première rend USA | 3, la seconde USA | 5. Même base, même pays, même seuil, et deux nombres.

En A, le filtre passe d'abord : les cinq films américains sont réduits à trois, et le compte porte sur ces trois-là. En B, les cinq films sont comptés, et le seuil sert seulement à décider si le groupe USA mérite d'apparaître. La France et la Corée du Sud disparaissent dans les deux cas, ce qui masque la différence si l'on ne regarde que la liste des pays.

Les deux se disent de la même façon en français : « les pays dont un film dépasse cent millions ». La question à se poser avant d'écrire est donc toujours la même : le seuil décrit-il les lignes à conserver, ou les groupes à conserver ?

Reste le cas où le moteur tranche à la place du lecteur. Exécuter le bloc suivant : la requête est refusée, et le message d'erreur est exactement la leçon à retenir.

La figure suivante montre où exactement la requête est refusée : à la deuxième étape, avant que le moindre regroupement n'ait eu lieu.

SELECT PaysFROM FILMWHERE AVG(Budget) > 10000000GROUP BY Pays

La bonne écriture regroupe d'abord, puis filtre sur le résultat du regroupement :

La même requête dans le pipeline. Le regroupement a lieu à l'étape 3, HAVING à l'étape 4 : la moyenne existe.

SELECT Pays, AVG(Budget) AS budget_moyenFROM FILMGROUP BY PaysHAVING AVG(Budget) > 10000000

Requêtes imbriquées (sous-requêtes)

Filtrer sur un nombre connu d'avance ne demande pas de sous-requête. Il en faut une dès que le seuil doit lui-même être calculé sur la base : « plus cher que la moyenne » n'est pas un nombre, c'est une requête.

Une sous-requête est un SELECT placé à l'intérieur d'une autre requête. Elle est exécutée en premier, et son résultat est utilisé par la requête externe.

La première question à se poser devant une sous-requête n'est pas ce qu'elle calcule, mais ce qu'elle rend. C'est cela qui décide de l'opérateur utilisable. Une erreur à ce niveau produit un refus du moteur ou, pire, un résultat plausible et faux.

Une sous-requête corrélée est à part : elle cite une colonne de la requête externe, et se réévalue donc pour chaque ligne examinée. Elle rend en général une valeur, mais une valeur différente à chaque fois.

Sous-requêtes scalaires

Les trois sous-requêtes rendent une valeur, et c'est pour cela que > et = sont utilisables. La première rend 90 362 500, la moyenne des huit budgets, et la requête externe garde quatre films au-dessus. La deuxième rend 237 000 000 et désigne Avatar. La troisième rend 2010, et le résultat se réduit à Inception lui-même, seul film de cette année dans la base.

La troisième est la plus fragile des trois : elle ne rend une valeur que parce qu'aucun autre film ne s'appelle Inception. Le jour où un remake entre dans la base, la sous-requête rend deux lignes et le moteur refuse la comparaison. Une sous-requête n'est scalaire que si quelque chose le garantit, un agrégat ou une contrainte d'unicité.

Sous-requêtes avec IN et NOT IN

Dès que la sous-requête rend plusieurs lignes, la comparaison devient un test d'appartenance.

La sous-requête rend une colonne de deux valeurs, 237 000 000 et 30 000 000. La requête externe rend donc deux films, Avatar et District 9, qui se trouvent être ceux de 2009. Avec un seul budget dans la liste, = (...) aurait suffi ; avec deux, seul IN fonctionne.

Sept films sur huit : tous sauf Inception, seul titre de 2010. NOT IN garde ce qui n'appartient pas à la colonne rendue.

Les deux requêtes suivantes ne diffèrent que par une valeur ajoutée à la liste.

La première rend cinq films. La seconde n'en rend aucun.

L'explication tient en une ligne : NOT IN demande au moteur d'affirmer que l'année diffère de chacune des valeurs de la liste. Pour 2009 et 2010, il sait répondre. Pour NULL, il ne sait pas : la valeur est inconnue, et affirmer qu'un nombre en diffère est indécidable. Une conjonction dont un terme est indécidable n'est jamais vraie, et aucune ligne ne passe.

Le défaut est redoutable parce qu'il ne se déclenche pas tout de suite. Une requête NOT IN juste pendant des mois devient silencieusement vide le jour où une seule saisie est laissée en blanc dans la colonne interrogée. IN n'a pas ce problème : il lui suffit d'une correspondance vraie, et le NULL y est simplement ignoré.

Requêtes avancées sans jointure

Les quatre requêtes qui suivent n'introduisent aucune notion nouvelle : elles empilent celles du chapitre. C'est l'empilement lui-même qui se travaille ici, et la lecture de l'intérieur vers l'extérieur qu'il impose.

Années ayant vu sortir au moins deux films

Une seule ligne, 2009 avec deux films. Sur une base de huit films répartis sur sept années, c'est le résultat attendu, et il vaut d'être prévu avant d'exécuter.

Top 3 des années avec le plus gros budget cumulé

2009, 2014 et 2010, avec 267, 165 et 160 millions. ORDER BY s'exécute avant LIMIT : c'est ce qui rend un « top 3 » possible. Dans l'ordre inverse, la coupe précéderait le tri et rendrait trois années quelconques.

Film le plus cher de chaque année (sous-requête corrélée)

La sous-requête est corrélée : f2.AnneeSortie = f1.AnneeSortie cite la ligne en cours de traitement, ce qui oblige à la réévaluer huit fois, une par film.

Le résultat compte sept lignes, une par année : chaque film y est le plus cher de la sienne. Le huitième film, District 9, manque à l'appel, écrasé par Avatar en 2009.

Ce que la corrélation change : en retirant la clause WHERE de la sous-requête, elle rendrait le maximum de toute la base, et la requête ne garderait plus qu'Avatar. C'est cette seule ligne qui transforme « le plus cher » en « le plus cher de son année ».

Réalisateurs ayant sorti plus de films que la moyenne

1. La sous-requête la plus interne rend une table de sept lignes : un compte de films par réalisateur. Elle est placée dans un FROM, ce qui est la seule position où une table est acceptée.
2. La sous-requête intermédiaire rend une valeur, la moyenne de ces sept comptes, soit 8 / 7 ≈ 1,14.
3. La requête externe compare chaque groupe à cette valeur. Un seul la dépasse : Nolan, avec deux films.

L'alias AS sous_stats n'est pas décoratif : une table placée dans un FROM doit porter un nom, faute de quoi la requête est refusée.

Opérateurs ALL et ANY

Une seule ligne, Avatar. Les six films seuls de leur année sont écartés, et pour une raison qui vaut d'être vue : leur sous-requête ne trouve aucun autre film et rend NULL, une comparaison avec NULL n'étant jamais vraie. Le résultat est correct, mais il le doit au traitement des NULL autant qu'au seuil.

Deux lignes, Interstellar et Avatar. La sous-requête rend 160 000 000, budget d'Inception, seul film de 2010 : être plus cher qu'au moins un film de 2010 revient ici à être plus cher que celui-là.

ALL vs ANY

ALL : la condition doit être vraie pour tous les éléments de la sous-requête, d'où la réécriture par MAX.
ANY (ou SOME) : vraie pour au moins un élément, d'où la réécriture par MIN.
= ANY (...) est équivalent à IN (...).
Ces deux opérateurs attendent une colonne, jamais une valeur seule.

Tri des résultats : ORDER BY

Tout ce qui précède décide de quelles lignes sortent. ORDER BY décide seulement de leur ordre, et s'exécute donc en dernier, ou presque.

Cinq lignes, d'Avatar à 237 millions jusqu'à District 9 à 30 millions. Le tri ne change ni le nombre de lignes ni leur contenu : sans ORDER BY, les mêmes cinq films sortiraient dans un ordre que rien ne garantit.

Trier par plusieurs colonnes

L'ordre de tri s'applique colonne par colonne : si deux films ont la même année, le budget départage. Les huit films sortent ici de 2001 à 2019, et la seule paire concernée est celle de 2009, où Avatar passe devant District 9. La seconde colonne de tri ne sert qu'aux égalités sur la première.

Trier après agrégation

L'alias nb_films défini dans le SELECT est utilisable tel quel dans le ORDER BY, puisque celui-ci s'exécute après. Le même alias dans un WHERE serait refusé : à ce moment-là, le SELECT n'a encore rien calculé. C'est la conséquence la plus concrète du tableau d'ordre d'exécution ouvrant le chapitre.

Trier les valeurs NULL

Le comportement par défaut des NULL varie selon le SGBD. Pour forcer leur position en MySQL :

-- Dates connues en premier, NULL en dernier
ORDER BY DateSortie IS NULL ASC, DateSortie ASC;

DateSortie IS NULL rend 0 (faux) pour les valeurs renseignées et 1 (vrai) pour les NULL. En triant ASC, les 0 passent devant les 1, donc les dates connues avant les absentes. L'astuce consiste à trier sur une expression calculée plutôt que sur la colonne elle-même.

La méthode

1. Composer la requête dans l'ordre d'exécution, pas dans l'ordre d'écriture : la table, puis le filtre de lignes, puis le regroupement, puis le filtre de groupes, et les colonnes affichées en dernier.
2. Classer la condition avant de choisir la clause. Si elle contient COUNT, AVG, SUM, MIN ou MAX, elle va dans HAVING ; sinon dans WHERE. En cas d'hésitation, se demander si le seuil décrit les lignes à garder ou les groupes à garder.
3. Contrôler chaque colonne du SELECT en présence d'un GROUP BY : elle doit figurer dans le regroupement ou dans un agrégat. Toute autre colonne rend une valeur arbitraire, sans avertissement.
4. Chercher les NULL de chaque colonne interrogée avant d'écrire une comparaison. IS NULL et IS NOT NULL sont les seuls tests qui les voient, et un seul NULL suffit à vider un NOT IN.
5. Exécuter chaque sous-requête seule, compter ses lignes, puis dire si elle rend une valeur, une colonne ou une table. L'opérateur suit : = pour une valeur, IN pour une colonne, EXISTS pour une table.
6. Repérer la corrélation : une sous-requête qui cite une colonne de la requête externe se recalcule à chaque ligne. C'est cette clause qui transforme « le maximum » en « le maximum de son groupe ».
7. Annoncer le nombre de lignes attendu avant d'exécuter, puis compter celles qui sortent. Un résultat vide, ou plus large que prévu, est le premier signal d'une clause mal placée.
8. Écrire un ORDER BY dès qu'un LIMIT apparaît. Sans tri, la coupe porte sur des lignes arbitraires, et le résultat reste plausible.

Synthèse

• L'ordre d'écriture n'est pas l'ordre d'exécution. Le moteur applique FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. Un alias défini dans SELECT est donc inconnu de WHERE et connu de ORDER BY.
• WHERE filtre des lignes, HAVING filtre des groupes. Sur la base du chapitre, WHERE Budget > 100000000 GROUP BY Pays compte 3 films américains. GROUP BY Pays HAVING MAX(Budget) > 100000000 en compte 5. Même base, même seuil, deux questions distinctes.
• Une fonction d'agrégation remplace un paquet de lignes par un nombre. Après un GROUP BY, les lignes individuelles n'existent plus, et c'est de là que découlent toutes les règles qui suivent.
• COUNT(*) compte les lignes, COUNT(col) compte les valeurs présentes. SUM et AVG ignorent aussi les NULL, ce qui change le dénominateur d'une moyenne sans que rien ne le signale.
• Toute colonne affichée à côté d'un agrégat doit figurer dans le GROUP BY. SQLite et MySQL acceptent le contraire et rendent une valeur quelconque du groupe. Le résultat paraît juste et ne l'est pas.
• NULL n'est pas une valeur mais une absence. Aucune comparaison avec lui n'est vraie, pas même NULL = NULL. Les deux seuls tests utilisables sont IS NULL et IS NOT NULL.
• Un seul NULL dans la liste d'un NOT IN vide le résultat, sans erreur ni avertissement. NOT EXISTS teste une existence plutôt qu'une égalité et résiste à ce défaut.
• Une sous-requête se classe par ce qu'elle rend : une valeur autorise =, <, > ; une colonne appelle IN, ALL ou ANY ; une table appelle EXISTS, ou une place dans le FROM avec un alias.
• Une sous-requête corrélée cite la requête externe et se réévalue à chaque ligne. C'est ce qui permet de comparer une ligne à son propre groupe, et ce qui coûte le plus cher.
• ORDER BY s'exécute avant LIMIT, et c'est ce qui rend un « top n » possible : trier d'abord, couper ensuite. Un LIMIT seul rend des lignes quelconques.

Quiz