Initiation à SQL

Sous-requêtes corrélées

Une sous-requête autorisée à se référer à l'une quelconque des tables déjà mentionnées est appelée sous-requête corrélée. Il est également possible de dire que la sous-requête contient une référence corrélée à une table utilisée dans la requête principale.

Dans l'exemple suivant, une sous-requête non corrélée demande la liste des matricules et des noms des employés du service 'A00' dont le salaire est supérieur au salaire moyen du service.

 
     SELECT EMPNO, LASTNAME
        FROM EMPLOYEE
        WHERE WORKDEPT = 'A00'
          AND SALARY > (SELECT AVG(SALARY)
                           FROM EMPLOYEE
                           WHERE WORKDEPT = 'A00')

Cette instruction donne le résultat suivant :

	EMPNO  LASTNAME
	------ ---------------
	000010 HAAS
	000110 LUCCHESSI

Pour connaître le salaire moyen par service, il faut évaluer la sous-requête une fois par service. L'aptitude de SQL à établir des corrélations permet de le faire : il suffit d'écrire une sous-requête à exécuter de manière répétitive, une fois par ligne de la table identifiée dans la requête de niveau supérieur.

L'exemple suivant recense, via une sous-requête corrélée, tous les employés dont le salaire est supérieur au salaire moyen en vigueur dans leur service :

 
     SELECT E1.EMPNO, E1.LASTNAME, E1.WORKDEPT
        FROM EMPLOYEE E1
        WHERE SALARY > (SELECT AVG(SALARY) 
                            FROM EMPLOYEE  E2
                            WHERE E2.WORKDEPT = E1.WORKDEPT)
        ORDER BY E1.WORKDEPT

Dans cette requête, la sous-requête est évaluée une fois par service. Voici le résultat obtenu :

     EMPNO  LASTNAME        WORKDEPT
     ------ --------------- --------
     000010 HAAS            A00
     000110 LUCCHESSI       A00
     000030 KWAN            C01     
     000060 STERN           D11     
     000150 ADAMSON         D11
     000170 YOSHIMURA       D11     
     000200 BROWN           D11     
     000220 LUTZ            D11     
     000070 PULASKI         D21     
     000240 MARINO          D21
     000270 PEREZ           D21     
     000090 HENDERSON       E11
     000280 SCHNEIDER       E11     
     000100 SPENSER         E21     
     000330 LEE             E21
     000340 GOUNOT          E21     

Pour écrire une requête avec une sous-requête corrélée, utilisez le format de base d'une requête externe ordinaire avec sous-requête. Cependant, dans la clause FROM de la requête externe, juste après le nom de la table, placez un nom de corrélation. Ensuite, la sous-requête peut contenir des références qualifiées par le nom de corrélation. Par exemple, si E1 est un nom de corrélation, E1.WORKDEPT désigne la valeur WORKDEPT de la ligne en cours, dans la table de la requête externe. La sous-requête est ré-évaluée à chaque ligne de la table de la requête externe.

Lorsque vous utilisez une sous-requête corrélée, le système fait le travail pour vous. Ainsi, vous avez moins de code à écrire dans l'application.

DB2 autorise les références corrélées non qualifiées. Par exemple, une colonne de la table EMPLOYEE est appelée LASTNAME et une colonne de la table SALES est appelée SALES_PERSON, mais aucune colonne n'est appelée LASTNAME.

 
     SELECT LASTNAME, FIRSTNME, COMM 
        FROM EMPLOYEE
        WHERE 3 > (SELECT AVG(SALES)
                      FROM SALES
                      WHERE LASTNAME = SALES_PERSON)

Dans cet exemple, le système cherche une colonne LASTNAME dans la clause FROM la plus imbriquée. Comme il n'en trouve pas, il recherche dans la clause FROM de niveau immédiatement supérieur (qui, dans ce cas, est la clause FROM externe). Bien que cela ne soit pas toujours nécessaire, il est recommandé de qualifier des références corrélées pour rendre la requête plus lisible et obtenir le résultat attendu.

