Subqueries sous SQL

Mise en pratique des sous requêtes sous SQL

Comme vous le savez surement déjà, pour l'avoir expérimenté, les requêtes SQL peuvent devenir parfois très complexes. A tel point qu'il faut sortir des sentiers battus et emprunter une voie plus obscure : celle des subqueries, comprenez des instructions SELECT imbriquées entre elles.
Nous allons revenir dans articles sur les subqueries et la façon de les implémenter.

Données de travail

Nous allons travailler, pour cet article, avec une base originale disponible sur Gorenja.com. Il s'agit d'une base mySQL, nommée base_articles, qui regroupe, comme son nom l'indique, un certain nombre de produits. Sont également disponibles dans cette base, les catégories, matières, et grilles de prix associées à ces articles.

La base est disponible ici pour ceux qui sont intéressés. Voici ci-dessous le MLD associé à cette base :

Présentation des sous-requêtes

Les subqueries constituent une solution vers laquelle on hésite souvent à s'engager. Comme nous l'avons précisé en introduction, il s'agit la plupart du temps de SELECT imbriqués les uns dans les autres, parfois même corrélés entre eux, comme nous le verrons. Aussi, les instructions deviennent vite illisibles, difficiles à maintenir ou à déboguer, même pour leurs auteurs.
Cette solution existe néanmoins et permet de se sortir de bon nombre de situations difficiles. Il est donc bien d'en comprendre le principe.

Nous allons présenter les 4 types de sous-requêtes les plus couramment utilisées :
- les sous-requêtes sur filtre retournant une valeur,
- les sous-requêtes sur filtre retournant plusieurs enregistrements,
- les sous-requêtes dites derivées,
- les sous-requêtes corrélées.

Sous-requête à résutat unique

Tous les produits sont notés par les acheteurs et disposent d'une note entre 0 et 7. Pour ce premier exemple, nous allons afficher les produits dont la note est supérieure à la note moyenne de l'ensemble des produits de la base.

Nous pouvons facilement identifier, dans ce besoin, 2 requêtes distinctes : déterminer tout d'abord la note moyenne de l'ensemble des produits, puis sortir la liste de ceux dont la note est supérieure à cette valeur. Implémentons l'instruction SQL correspondante :

SELECT `reference`, `designation`, `note` FROM `articles` 
 WHERE `note` >= (SELECT AVG(`note`) FROM `articles`)

L'instruction SELECT AVG permet d'obtenir la note moyenne globale. Il s'agit d'une valeur unique que nous pouvons, par conséquent, venir comparer à la note de chaque article. Ici la sous-requête est incluse dans la clause WHERE mais nous pourrions tout aussi bien la positionner sur une clause HAVING par exemple. Ainsi, ci-dessous le même besoin, mais par catégorie cette fois :

SELECT `categories`.`categorie_id`, `description`, ROUND(AVG(`note`),1) AS 'note_moyenne' 
 FROM `categories`
 INNER JOIN `articles` ON `articles`.`categorie_id` = `categories`.`categorie_id`
 GROUP BY `articles`.`categorie_id`
 HAVING AVG(`note`) >= (SELECT AVG(`note`) FROM `articles`)

Nous avons ci-dessus l'instruction nous permettant d'éditer la liste des catégories de produits dont la note moyenne est supérieure à la note moyenne des produits de la base.
La sous-requête a été incluse dans une clause HAVING. De plus, puisque la note est dans la table des articles et la description de la catégorie, dans la table categories, nous avons établi une jointure entre ces deux tables. Vous noterez que, pour lever toute ambiguïté concernant le champ categorie_id, présent dans les 2 tables, nous avons précisé la table source. Nous avons enfin arrondi, via ROUND, la note moyenne de chaque catégorie à l'affichage puis lui avons attribué un alias (note_moyenne) afin d'éviter que la formule ne ressorte en en-tête de colonne du résultat.

Sous- requête à résutats multiples

Il est tout à fait possible d'utiliser des sous-requêtes avec des opérateurs autres que les comparaisons directes. L'exemple le plus parlant porte sur la clause IN. Ainsi, disons que nous voulons cette fois le produit le plus lourd parmi les produits dont la note de leur catégorie respective est supérieure à 4. Nous pourrions implémenter ce besoin ainsi :

SELECT `reference`, `designation`, `poids` FROM `articles` 
 WHERE `categorie_id` IN
  (SELECT `categorie_id` FROM `articles` GROUP BY `categorie_id` HAVING AVG(`note`) > 4)
 ORDER BY `poids` DESC LIMIT 1

Sous- requête dérivée

Plutôt que sous-requête dérivée, nous devrions parler de table dérivée. Ce cas correspond, en effet, à la possibilité de remplacer la référence à une table par une sous-requête. Celle-ci va être portée par la clause FROM ou au sein des jointures.

Pour illustrer nos propos, établissons la liste des articles en leur associant la note moyenne de leur catégorie respective :

SELECT reference, designation, note, noteMoyenne
 FROM articles
 INNER JOIN 
    (SELECT categorie_id,
         ROUND(AVG(note),1) AS noteMoyenne
      FROM articles
      GROUP BY categorie_id) as noteMoyCat
 ON articles.categorie_id = noteMoyCat.categorie_id

Comme vous le constatez dans l'instruction SQL ci-dessus, la sous-requête a été incluse dans la clause INNER JOIN. Cette sous-requête est donc l'identique d'une table sur laquelle nous effectuons d'ailleurs, dans le cadre de cet exemple, une jointure. Nous donnons à cette sous-requête un alias (ici noteMoyCat) nous permettant d'y faire référence dans les spécification de la jointure.

Sous- requête corrélée

Il s'agit d'une sous-requête qui présente une relation de corrélation avec la requête dans laquelle elle est incluse. Pour que ce soit plus clair, prenons un exemple : Nous voulons les articles dont le poids est supérieur au poids moyen de leur catégorie respective. Nous pouvons répondre à ce besoin via une sous-requête corrélée de la façon suivante :

SELECT `reference`, `designation`, `poids`
FROM `articles` AS ArtDetails
WHERE `poids` > (SELECT AVG(`poids`)
                FROM `articles` AS ArtAgreges
                WHERE ArtDetails.`categorie_id` = ArtAgreges.`categorie_id`)

Nous pouvons constater que la requête imbriquée fait référence à l'alias (ArtDetails) de la requête principale. Des alias, respectivement ArtDetails et ArtAgreges, ont été définis pour éviter toute ambiguïté dans la mesure où nous avons dans cette instruction deux instances d'une même table (articles).

Conclusion

Nous nous sommes contentés de requêtes relativement simples qui, pour la plupart, ne nécessitaient pas l'emploi de subqueries. Elles restent cependant encore lisibles. Néanmoins, ce type d'imbrication peut vite devenir une usine à gaz difficile à maintenir. Certains développeurs préfèrent donc tout simplement les éviter et optent plus volontiers, soit pour un découpage pur et simple de leurs requêtes, soit pour une autre solution, les Common Table Expression, qui feront l'objet d'un article à venir.

Crédits

Miniature de vectorjuice sur Freepik

Retrouvez dans la rubrique "Nos datasets" toutes les données dont vous aurez besoin pour tester et pratiquer !