Unterabfragen

Aus Informatik

Unter Unterabfragen oder Subqueries wird in SQL die Möglichkeit verstanden, Anweisungen zu schachteln.

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 folgendene Übungen beziehen sich auf die folgenden Relationen:

 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)
 
 phpMyAdmin Export mit Datensätzen; INNODB mit ref. Integrität: Datei:Versand.sql.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

  SELECT knr
  FROM girokonto
  WHERE kontonr = 12346789

Name des Kunden 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 AS k, girokonto AS g
  WHERE k.knr = g.knr
    AND kontonr = 12346789

Ihre Vorteile können Unterabfragen an anderen Stellen ausspielen.

Hier soll der Kunde mit der größten Bestellsume 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 projeziert 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 AS k, bestellung AS 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 qualifieziert, 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 wie ein Ergebnis oder gar einen Nullwert zurückliefern. 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 AS 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 AS 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 AS b1
  NATURAL JOIN kunde AS k1
  WHERE EXISTS(SELECT *
               FROM bestellung AS b2
               WHERE b1.knr = b2.knr AND
                     b1.bnr <> b2.bnr)

Vermischte Beispiele und Übungen

1. Welche Beschreibung hat die Mehrwertsteuer, die für den Artikel mit der Nummer K001 gilt?
2. Welche Artikel sind teurer als der Durchschnitt?
3. Was ist die Abweichung vom durchschnittlichen Listenpreis?
4. Zeige alle Kunden, die nach ihrer letzten Werbung nichts mehr bestellt haben!
5. Es sollen alle Kunden, die ein Konto haben ausgegeben werden!
6. Ermittle den jeweils teuersten Artikel eines Lagerplatzes
7. Alle Artikel, die der Kunde Peter Stein jemals bestellt hat.

  SELECT mwst, beschreibung
  FROM mwstsatz
  WHERE mwst = (SELECT mwst
                FROM artikel AS g
                WHERE anr = 'K001')
 
 
 
  SELECT anr, bezeichnung
  FROM artikel
  WHERE listenpreis >
    (SELECT AVG(listenpreis)
     FROM artikel)
 
 
 
  SELECT anr, listenpreis,
    listenpreis - (SELECT AVG(listenpreis)
                   FROM artikel)
  FROM artikel
 
 
 
  SELECT name
  FROM kunde AS k
  WHERE letztewerbung > ALL 
    (SELECT bestelldatum
     FROM bestellung
     WHERE k.knr = knr)
 
 
 
  SELECT name
  FROM kunde AS k
  WHERE knr in (SELECT knr
                FROM girokonto)
 
 
 
  SELECT lagerplatz, bezeichnung
  FROM artikel AS sup
  WHERE listenpreis = (SELECT MAX(listenpreis)
                       FROM artikel AS sub
                       WHERE sub.lagerplatz = sup.lagerplatz
                       GROUP BY lagerplatz)
 
 
 
  SELECT bezeichnung
  FROM artikel AS a
  WHERE a.anr IN (SELECT p.anr
                  FROM bestellung b
                    NATURAL JOIN position p
                    NATURAL JOIN kunde k
                  WHERE k.name = "Stein, Peter"
                 )