Introduction
Les sous-requêtes sont des requêtes à l'intérieur de requêtes. Elles sont parfois appelées requêtes imbriquées.
Sa durée de vie correspond au temps d'exécution de l'instruction dans laquelle elle est imbriquée.
Elles sont utiles pour obtenir une valeur en fonction de l'enregistrement en cours ou à partir d'un jeu de données.
Base de données
Il faut utiliser la base de données bd_cours8.sqlite.
Voici le DEA de la base de données à utiliser.
Valeur scalaire
Si la sous-requête retourne une ligne d'une seule valeur, la requête retourne une valeur scalaire. Une valeur scalaire peut remplacer toute colonne ou constante d'une commande.
Par exemple, la requête ci-dessous retourne une valeur scalaire.
Elle consiste à obtenir la moyenne note pour le cours 420BD1.
SELECT Round(Avg(Note),0) AS MoyenneFROM EtudiantsCoursWHERE CoursSigle = '420BD1'
Il serait intéressant de faire une requête qui permettrait d'afficher le DA de l'étudiant, sa note et la moyenne du cours.
Il est possible d'appeler la requête ci-dessus à l'intérieur d'un SELECT. Dans le jargon SQL, c'est un select imbriqué.
SELECT EtudiantDA, Note, (SELECT Round(Avg(Note),0) FROM EtudiantsCours WHERE CoursSigle = '420BD1') AS MoyenneFROM EtudiantsCoursWHERE CoursSigle = '420BD1';
Voici le résultat :
EtudiantDA Note Moyenne ---------- ---- ------- ALLM111111 87 77.0 PAUP222222 75 77.0 CHOG333333 85 77.0 TANP444444 84 77.0 DESS555555 56 77.0 FORP666666 76 77.0 TREM777777 NULL 77.0 TURM888888 NULL 77.0
Il est important que la sous-requête retourne toujours une valeur scalaire lorsqu'elle est utilisée dans un SELECT, car la requête ne fonctionnera pas ou elle donnera un résultat incorrect.
Utilisation de paramètres - requête corrélée
Maintenant, il faut afficher la note de chacun des cours et la moyenne du cours pour un étudiant.
Il est possible de passer en paramètre dans la sous-requête la valeur d'un champ de l'enregistrement parent. Il s'agit d'une requête corrélée.
Dans l'exemple ci-dessous, la sous-requête utilise la valeur du champ CoursSigle.
SELECT EtudiantDA, CoursSigle, Note, (SELECT Round(Avg(ec.Note),0) FROM EtudiantsCours ec WHERE ec.CoursSigle = EtudiantsCours.CoursSigle) AS MoyenneFROM EtudiantsCoursWHERE EtudiantDA = 'ALLM111111';
Voici le résultat :
EtudiantDA CoursSigle Note Moyenne---------- ---------- ---- -------ALLM111111 420BD1 87 77.0ALLM111111 420CN1 77 79.0
Pour comprendre le fonctionnement de la requête, elle sera décortiquée en étape.
Premièrement, il faut sortir les enregistrements dans la requête principale.
SELECT EtudiantDA, CoursSigle, Note FROM EtudiantsCoursWHERE EtudiantDA = 'ALLM111111'; /*EtudiantDA CoursSigle Note---------- ---------- ----ALLM111111 420BD1 87 ALLM111111 420CN1 77 */
Ensuite, il y a une itération pour chacune des lignes pour effectuer la sous-requête.
/*EtudiantDA CoursSigle Note---------- ---------- ----ALLM111111 420BD1 87 */SELECT Round(Avg(ec.Note),0)FROM EtudiantsCours ecWHERE ec.CoursSigle = '420BD1'/*77*//*EtudiantDA CoursSigle Note---------- ---------- ----ALLM111111 420CN1 77 */SELECT Round(Avg(ec.Note),0)FROM EtudiantsCours ecWHERE ec.CoursSigle = '420CN1'/*79*/
Finalement, il y a l'assemblage final.
/*EtudiantDA CoursSigle Note---------- ---------- ----ALLM111111 420BD1 87 ALLM111111 420CN1 77 */
Il est important de comprendre que la sous-requête sera exécutée pour chacun des enregistrements de la requête principale. Elle peut donc ralentir considérablement le système, et devrait être utilisée avec parcimonie et être remplacée si possible par une requête avec jointure.
Alias
Il est important d'utiliser les alias de table pour différencier la table de la requête parent et celle enfant lors de l'utilisation d'un champ parent en paramètre lors de l'utilisation d'une requête corrélée.
Si aucun alias n'est utilisé, il va avoir une ambiguïté entre l'enregistrement parent et enfant.
Voici la requête de l'exemple 3.1 sans l'utilisation d'un alias.
SELECT EtudiantDA, CoursSigle, Note, (SELECT Round(Avg(Note),0) FROM EtudiantsCours WHERE CoursSigle = CoursSigle) AS MoyenneFROM EtudiantsCoursWHERE EtudiantDA = 'ALLM111111';
Voici le résultat.
EtudiantDA CoursSigle Note Moyenne ---------- ---------- ---- ------- ALLM111111 420BD1 87 78.0 ALLM111111 420CN1 77 78.0
D'où provient le 78.0 ? Il s'agit de la moyenne de la table au complet. La condition CoursSigle = CoursSigle retourne toujours vraie, car elle valide le champ en cours avec lui-même. Il n'utilise pas la valeur de l'enregistrement parent.
Opérateur relationnel
Il est possible d'utiliser une sous-requête pour faire une comparaison dans la condition WHERE.
Par exemple, il faut afficher les étudiants qui ont une note supérieure à la moyenne pour le cours 420BD1.
SELECT Etudiants.Nom, EtudiantsCours.CoursSigle, EtudiantsCours.NoteFROM EtudiantsINNER JOIN EtudiantsCours ON Etudiants.DA = EtudiantsCours.EtudiantDAWHERE EtudiantsCours.Note > (SELECT Avg(Note) FROM EtudiantsCours ec WHERE ec.CoursSigle = '420BD1');
Voici le résultat :
Nom CoursSigle Note --------------- ---------- ---- Michel Allard 420BD1 87 Gino Chouinard 420BD1 85 Pierre Tanguay 420BD1 84 Pierre Tanguay 420CN1 99 Sylvie Deshaies 420CN1 78
Il est également possible de le faire pour tous les cours en utilisant une requête corrélée.
SELECT Etudiants.Nom, EtudiantsCours.CoursSigle, EtudiantsCours.NoteFROM EtudiantsINNER JOIN EtudiantsCours ON Etudiants.DA = EtudiantsCours.EtudiantDAWHERE EtudiantsCours.Note > (SELECT Avg(Note) FROM EtudiantsCours ec WHERE ec.CoursSigle = EtudiantsCours.CoursSigle);
Exemple sous-requête et GROUP BY
Il faut afficher le nombre de cours par étudiants.
Avec une sous-requête.
SELECT Etudiants.Nom, (SELECT Count(*) FROM EtudiantsCours ec WHERE ec.EtudiantDA = Etudiants.DA) AS NbCoursFROM EtudiantsORDER BY Nom;
Avec un GROUP BY.
SELECT Etudiants.Nom, Count(*) AS NbCoursFROM EtudiantsINNER JOIN EtudiantsCours ON Etudiants.DA = EtudiantsCours.EtudiantDAGROUP BY Etudiants.NomORDER BY Etudiants.Nom;
Liste de valeurs - 1 colonne
Si la sous-requête retourne plus d'une ligne, mais une seule colonne, elle peut remplacer une liste de valeurs.
Il est possible d'utiliser cette liste de valeur pour limiter l'application d'une requête.
La clause IN
La clause IN permet de vérifier si le champ a vérifié est dans la liste de vérification. Jusqu'à maintenant, le IN avait des valeurs statiques.
Il est possible d'avoir des valeurs dynamiques avec l'utilisation d'une sous-requête. Il est important que la sous-requête retourne uniquement une seule colonne.
Par exemple, il faut afficher la liste de tous les étudiants qui ont suivi le cours 420CN1.
SELECT NomFROM EtudiantsWHERE DA IN (SELECT DISTINCT EtudiantDa FROM EtudiantsCours WHERE CoursSigle = '420CN1');
Il serait possible d'avoir le même résultat avec une jointure.
SELECT Etudiants.NomFROM EtudiantsINNER JOIN EtudiantsCours ON Etudiants.da = EtudiantsCours.EtudiantDaWHERE EtudiantsCours.CoursSigle = '420CN1';
Une jointure est plus performante qu'une sous-requête. Il est préférable d'utiliser la jointure si c'est possible. La sous-requête est pratique lorsqu'il n'est pas possible de faire facilement une jointure.
Également, il peut être pratique pour faire un UPDATE ou un DELETE en fonction d'une autre table. En SQLite, il n'est pas toujours possible de faire des jointures pour un UPDATE et ce n'est pas possible pour un DELETE. L'utilisation de la sous-requête permet de contourner cette limitation.
Par exemple, revoyons l'exemple du cours 6 où il faut ajouter le préfixe (INFO) au nom du cours si au moins un étudiant du programme 420 le suit.
UPDATE CoursSET Nom = '(INFO)' || NomWHERE Sigle IN (SELECT DISTINCT EtudiantsCours.CoursSigle FROM Etudiants INNER JOIN EtudiantsCours ON Etudiants.da = EtudiantsCours.EtudiantDa WHERE Etudiants.ProgrammeId = 420);
La clause WITH
L'utilisation de sous-requête rend la lecture des requêtes plus difficile.
La clause WITH permet de mieux structurer une sous-requête. Elle consiste à mettre le résultat d'une requête dans une variable.
Cette variable est par la suite réutilisable dans la requête.
Par exemple, il faut afficher la liste de tous les étudiants qui ont suivi le cours 420CN1.
Voici la version standard.
SELECT NomFROM EtudiantsWHERE DA IN (SELECT DISTINCT EtudiantDa FROM EtudiantsCours WHERE CoursSigle = '420CN1');
Voici la version avec le WITH.
WITH liste_etudiants AS (SELECT DISTINCT EtudiantDa FROM EtudiantsCours WHERE CoursSigle = '420CN1') SELECT NomFROM EtudiantsWHERE DA IN liste_etudiants;
Afficher la liste des étudiants qui ont seulement 1 cours.
Il est possible de faire une sous-requête avec un GROUP BY.
WITH liste_etudiants AS (SELECT Etudiants.DA FROM Etudiants INNER JOIN EtudiantsCours ON Etudiants.DA = EtudiantsCours.EtudiantDA GROUP BY Etudiants.DA HAVING Count(*) = 1)SELECT NomFROM EtudiantsWHERE DA IN liste_etudiants;
La clause EXISTS et NOT EXISTS
Il est possible de vérifier si un enregistrement existe ou non dans une condition WHERE. Elle est utilisée avec une requête corrélée.
Par exemple, il faut afficher la liste des étudiants qui suivent le cours 420CN1.
SELECT NomFROM EtudiantsWHERE EXISTS (SELECT * FROM EtudiantsCours ec WHERE ec.EtudiantDA = Etudiants.DA AND ec.CoursSigle = '420CN1');
Il faut afficher la liste des étudiants qui n'ont pas le cours 420CN1.
SELECT NomFROM EtudiantsWHERE NOT EXISTS (SELECT * FROM EtudiantsCours ec WHERE ec.EtudiantDA = Etudiants.DA AND ec.CoursSigle = '420CN1');
Liste de valeurs - Plusieurs colonnes
Si la sous-requête retourne plusieurs lignes et plusieurs colonnes, elle est utilisable comme si elle était une table.
Il est possible de faire une jointure sur une variable provenant d'un WITH.
Il est possible d'utiliser un champ de la valeur
La variable devient l'équivalent d'une table.
WITH moyenne_par_cours AS (SELECT CoursSigle, Avg(Note) AS Moyenne FROM EtudiantsCours GROUP BY CoursSigle)SELECT Etudiants.Nom, EtudiantsCours.CoursSigle, EtudiantsCours.NoteFROM Etudiants INNER JOIN EtudiantsCours ON Etudiants.DA = EtudiantsCours.EtudiantDaINNER JOIN moyenne_par_cours ON EtudiantsCours.CoursSigle = moyenne_par_cours.CoursSigleWHERE Note > moyenne_par_cours.Moyenne;
Exercices
Numéro 1
Lister le nom, le DA et la note de tous les étudiants du cours 420CN1, et la moyenne de tous les étudiants pour ce cours.
Nom DA Note Moyenne --------------- ---------- ---- ------- Michel Allard ALLM111111 77 79.0 Paul Paul PAUP222222 75 79.0 Gino Chouinard CHOG333333 76 79.0 Pierre Tanguay TANP444444 99 79.0 Sylvie Deshaies DESS555555 78 79.0 Patricia Fortin FORP666666 69 79.0
Numéro 2
Lister le nom des étudiants, les cours qu'ils suivent, la note de chaque cours, ainsi que la moyenne générale de cet étudiant.
Il est possible de le faire à partir d'un WITH et effectuer dans la requête principale un select imbriqué avec la variable WITH.
Il est possible de le faire à partir d'un WITH et effectuer dans la requête principale une jointure avec la variable WITH.
Numéro 3
Lister tous les étudiants qui ont au moins une note > 90
Il y a 4 façons de résoudre ce problème. Les trouverez-vous toutes ?
Le résultat est : Pierre Tanguay
Solution 1 : utilisez un select imbriqué sur un EXISTS.
Truc : est-ce qu'il existe une note > 90 pour cet étudiant ?
Solution 2 : utilisez un select imbriqué sur un opérateur relationnel.
Truc : est-ce qu'il y a un Count() supérieur à 0 pour le nombre de
notes supérieur à 90 pour cet étudiant ?
Solution 3 : utilisez un select imbriqué sur un IN.
Solution 4 : utilisez une jointure interne uniquement (meilleure solution).
Version originale par François St-Hilaire
Produit à partir des notes de Benoit Desrosiers
Mise à jour 2023 par Pierre-Luc Boulanger