Implémentation d'une sous-requête corrélée

Quand utiliser une sous-requête corrélée ? Quelquefois, l'utilisation d'une fonction de colonne relève de l'énigme.

Par exemple, vous désirez obtenir la liste des employés ayant un niveau d'études supérieur au niveau d'études moyen en vigueur dans leur service.

Il faut d'abord déterminer les éléments de la liste de sélection. Le but de la requête est d'obtenir une "liste des employés". Ceci implique que LASTNAME de la table EMPLOYEE devrait être suffisant pour identifier les employés sans risque de doublon. La liste doit énumérer les employés en fonction de leur niveau d'études (EDLEVEL) et du service (WORKDEPT) auquel ils appartiennent. Bien que le but de la requête ne soit pas l'affichage des colonnes, le fait de les inclure dans la liste de sélection aidera à illustrer le résultat. Il devient possible de construire une partie de la requête :

     SELECT LASTNAME, WORKDEPT, EDLEVEL
        FROM EMPLOYEE

Ensuite, il faut prévoir une condition de recherche (clause WHERE). Voici la condition déclarée par l'instruction : "...qui ont un niveau d'études supérieur au niveau d'études moyen des employés de ce service". Ceci signifie qu'il faut calculer, pour chaque employé, le niveau d'études moyen du service auquel il appartient. Cette instruction illustre le cas où une sous-requête corrélée conviendrait très bien. Sur chaque ligne est calculée une propriété inconnue (en l'occurrence, le niveau d'études moyen du service auquel l'employé appartient). Il faut donner à la table EMPLOYEE un nom de corrélation :

     SELECT LASTNAME, WORKDEPT, EDLEVEL
        FROM EMPLOYEE E1

La sous-requête nécessaire est simple. Elle calcule le niveau d'études moyen par service. Voici l'instruction SQL complète :

     SELECT LASTNAME, WORKDEPT, EDLEVEL
        FROM EMPLOYEE E1
        WHERE EDLEVEL > (SELECT AVG(EDLEVEL)
                            FROM EMPLOYEE  E2
                            WHERE E2.WORKDEPT = E1.WORKDEPT)

Voici le résultat obtenu :

     LASTNAME        WORKDEPT EDLEVEL
     --------------- -------- -------
     HAAS            A00           18
     KWAN            C01           20
     PULASKI         D21           16
     HENDERSON       E11           16
     LUCCHESSI       A00           19
     PIANKA          D11           17
     SCOUTTEN        D11           17
     JONES           D11           17
     LUTZ            D11           18
     MARINO          D21           17
     JOHNSON         D21           16
     SCHNEIDER       E11           17
     MEHTA           E21           16
     GOUNOT          E21           16

Vous voulez obtenir les noms, et non les numéros, des services auxquels les employés appartiennent. Les informations à utiliser (DEPTNAME) se trouvent dans une autre table (DEPARTMENT). La requête externe définissant une variable de corrélation peut également être une requête de jointure (voir la section Sélection de données dans plusieurs tables, pour plus de détails).

Lorsque vous utilisez des jointures dans une requête externe, dans la clause FROM, indiquez les tables à joindre et placez le nom de corrélation à côté du nom de table approprié.

Pour que la requête recherche le nom du service, et non son numéro, remplacez WORKDEPT par DEPTNAME dans la liste de sélection. La clause FROM doit également inclure la table DEPARTMENT et la clause WHERE doit exprimer la condition de jointure appropriée.

Voici la requête modifiée :

     SELECT LASTNAME, DEPTNAME, EDLEVEL
        FROM EMPLOYEE E1, DEPARTMENT
        WHERE E1.WORKDEPT = DEPARTMENT.DEPTNO
        AND EDLEVEL > (SELECT AVG(EDLEVEL)
                          FROM EMPLOYEE E2
                          WHERE E2.WORKDEPT = E1.WORKDEPT)

