SQL Erste Schritte

Unterabfragen mit Korrelationsbezug

Eine Unterabfrage, die sich auf alle zuvor angegebenen Tabellen beziehen kann, wird als Unterabfrage mit Korrelationsbezug bezeichnet. Man spricht in diesem Zusammenhang auch davon, daß die Unterabfrage einen Korrelationsbezug auf eine Tabelle in der Hauptabfrage hat.

Im folgenden Beispiel wird eine Unterabfrage ohne Korrelationsbezug verwendet, um die Namen und Personalnummern der Mitarbeiter in der Abteilung 'A00' aufzulisten, deren Gehalt über dem Durchschnittsgehalt in dieser Abteilung liegt:

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

Diese Anweisung erzeugt das folgende Ergebnis:

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

Wenn für jede Abteilung das Durchschnittsgehalt ermittelt werden soll, muß die Unterabfrage für jede Abteilung einmal separat ausgewertet werden. Dies können Sie durch die Korrelationsfähigkeit von SQL erreichen. Sie ermöglicht das Schreiben einer Unterabfrage, die wiederholt ausgeführt wird, nämlich einmal für jede Zeile in der Tabelle, die in der übergeordneten Abfrage angegeben ist.

Im folgenden Beispiel wird eine Unterabfrage mit Korrelationsbezug verwendet, um alle Mitarbeiter aufzulisten, deren Gehalt über dem Durchschnittsgehalt ihrer Abteilung liegt:

 
     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

In dieser Abfrage wird die Unterabfrage einmal pro Abteilung ausgewertet. Das Ergebnis lautet:

     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     

Beim Schreiben einer Abfrage, die eine Unterabfrage mit Korrelationsbezug enthält, verwenden Sie dasselbe Basisformat wie bei einer herkömmlichen übergeordneten Abfrage mit Unterabfrage. Hierbei geben Sie jedoch in der Klausel FROM der übergeordneten Abfrage direkt nach dem Tabellennamen einen Korrelationsnamen an. Die Unterabfrage kann dann Spaltenbezüge enthalten, die durch den Korrelationsnamen qualifiziert werden. Wenn beispielsweise E1 ein Korrelationsname ist, steht E1.WORKDEPT für den Wert WORKDEPT der aktuellen Zeile der Tabelle in der übergeordneten Abfrage. Die Unterabfrage wird konzeptionsgemäß für jede Zeile der Tabelle in der übergeordneten Abfrage erneut ausgewertet.

Durch die Verwendung einer Unterabfrage mit Korrelationsbezug können Sie das System für sich arbeiten lassen und den Umfang des Codes reduzieren, den Sie in ihrer Anwendung schreiben müssen.

Korrelationsbezüge ohne Qualifikationsmerkmal sind in DB2 zulässig. Die Tabelle EMPLOYEE enthält beispielsweise eine Spalte namens LASTNAME, die Tabelle SALES jedoch enthält eine Spalte namens SALES_PERSON und keine Spalte mit dem Namen LASTNAME.

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

In diesem Beispiel überprüft das System die am weitesten untergeordnete Klausel FROM im Hinblick auf eine Spalte LASTNAME. Da keine gefunden wird, überprüft es anschließend die in der Hierarchie nächsthöhere Klausel FROM (die in diesem Fall die übergeordnete Klausel FROM ist). Auch wenn Korrelationsbezüge mit Qualifikationsmerkmal nicht immer erforderlich sind, empfiehlt sich doch ihre Verwendung, weil auf diese Weise die Lesbarkeit der Abfrage verbessert und sichergestellt wird, daß das beabsichtigte Ergebnis erzielt wird.

Implementieren einer Unterabfrage mit Korrelationsbezug

In welchen Situationen sollte eine Unterabfrage mit Korrelationsbezug verwendet werden? Manchmal ist die Verwendung einer Spaltenfunktion ein Anhaltspunkt.

Angenommen, Sie wollen die Mitarbeiter auflisten, deren Bildungsstufe über dem entsprechenden Durchschnittswert ihrer Abteilung liegt.

Zunächst müssen Sie die Elemente der SELECT-Liste bestimmen. Die Aufgabenstellung lautet "Mitarbeiter auflisten". Dies impliziert, daß die Spalte LASTNAME aus der Tabelle EMPLOYEE ausreichen sollte, um die Mitarbeiter eindeutig zu anzugeben. Die Aufgabenstellung beinhaltet außerdem die Bildungsstufe (EDLEVEL) und die Abteilungen der Mitarbeiter (WORKDEPT) als Bedingungen. Die Aufgabenstellung erfordert zwar nicht explizit das Anzeigen von Spalten, aber durch deren Aufnahme in die SELECT-Liste wird die Lösung anschaulicher. Nun kann ein Teil der Abfrage konstruiert werden:

     SELECT LASTNAME, WORKDEPT, EDLEVEL
        FROM EMPLOYEE

