Unter Unterabfragen oder Subqueries wird in SQL die Möglichkeit verstanden, Anweisungen zu schachteln. Das Ergebnis einer Unterabfrage ist in der Regel ein einzelner Wert oder eine Liste von Werten.
In welchen Abfragen und Klauseln Unterabfragen gestellt werden können, hängt in der Regel von dem eingesetzten Datenbanksystem und dem dort umgesetzten SQL-Standard ab. Die grundlegenden, hier erläuterten, Abfragen sollten jedoch in jedem gängigen DBMS funktionieren.
Sämtliche nun folgende Beispiele und Übungen beziehen sich auf die Relationen einer Datenbank versand:
artikel (anr, ↑mwst, bezeichnung, listenpreis, bestand, mindestbestand, verpackung, lagerplatz) bestellung (bnr, ↑knr, bestelldatum, lieferdatum, betrag) girokonto (kontonr, ↑knr, inhaber, blz) kunde (knr, status, name, strasse, plz, ort, letztewerbung, zahlungsart) mwstsatz (mwst, prozent, beschreibung) position (↑bnr, ↑anr, bestellmenge, liefermenge, gesamtpreis)
Versand-Datenbank mit Datensätzen (INNODB mit ref. Integrität): versand.zip
Unterabfragen, die eine Zelle liefern
Zwei einfach Abfragen könnten wie folgt aussehen und würden nacheinander ausgeführt werden, um an das gewünschte Resultat zu gelangen.
Knr des Inhabers von Konto 12346789 und Name des Kunden 101
SELECT knr
FROM girokonto
WHERE kontonr = 12346789 // Ergebnis 101
SELECT name
FROM kunde
WHERE knr = 101
Mittels einer Unterabfrage könnten die beiden Abfragen zusammengefasst werden. Dafür wird die erste Abfrage einfach nur geklammert in die zweite Abfrage eingesetzt. Sie ersetzt dort das Resultat, das sie selbst zuvor geliefert hat.
Name des Inhabers von Konto 12346789 (Subselect)
SELECT name
FROM kunde
WHERE knr = (SELECT knr
FROM girokonto
WHERE kontonr = 12346789)
Die noch relativ simple Anfrage hätte sogar fast noch besser mittels eines Joins gelöst werden können.
Name des Inhabers von Konto 12346789 (Join)
SELECT name
FROM kunde k NATURAL JOIN girokonto g
WHERE kontonr = 12346789
Ihre Vorteile können Unterabfragen eher an anderen Stellen ausspielen.
Es soll der Kunde mit der größten Bestellsumme gesucht werden. Die dazu nötige Aggregatsfunktion kann jedoch nicht in der WHERE-Klausel verwendet werden. Ihr Einsatz ist nur in HAVING und in SELECT möglich. Letzteres sogar nur, wenn auf kein weiteres Attribut neben dem Gruppierungsattribut projiziert wird.
Kunde mit größter Bestellsumme (Join, nicht funktionstüchtig)
SELECT name
FROM kunde AS k, bestellung AS b
WHERE k.knr = b.knr AND
b.betrag = MAX(b.betrag)
Kunde mit größter Bestellsumme (Subselect, funktionstüchtig)
SELECT name
FROM kunde k, bestellung b
WHERE k.knr = b.knr AND
b.betrag = (SELECT MAX(betrag)
FROM bestellung)
In der Haupt-, sowie in der Unterabfrage wird das Attribut ‚betrag‘ aus der Tabelle ‚bestellung‘ referenziert. Da in der Unterabfrage nicht qualifiziert wurde, welches der beiden ‚betrag‘-Attribute gemeint ist, bezieht sich MAX() auf das in geschachtelten Ebene referenzierte Attribut.
Wird mittels eines Alias in einer Unterabfrage ein Attribut einer darüberliegenden Abfrage qualifiziert, spricht man von korrelierten Unterabfragen, da diese nicht mehr unabhängig von der Hauptabfrage sind.
Bei den oben genannten Beispielen ergeben sich Probleme, sollte die Unterabfrage mehr als ein Ergebnis oder gar einen Nullwert zurück liefern. Man sollte daher darauf achten, die Unterabfrage so zu gestalten, dass nur eine einzelne Zelle als Ergebnis geliefert wird. Nur in diesem Fall wird ein Relationszeichen (=, <, <=, …) vor dem Subselect zugelassen.
Unterabfragen, die Spalten liefern
Um mit Unterabfragen umgehen zu können, die einen oder mehrere Tupel liefern, gibt es verschiedene Operatoren. Zu den wichtigsten gehören IN, ANY und EXISTS.
IN
Befindet sich ein bestimmtes Tupel in der Abfragerelation?
WHERE (A1, A2) IN (SELECT A1, A2, …
Alle Bestellungen des Artikels L001.
SELECT bnr, bestelldatum
FROM bestellung
WHERE bnr IN (SELECT bnr
FROM position
WHERE anr = 'L001')
ALL und ANY
Gilt eine Bedingung für eine, mehrere oder alle gelieferten Tupel der Unterabfrage?
WHERE A1 > ANY (SELECT A1…
Neben > sind hier alle gängigen Vergleichsoperatoren einsetzbar (<, >, !=, =). ANY überprüft, ob diese für einen oder mehrere Tupel des Ergebnisses wahr werden. Bei ALL muss die Bedingung für sämtliche Tupel erfüllt sein.
Welche Artikel sind billiger als alle Artikel aus Lagerplatz 7?
SELECT anr, listenpreis
FROM artikel
WHERE listenpreis < ALL (SELECT listenpreis
FROM artikel
WHERE lagerplatz = 7)
Wurde ein Artikel schon in Mengen bestellt, die über seinen Mindestbestand hinausgehen? (korrelierte Unterabfrage)
SELECT DISTINCT anr, bezeichnung, mindestbestand
FROM artikel a
WHERE mindestbestand < ANY (SELECT bestellmenge
FROM position
WHERE a.anr = anr)
EXISTS
Ist eine korrelierte Unterabfrage Null oder enthält sie Tupel?
WHERE EXISTS (SELECT * FROM … WHERE A.A1=A2 …
Für welche Kunden existiert keine Bestellung? (… auch über Differenz lösbar)
SELECT knr, name
FROM kunde k
WHERE NOT EXISTS (SELECT *
FROM bestellung AS b
WHERE b.knr = k.knr)
Gesucht sind die Namen der Kunden, die mehr als 1 mal bestellt haben (… auch über Gruppierung lösbar)
SELECT DISTINCT k1.name
FROM bestellung b1
NATURAL JOIN kunde k1
WHERE EXISTS (SELECT *
FROM bestellung b2
WHERE b1.knr = b2.knr AND
b1.bnr <> b2.bnr)
Übungsaufgaben
Stelle jeweils eine Abfrage über eine Unterabfrage, falls möglich auch über eine alternative Variante.
- Welche Beschreibung hat die Mehrwertsteuer, die für den Artikel mit der Nummer K001 gilt?
- Welche Artikel sind überdurchschnittlich teuer?
- Was ist die Abweichung vom durchschnittlichen Listenpreis?
- Zeige alle Kunden, die nach ihrer letzten Werbung nichts mehr bestellt haben!
- Es sollen alle Kunden, die ein Konto haben ausgegeben werden!
- Ermittle den jeweils teuersten Artikel eines Lagerplatzes
- Alle Artikel, die der Kunde Peter Stein jemals bestellt hat.