Cette instruction donne le résultat suivant :

	LASTNAME        DEPTNAME                      EDLEVEL
	--------------- ----------------------------- -------
	HAAS            SPIFFY COMPUTER SERVICE DIV.       18
	LUCCHESSI       SPIFFY COMPUTER SERVICE DIV.       19
	KWAN            INFORMATION CENTER                 20
	PIANKA          MANUFACTURING SYSTEMS              17
	SCOUTTEN        MANUFACTURING SYSTEMS              17
	JONES           MANUFACTURING SYSTEMS              17
	LUTZ            MANUFACTURING SYSTEMS              18
	PULASKI         ADMINISTRATION SYSTEMS             16
	MARINO          ADMINISTRATION SYSTEMS             17
	JOHNSON         ADMINISTRATION SYSTEMS             16
	HENDERSON       OPERATIONS                         16
	SCHNEIDER       OPERATIONS                         17
	MEHTA           SOFTWARE SUPPORT                   16
	GOUNOT          SOFTWARE SUPPORT                   16

Les exemples ci-dessous montrent que le nom de corrélation utilisé dans une sous-requête doit être défini dans la clause FROM d'une requête contenant la sous-requête corrélée. Cependant, ceci peut impliquer plusieurs niveaux d'imbrication.

Si certains services ont un effectif restreint, le niveau d'études moyen obtenu risque de prêter à confusion. Pour que la comparaison entre le niveau d'études moyen d'un service et celui d'un employé donne un résultat significatif, vous pouvez préciser qu'il faut au moins cinq employés par service. Il nous faut alors recenser les employés dont le niveau d'études est supérieur à la moyenne de leur service, et ne prendre en compte que les services ayant un effectif supérieur ou égal à cinq employés.

Il faut prévoir une autre sous-requête afin de déterminer le nombre total d'employés du service auquel appartient chaque employé indiqué dans la requête externe :

     SELECT COUNT(*)
        FROM EMPLOYEE E3
        WHERE E3.WORKDEPT = E1.WORKDEPT

La moyenne ne sera calculée que si le résultat est égal ou supérieur à 5 :

     SELECT AVG(EDLEVEL)
        FROM EMPLOYEE E2
        WHERE E2.WORKDEPT = E1.WORKDEPT
        AND 5 <= (SELECT COUNT(*)
                     FROM EMPLOYEE  E3
                     WHERE E3.WORKDEPT = E1.WORKDEPT)

Enfin, seuls les employés ayant un niveau d'études supérieur à la moyenne du service seront inclus :

     SELECT LASTNAME, DEPTNAME, EDLEVEL
        FROM EMPLOYEE E1, DEPARTMENT
        WHERE E1.WORKDEPT = DEPARTMENT.DEPTNO
        AND EDLEVEL > 
        (SELECT AVG(EDLEVEL)
            FROM EMPLOYEE E2
            WHERE E2.WORKDEPT = E1.WORKDEPT
            AND 5 <=
            (SELECT COUNT(*)
                FROM EMPLOYEE E3
                WHERE E3.WORKDEPT = E1.WORKDEPT))

Cette instruction donne le résultat suivant :

     LASTNAME        DEPTNAME                      EDLEVEL
     --------------- ----------------------------- -------
     PIANKA          MANUFACTURING SYSTEMS              17
     SCOUTTEN        MANUFACTURING SYSTEMS              17
     JONES           MANUFACTURING SYSTEMS              17
     LUTZ            MANUFACTURING SYSTEMS              18
     PULASKI         ADMINISTRATION SYSTEMS             16
     MARINO          ADMINISTRATION SYSTEMS             17
     JOHNSON         ADMINISTRATION SYSTEMS             16
     HENDERSON       OPERATIONS                         16
     SCHNEIDER       OPERATIONS                         17


[ Début de page | Page précédente | Page suivante | Table des matières | Index ]