Unterabfragen bzw. Subqueries - einfach, korreliert, anstelle einer Tabelle

• » Home
• »Sql-Tutorial
• » Subqueries / Unterabfragen

Vorbemerkung

Die folgenden Techniken verwenden kein neues Schlüsselwort, sondern ausschließlich die bisher bekannten grundlegenden Techniken mit SELECT, JOIN, WHERE und GROUP BY. Unterabfragen, für die meistens das Wort Subqueries genutzt wird, werden dann verwendet, falls das Ergebnis einer Abfrage / Query herangezogen wird, um eine neue Abfrage zu erstellen, mit welcher ein genauerer Einblick in die Datenstruktur gewonnen werden kann. Unterabfragen werden deshalb in WHERE-Klauseln und als Tabellen-Ersatz in JOIN-Klauseln genutzt.

Die einfachste Form - eine zurückgegebene Zelle

In der elementarsten Version einer Unterabfrage liefert diese eine Zelle zurück und ist unabhängig von der sie aufrufenden Abfrage. Sie wird verwendet, um Details zu einem Datensatz zu ermitteln.
Beispiel:

SELECT A.*
FROM ARTIKEL As A
WHERE A.A_PREIS =
	(SELECT MAX(A.A_PREIS)
	FROM ARTIKEL As A)

Die Unterabfrage ermittelt durch Maximumsbildung den teuersten Artikel. Dieser Wert wird zurückgegeben, die übergeordnete Abfrage sucht nach diesem Wert, den es mindestens einmal geben muß und gibt die zugehörigen Zeilen aus.
Die ganze Konstruktion mag für jemanden, der dies zum ersten Mal macht, verdoppelt aussehen: 'Suche die Zeilen, deren Wert in A_PREIS gleich dem Maximum aller Preise ist'. Erst muß dieses Maximum einmal ermittelt werden, dann ist ein zweiter Durchlauf notwendig, um die zu diesem Preis gehörenden Zeilen zu bestimmen.
Wird die Abfrage allerdings auf dem MS-Sql-Server durchgeführt und zuvor mit SET SHOWPLAN_TEXT ON die Analyseoption eingeschaltet, so wird sichtbar, daß der Optimierer einen veränderten Abfragetext vorgeschlagen hat:

Top(1)
  Filter(WHERE:(Artikel.A_Preis <> NULL)
    Sort(Order By:(Artikel.A_Preis DESC)
      Clustered Index Scan(Object:Artikel.pk_Artikel)

Es wird also eine Abfrage ausgeführt, die in etwa dem folgenden Sql-Befehl entspricht:

SELECT TOP 1 A.*
FROM ARTIKEL As A
ORDER BY A.A_PREIS DESC

Das heißt, daß der Optimierer eine Unterabfrage gegebenenfalls in eine andere Sql-Befehlsfolge auflöst und diese ausführt. Verwenden Sie die obige, umgeschriebene Abfrage jedoch nicht. Denn setzen Sie alle Artikel auf denselben Preis, so wird Ihnen die Version mit der Unterabfrage korrekt alle vier Datensätze ausgeben, die umgeschriebene Version liefert jedoch nur einen Datensatz.

Eine solche Unterabfrage wird also in Klammern gesetzt, muß genau eine Zelle zurückliefern und darf überall dort verwendet werden, wo Konstanten, etwa Zahlen, oder Spalten eingesetzt werden dürfen.

Unterabfragen, die eine Spalte zurückliefern

Jede Zeile in der Tabelle Umsatz stellt einen einzelnen Geschäftsvorfall dar, an dem ein Artikel und ein Verkäufer beteiligt ist, jeder Geschäftsvorfall liefert einen gewissen Brutto-Umsatz. Der durchschnittlichen Brutto-Umsatz kann mit der AVG-Funktion ermittelt werden. Dieses Kriterium läßt sich verwenden, um jene Geschäftsvorfälle zu ermitteln, welche hieran beteiligt waren. Interessant ist nun: Welche Artikel und welche Verkäufer sorgen für diese überdurchschnittlichen Geschäftsvorfälle? Offenbar ist nicht bekannt, wieviele Artikel und wieviele Verkäufer dies sind, also wird eine Liste zurückgegeben, wobei sich der Auswertende für die Klartext-Namen, nicht für die internen Primärschlüssel interessiert. Betrachten Sie zunächst die folgende Abfrage:

SELECT U.A_NR, U.DATUM, U.V_NR
FROM ARTIKEL As A INNER JOIN UMSATZ As U
ON A.A_NR = U.A_NR
WHERE A.A_PREIS * U.A_STUECK >
	(SELECT AVG(A.A_PREIS * U.A_STUECK)
	FROM ARTIKEL As A INNER JOIN UMSATZ As U
	ON A.A_NR = U.A_NR)

Ergebnis:

A_NR DATUM V_NR 22 24.06.1999 5016 11 24.06.1999 8413 22 25.06.1999 5016 13 24.06.1999 8413

An den überdurchschnittlichen Umsätzen sind drei von vier Artikeln sowie zwei von drei Verkäufern beteiligt. Werden deren Klartext-Informationen benötigt, so reduziert man die Ausgabespalten auf A_Nr bzw. V_Nr und verwendet den ganzen Ausdruck als Unterabfrage für die Tabelle mit den zugeordneten Stammdaten. Am Beispiel der Artikel:

SELECT A.*

FROM ARTIKEL As A

WHERE A.A_NR IN

	(SELECT U.A_NR

	FROM ARTIKEL As A INNER JOIN UMSATZ As U
		ON A.A_NR = U.A_NR

	WHERE A.A_PREIS * U.A_STUECK >

		(SELECT AVG(A.A_PREIS * U.A_STUECK)
		FROM ARTIKEL As A INNER JOIN UMSATZ As U
			ON A.A_NR = U.A_NR)
	)

Korrelierte Unterabfragen

Bislang waren die Unterabfragen immer unabhängig von der übergeordneten Abfrage. Man könnte sie eigenständig ausführen, ihr Ergebnis in eine neue Tabelle eintragen (Select ... Into) und die übergeordnete Abfrage mit dieser verknüpfen. Nun soll der Fall betrachtet werden, bei dem zu jeder Zeile der übergeordneten Tabelle eine Anweisung ausgeführt werden soll: Es soll ermittelt werden, mit welchem Artikel und an welchem Tag jeder Vertreter seinen höchsten Einzelumsatz gemacht hat. Bei wenigen Vertretern und vielen Einzelumsätzen liegt es nahe, diesen Befehl für jeden Vertreter getrennt auszuführen, also V_NR als Parameter @V_NR zu übergeben:

SELECT U.DATUM, U.A_NR, MAX(A.A_PREIS * U.A_STUECK) As Maximum
FROM UMSATZ As U INNER JOIN ARTIKEL As A
ON U.A_NR = A.A_NR
WHERE U.V_NR = @V_NR

Diese Abfrage funktioniert auch dann nicht, wenn man @V_NR durch eine Konstante, etwa 1215 ersetzt. Da eine Aggregatfunktion verwendet wird, muß nach den anderen Spalten gruppiert werden, dann erhält man jedoch zu jedem Vertreter so viele Zeilen, wie dieser verschiedene Artikel verkauft hat, anstatt daß man nur eine Zeile für einen Vertreter erhält.

Ein Umschreiben ergibt zunächst soviele Zeilen aus, wie dieser Vertreter Umsätze gemacht hat:

SELECT U.DATUM, U.A_NR, A.A_PREIS * U.A_STUECK As Maximum
FROM UMSATZ As U INNER JOIN ARTIKEL As A
ON U.A_NR = A.A_NR
WHERE U.V_NR = @V_NR

Also muß dieser Ausdruck noch ergänzt werden um eine filternde Unterabfrage, welche das Maximum für diesen Vertreter ermittelt:

SELECT U.DATUM, U.A_NR, A.A_PREIS * U.A_STUECK As Maximum

FROM UMSATZ As U INNER JOIN ARTIKEL As A
	ON U.A_NR = A.A_NR

WHERE (U.V_NR = @V_NR) AND

(A.A_PREIS * U.A_STUECK) =

	(SELECT MAX(X.A_PREIS * Y.A_STUECK)
	FROM ARTIKEL As X INNER JOIN UMSATZ As Y
	ON X.A_NR = Y.A_NR
	WHERE Y.V_NR = @V_NR)

Diesen Ausdruck müßte man für jeden Vertreter aufrufen und die Ergebnisse anschließend vereinigen. Tatsächlich kann man stattdessen auch eine einzige Anweisung verwenden. Denn sowohl die äußere als auch die innere Abfrage verwenden denselben Parameter @V_NR, dieser entspricht einer Zelle in der äußeren Abfrage und kann von dieser auf die innere Abfrage 'durchgereicht' werden. Die Unterabfrage erhält von der übergeordneten Abfrage die V_NR und ermittelt nur für diese den maximalen Umsatz.

SELECT V.V_NR, V.V_NAME, U.DATUM,
	A.A_NR, A.A_PREIS * U.A_STUECK As [Höchster Umsatz]

FROM (VERTRETER AS V INNER JOIN UMSATZ AS U
	ON V.V_NR = U.V_NR) INNER JOIN ARTIKEL As A
	ON U.A_NR = A.A_NR

WHERE (A.A_PREIS * U.A_STUECK) =

	(SELECT MAX(X.A_PREIS * Y.A_STUECK)
	FROM ARTIKEL As X INNER JOIN UMSATZ As Y
	ON X.A_NR = Y.A_NR
	WHERE Y.V_NR = V.V_NR)

Die äußere Abfrage ruft für jede V_NR einmal die Unterabfrage auf und belegt den Wert von V_NR mit dem aktuellen Wert. Das Ergebnis wird genutzt, um von den Zeilen, welche die äußere Abfrage liefert und welche diesen Vertreter betreffen, die Zeilen zu entfernen, deren Produkt aus A_PREIS und A_STUECK nicht dem Maximum entspricht.

Das Interessante an dieser Anweisung ist, daß sie auch dann die korrekten Ergebnisse liefert, falls man einen Artikel im Wert von 12.600,00, einen neuen Vertreter und einen Umsatz dieses Vertreters mit diesem Artikel (A_STUECK = 1) hinzufügt. Obwohl es bei dieser Datenstruktur zwei maximale Umsätze mit demselben Betrag gibt, die Abfrage auf die Gleichheit von Produkt und Maximum also zwei Werte zurückliefern müßte, wird pro Vertreter nur eine Zeile mit den ansonsten korrekten Daten ausgegeben. Die äußere Abfrage wird also, wie bei einer mit GROUP BY gruppierten Abfrage, nach V_NR gruppiert, für jede Gruppe wird einmal die Unterabfrage aufgerufen. Und nur auf diese Teilmenge der äußeren Abfrage wird das zusätzliche, durch die Unterabfrage bestimmte Kriterium angewandt.

Beim Entwickeln solcher Abfragen ist zu beachten, daß die Alias-Namen für die Tabelle in der untergeordneten Abfrage anders gewählt werden müssen als in der übergeordneten Abfrage. Enthält die Unterabfrage Ausdrücke, die nicht aufgelöst werden können, so wird in der übergeordneten Abfrage nach einem entsprechenden Ausdruck gesucht. Wird ein solcher gefunden, handelt es sich um eine korrelierende Unterabfrage.

Unterabfragen anstelle von Tabellen in einer JOIN-Klausel

Möchte man zu jedem Vertreter mit seinem Klarnamen den maximalen Umsatz ermitteln, so wurde dies bislang mit einer Doppelberechnung erledigt. Zunächst wurde für diesen Vertreter der Maximalumsatz ermittelt, anschließend wurden die Produkte A_PREIS * A_STUECK für diesen Vertreter mit dem errechneten Maximum verglichen, um die anderen Daten DATUM und A_NR zu bestimmen. Ist man nur an dem Maximalwert interessiert, erhält man diesen für alle Vertreter gemeinsam mit einer Abfrage, die mit MAX aggregiert und nach V_NR gruppiert. Diese kann in einem JOIN verwendet werden, um die Daten direkt auszugeben.

SELECT V.V_NR, V.V_NAME, X.MAXIMUM

FROM VERTRETER AS V INNER JOIN

	(SELECT U.V_NR, MAX(A.A_PREIS * U.A_STUECK) As MAXIMUM
	FROM ARTIKEL As A INNER JOIN UMSATZ As U
	ON A.A_NR = U.A_NR
	GROUP BY U.V_NR) AS X

ON V.V_NR = X.V_NR

Anstelle einer Tabelle wird eine eigenständige Abfrage notiert, geklammert und mit einem Alias identifiziert. Beachten Sie, daß in diesem Fall ein Alias für die Tabelle, die aus der Unterabfrage gebildet wird, Pflicht ist.