Kom godt i gang med SQL

Korreleret underforespørgsel

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.

Udfør korreleret underforespørgsel

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


[ Øverst på siden | Forrige side | Næste side | Indholdsfortegnelse | Stikordsregister ]