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.
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