En underforespørgsel, der kan henvise til en af de ovenfor nævnte tabeller, kaldes en korreleret underforespørgsel. Man kan også sige, at underforespørgslen har en korreleret reference til en tabel i hovedforespørgslen.
Nedenstående eksempel bruger en ukorreleret underforespørgsel til at få vist personalenummer og navn på medarbejdere i afdeling 'A00', hvis løn er højere end afdelingsgennemsnittet:
SELECT EMPNO, LASTNAME FROM EMPLOYEE WHERE WORKDEPT = 'A00' AND SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE WHERE WORKDEPT = 'A00')
Resultatet er:
EMPNO LASTNAME ------ --------------- 000010 HAAS 000110 LUCCHESSI
Hvis du vil have vist gennemsnitslønnen for hver afdeling, skal underforespørgslen vurderes én gang for hver afdeling. Det kan du gøre vha. korrelationsegenskaben i SQL, som gør det muligt at skrive en underforespørgsel, som udføres gentagne gange, én for hver række i den tabel, der er angivet i den yderste forespørgsel.
I følgende eksempel bruges en korreleret underforespørgsel til at få vist alle medarbejdere, hvis løn er højere end gennemsnittet i afdelingen:
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
I denne forespørgsel vurderes underforespørgslen én gang for hver afdeling. Resultatet er:
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
Hvis du vil skrive en forespørgsel med en korreleret underforespørgsel, skal du bruge samme grundlæggende format som til en ydre forespørgsel med en underforespørgsel. I FROM-udtrykket i den ydre forespørgsel - lige efter tabelnavnet - skal du imidlertid anbringe korrelationsnavnet. Underforespørgslen kan så indeholde kolonnehenvisninger, der er kvalificeret vha. korrelationsnavnet. Hvis f.eks. E1 er et korrelationsnavn, så betyder E1.WORKDEPT værdien WORKDEPT i den aktuelle række i tabellen i den ydre forespørgsel. Underforespørgslen revurderes (principielt) for hver række i tabellen i den ydre forespørgsel.
Når du bruger en korreleret underforespørgsel, gør systemet arbejdet for dig, og du reducerer den mængde kode, der skal skrives i applikationen.
DB2 tillader også ukvalificerede korrelerede referencer. Eksempelvis har tabellen EMPLOYEE en kolonne, der hedder LASTNAME, og tabellen SALES har en kolonne, der hedder SALES_PERSON, men ingen kolonne, der hedder LASTNAME.
SELECT LASTNAME, FIRSTNME, COMM FROM EMPLOYEE WHERE 3 > (SELECT AVG(SALES) FROM SALES WHERE LASTNAME = SALES_PERSON)
I dette tilfælde søger systemet efter kolonnen LASTNAME i det inderste FROM-udtryk. Den findes ikke, hvorefter det næstinderste FROM-udtryk søges. I dette tilfælde er det lig med det ydre FROM-udtryk. Selv om det ikke altid er nødvendigt, anbefales det at kvalificere korrelerede referencer for at forbedre forespørgslens læsbarhed og sikre, at det ønskede resultat opnås.
Hvornår skal du bruge en korreleret underforespørgsel? Brugen af en beregningsfunktion kan være en indikator.
Forestil dig, at du vil have vist de medarbejdere, hvis uddannelsesniveau ligger højere end afdelingsgennemsnittet.
Først skal du finde frem til de kolonner, der skal vælges. Opgaven lyder "Vis medarbejdere". Det indebærer, at kolonnen LASTNAME i tabellen EMPLOYEE skulle være nok til at identificere medarbejdere entydigt. Opgaven indeholder også betingelserne uddannelsesniveau (EDLEVEL) og medarbejdernes afdelinger (WORKDEPT). Du skal ikke eksplicit have vist disse kolonner, men resultatet kan blive lettere at få overblik over, hvis du vælger dem. Du kan nu udforme en del af forespørgslen:
SELECT LASTNAME, WORKDEPT, EDLEVEL FROM EMPLOYEE
Dernæst skal du bruge et søgekriterium, dvs. et WHERE-udtryk. Opgaven lyder "...hvis uddannelsesniveau ligger højere end afdelingsgennemsnittet". Det betyder, at for hver medarbejder i tabellen skal det gennemsnitlige uddannelsesniveau i afdelingen udregnes. Denne sætning svarer til beskrivelsen af en korreleret underforespørgsel. Der beregnes en ukendt egenskab for hver række, nemlig det gennemsnitlige uddannelsesniveau for den aktuelle medarbejders afdeling. Du skal bruge et korrelationsnavn til tabellen EMPLOYEE:
SELECT LASTNAME, WORKDEPT, EDLEVEL FROM EMPLOYEE E1
Underforespørgslen er enkel. Den beregner det gennemsnitlige uddannelsesniveau for hver afdeling. Den fuldstændige SQL-sætning ser således ud:
SELECT LASTNAME, WORKDEPT, EDLEVEL FROM EMPLOYEE E1 WHERE EDLEVEL > (SELECT AVG(EDLEVEL) FROM EMPLOYEE E2 WHERE E2.WORKDEPT = E1.WORKDEPT)
Resultatet er:
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
Måske vil du hellere have vist medarbejderens afdelingsnavn end -nummer. De oplysninger, du skal bruge (DEPTNAME) ligger i en særskilt tabel (DEPARTMENT). Den ydre forespørgsel, som definerer en korrelationsvariabel, kan også være en sammenkædningsforespørgsel. Læs nærmere herom under Vælg data fra flere tabeller.
Når du bruger sammenkædning i en ydre forespørgsel, skal du angive de tabeller, der skal sammenkædes, i FROM-udtrykket og anbringe korrelationsnavnet ved siden af det relevante tabelnavn.
Hvis du vil ændre forespørgslen, så du får vist afdelingsnavnet i stedet for -nummeret, skal du udskifte WORKDEPT med DEPTNAME i listen over valgte kolonner. FROM-udtrykket skal nu også indeholde tabellen DEPARTMENT, og WHERE-udtrykket skal angive den relevante sammenkædningsbetingelse.
Sådan ser den ændrede forespørgsel ud:
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)
Resultatet er:
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
Ovenstående eksempler viser, at korrelationsnavnet i en underforespørgsel skal defineres i FROM-udtrykket i en forespørgsel, som indeholder den korrelerede underforespørgsel. Det kan medføre indfletning i flere lag.
Nogle afdelinger har kun få medarbejdere, og derfor kan det gennemsnitlige uddannelsesniveau være misvisende. Du kan derfor vedtage, at hvis det skal give mening at sammenligne en medarbejders uddannelsesniveau med afdelingsgennemsnittet, skal der være mindst fem ansatte i afdelingen. Opgaven lyder nu: vis de medarbejdere, hvis uddannelsesniveau ligger højere end afdelingsgennemsnittet, men se bort fra afdelinger med færre end fem medarbejdere.
Det kræver endnu en underforespørgsel, da der for hver medarbejder i den ydre forespørgsel skal beregnes, hvor mange ansatte der er i vedkommendes afdeling:
SELECT COUNT(*) FROM EMPLOYEE E3 WHERE E3.WORKDEPT = E1.WORKDEPT
Kun hvis antallet er større end eller lig med 5, beregnes et gennemsnit:
SELECT AVG(EDLEVEL) FROM EMPLOYEE E2 WHERE E2.WORKDEPT = E1.WORKDEPT AND 5 <= (SELECT COUNT(*) FROM EMPLOYEE E3 WHERE E3.WORKDEPT = E1.WORKDEPT)
Endelig skal vi kun have vist de medarbejdere, hvis uddannelse ligger over afdelingsgennemsnittet:
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))
Resultatet er:
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