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