Anschließend wird eine Suchbedingung (Klausel WHERE) benötigt. Die Aufgabenstellung lautet "...deren Bildungsstufe über dem entsprechenden Durchschnittswert ihrer Abteilung liegt". Dies bedeutet, daß für jeden Mitarbeiter in der Tabelle die durchschnittliche Bildungsstufe in seiner Abteilung berechnet werden muß. Diese Anweisung entspricht der Beschreibung einer Unterabfrage mit Korrelationsbezug. Für jede Zeile wird ein bestimmtes unbekanntes Merkmal (die durchschnittliche Bildungsstufe in der Abteilung des aktuellen Mitarbeiters) berechnet. Für die Tabelle EMPLOYEE wird ein Korrelationsname benötigt:

     SELECT LASTNAME, WORKDEPT, EDLEVEL
        FROM EMPLOYEE E1

Die erforderliche Unterabfrage ist einfach. Sie berechnet die durchschnittliche Bildungsstufe für jede Abteilung. Die vollständige SQL-Anweisung lautet folgendermaßen:

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

Das Ergebnis lautet:

     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

Angenommen, Sie wollen anstelle der Abteilungsnummer eines Mitarbeiters den Namen der Abteilung auflisten. Die benötigten Informationen (DEPTNAME) befinden sich in einer separaten Tabelle (DEPARTMENT). Die übergeordnete Abfrage, die eine Korrelationsvariable definiert, kann auch eine Verknüpfungsabfrage sein (ausführliche Informationen finden Sie unter Auswählen von Daten aus mehreren Tabellen).

Wenn Sie in einer übergeordneten Abfrage Verknüpfungen verwenden, müssen Sie die zu verknüpfenden Tabellen in der Klausel FROM auflisten und den Korrelationsnamen neben den entsprechenden Tabellennamen stellen.

Um die Abfrage so zu modifizieren, daß anstelle der Abteilungsnummer der Name der Abteilung aufgelistet wird, ersetzen Sie in der SELECT-Liste die Angabe WORKDEPT durch DEPTNAME. Die Klausel FROM muß jetzt außerdem die Tabelle DEPARTMENT enthalten, und die Klausel WHERE muß die geeignete Verknüpfungsbedingung ausdrücken.

Die modifizierte Abfrage lautet wie folgt:

     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)

Diese Anweisung erzeugt das folgende Ergebnis:

	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

Aus den zuvor dargestellten Beispielen geht hervor, daß der in einer Unterabfrage verwendete Korrelationsname in der Klausel FROM einer Abfrage definiert werden muß, die die Unterabfrage mit Korrelationsbezug enthält. Dieser Einschluß kann jedoch unterschiedliche Verschachtelungsebenen mit sich bringen.

Angenommen, einige Abteilungen umfassen nur wenige Mitarbeiter und weisen daher eine unter Umständen irreführende durchschnittliche Bildungsstufe auf. In einem solchen Fall könnten Sie beschließen, nur Abteilungen mit mindestens fünf Mitarbeitern zu berücksichtigen, damit ein aussagekräftiger Wert für den Mitarbeitervergleich hinsichtlich der durchschnittlichen Bildungsstufe zur Verfügung steht. Daher müssen nun die Mitarbeiter mit einer höheren als der durchschnittlichen Bildungsstufe in ihrer Abteilung aufgelistet werden, wobei nur Abteilungen mit mindestens fünf Mitarbeitern zu berücksichtigen sind.

Die Aufgabenstellung impliziert eine weitere Unterabfrage, da für jeden Mitarbeiter in der übergeordneten Abfrage die Gesamtzahl der Mitarbeiter in seiner Abteilung ermittelt werden muß:

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

Nur dann, wenn die Anzahl größer-gleich 5 ist, soll ein Durchschnittswert berechnet werden:

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

Abschließend werden nur die Mitarbeiter in das Ergebnis aufgenommen, deren Bildungsstufe über dem Durchschnitt ihrer Abteilung liegt:

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

Diese Anweisung erzeugt das folgende Ergebnis:

     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


[ Seitenanfang | Vorherige Seite | Nächste Seite | Inhaltsverzeichnis | Index ]