Einführung in SQL: Unterabfragen

Aus Wikibooks

Inhaltsverzeichnis: Abfragen für Fortgeschrittene

Immer wieder werden zur Durchführung einer Abfrage oder eines anderen Befehls Informationen benötigt, die zuerst durch eine eigene Abfrage geholt werden müssen. Diese "Unterabfragen" werden in diesem Kapitel behandelt.

Wenn eine Abfrage als Ergebnis einen einzelnen Wert liefert, kann sie anstelle eines Wertes benutzt werden.
Wenn eine Abfrage als Ergebnis eine Liste von Werten liefert, kann sie anstelle einer solchen Liste benutzt werden.
Wenn eine Abfrage eine Ergebnismenge, also in Form einer Tabelle liefert, kann sie anstelle einer Tabelle benutzt werden.

Bitte beachten Sie, dass die Unterabfrage immer in Klammern gesetzt wird. Auch wenn ein DBMS das nicht verlangen sollte, ist es wegen der Übersichtlichkeit dringend zu empfehlen.

Allgemeiner Hinweis: Unterabfragen arbeiten in vielen Fällen langsamer als andere Verfahren. Soweit es irgend möglich ist, versuchen Sie, eine der Varianten bei Arbeiten mit JOIN vorzuziehen.

Inhaltsverzeichnis

1 Ergebnis als einzelner Wert
- 1.1 Ergebnisse einfacher Abfragen
- 1.2 Ergebnisse von Spaltenfunktionen
2 Ergebnis als Liste mehrerer Werte
3 Ergebnis in Form einer Tabelle
4 Verwendung bei Befehlen zum Speichern
5 Zusammenfassung
6 Übungen

[Bearbeiten] Ergebnis als einzelner Wert

[Bearbeiten] Ergebnisse einfacher Abfragen

Eine Situation, die es immer wieder gibt, ist diejenige, dass der Anwender den Namen eines Objekts kennt, aber für Abfragen die ID benötigt. Diese holt er sich mit einer Unterabfrage und übergibt das Ergebnis an die eigentliche Abfrage.

Nenne alle Mitarbeiter der Abteilung "Schadensabwicklung".

Lösung Teil 1: Hole die ID dieser Abteilung anhand des Namens.
Lösung Teil 2: Hole die Mitarbeiter dieser Abteilung unter Benutzung der gefundenen ID.

SQL-Quelltext:

SELECT Personalnummer, Name, Vorname
  FROM Mitarbeiter
 WHERE Abteilung_ID = 
       ( SELECT ID FROM Abteilung
          WHERE Kuerzel = 'ScAb' );

SQL-Ausgabe:

 PERSONALNUMMER  NAME       VORNAME
 80001           Schindler  Christina
 80002           Aliman     Zafer
 80003           Langer     Norbert
 80004           Kolic      Ivana

Teil 1 der Lösung ist der SELECT-Befehl innerhalb der Klammern. Das Ergebnis ist eine einzelne ID. Diese kann anstelle einer konkreten Zahl in die WHERE-Klausel der eigentlichen Abfrage übernommen werden. Das Wörtchen "Diese" hat in diesem Fall sprachlich eine doppelte Bedeutung: zum einen steht es für die Unterabfrage, zum anderen für die ID als Ergebnis.

Hinweis: Dies funktioniert nur deshalb auf einfache Weise, weil die Kurzbezeichnung faktisch eindeutig ist und deshalb genau eine ID geliefert wird. Wenn wir uns darauf nicht verlassen wollen oder können oder wenn das DBMS "empfindlich" ist und die Eindeutigkeit des Ergebnisses nicht erkennt, können wir daraus bewusst einen einzelnen Wert machen:

SQL-Quelltext:

SELECT Personalnummer, Name, Vorname
  FROM Mitarbeiter
 WHERE Abteilung_ID = 
       ( SELECT MAX(ID) FROM Abteilung
          WHERE Kuerzel = 'ScAb' );

Eine solche Aufgabe kann auch zweimal dieselbe Tabelle benutzen.

Nenne alle anderen Mitarbeiter der Abteilung, deren Leiterin Christina Schatzing ist.

Lösung Teil 1: Hole die Abteilung_ID, die bei Christina Schatzing registriert ist.
Lösung Teil 2: Hole die Mitarbeiter dieser Abteilung unter Benutzung der gefundenen Abteilung_ID.

Zur Sicherheit prüfen wir auch die Eigenschaft Ist_Leiter.

SQL-Quelltext:

SELECT Personalnummer, Name, Vorname
  FROM Mitarbeiter
 WHERE ( Abteilung_ID = 
         ( SELECT Abteilung_ID
             FROM Mitarbeiter
            WHERE ( Name = 'Schindler' )
              AND ( Vorname = 'Christina' )
              AND ( Ist_Leiter = 'J' ) 
         )
 ) AND ( Ist_Leiter = 'N' );

SQL-Ausgabe:

 PERSONALNUMMER  NAME    VORNAME
 80002           Aliman  Zafer
 80003           Langer  Norbert
 80004           Kolic   Ivana

Hoch zum Seitenanfang

[Bearbeiten] Ergebnisse von Spaltenfunktionen

Häufig werden Ergebnisse von Aggregatfunktionen als Teil der WHERE-Klausel benötigt.

Nenne alle Schadensfälle mit unterdurchschnittlicher Schadenshöhe.

Lösung Teil 1: Berechne den Durchschnitt der Schadenshöhe aller Schadensfälle.
Lösung Teil 2: Übernimm dieses Ergebnis als Vergleichswert in die Hauptabfrage.

SQL-Quelltext:

SELECT ID, Datum, Ort, Schadenshoehe
  FROM Schadensfall
 WHERE Schadenshoehe < 
       ( SELECT AVG(Schadenshoehe) FROM Schadensfall );

SQL-Ausgabe:

 ID  DATUM        ORT                                  SCHADENSHOEHE
  1  03.02.2007   Recklinghausen, Bergknappenstr. 144       1.234,50
  2  11.07.2007   Haltern, Hauptstr. 46                     2.066,00
  4  27.05.2008   Recklinghausen, Südgrabenstr. 23          1.438,75
  5  05.10.2008   Dorsten, Oberhausener Str. 18             1.983,00
  7  21.06.2009   Recklinghausen, Bergknappenstr. 144         865,00

Bestimme alle Schadensfälle, die von der durchschnittlichen Schadenshöhe eines Jahres maximal 300 € abweichen.

Lösung Teil 1: Bestimme den Durchschnitt aller Schadensfälle innerhalb eines Jahres.
Lösung Teil 2: Hole alle Schadensfälle, deren Schadenshöhe im betreffenden Jahr innerhalb des Bereichs "Durchschnitt plus/minus 300" liegen.

SQL-Quelltext:

SELECT sf.ID, sf.Datum, sf.Schadenshoehe, EXTRACT(YEAR FROM sf.Datum) AS Jahr
  FROM Schadensfall sf
 WHERE ABS(Schadenshoehe - ( SELECT AVG(sf2.Schadenshoehe)
                               FROM Schadensfall sf2
                              WHERE EXTRACT(YEAR FROM sf2.Datum) = EXTRACT(YEAR FROM sf.Datum)
                           )
          ) <= 300;

SQL-Ausgabe:

 ID  DATUM       SCHADENSHOEHE   JAHR
  2  11.07.2007       2.066,00   2007
  4  27.05.2008       1.438,75   2008
  5  05.10.2008       1.983,00   2008
  8  01.08.2009       2.471,50   2009

Zuerst muss für jeden einzelnen Schadensfall aus sf das Jahr bestimmt werden. In der Unterabfrage, die in der inneren Klammer steht, wird für alle Schadensfälle des betreffenden Jahres die durchschnittliche Schadenshöhe bestimmt. Dieser Wert wird mit der aktuellen Schadenshöhe verglichen; dazu wird die ABS-Funktion benutzt, also der Absolut-Betrag der Differenz der beiden Werte.

	Dies ist ein Paradebeispiel dafür, wie Unterabfragen nicht benutzt werden sollen.

Für jeden einzelnen Datensatz muss in der WHERE-Bedingung eine neue Unterabfrage gestartet werden – mit eigener WHERE-Klausel und Durchschnittsberechnung. Viel besser ist eine der JOIN-Varianten oder eine der Lösungen im Abschnitt Ergebnis in Form einer Tabelle.

Hoch zum Seitenanfang

[Bearbeiten] Ergebnis als Liste mehrerer Werte

Das Ergebnis einer Abfrage kann als Filter für die eigentliche Abfrage benutzt werden.

Bestimme alle Fahrzeuge eines bestimmten Herstellers.

Lösung Teil 1: Hole die ID des gewünschten Herstellers.
Lösung Teil 2: Hole alle IDs der Tabelle Fahrzeugtyp, die zu dieser Hersteller-ID gehören.
Lösung Teil 3: Hole alle Fahrzeuge, die zu dieser Liste von Fahrzeugtypen-IDs gehören.

SQL-Quelltext:

SELECT ID, Kennzeichen, Fahrzeugtyp_ID AS TypID
  FROM Fahrzeug
 WHERE Fahrzeugtyp_ID IN
       ( SELECT ID
           FROM Fahrzeugtyp
          WHERE Hersteller_ID =
                ( SELECT ID
                    FROM Fahrzeughersteller
                   WHERE Name = 'Volkswagen' ) );

SQL-Ausgabe:

 ID  KENNZEICHEN    TYPID
 22  BOR-PQ 567         3
 23  BOR-RS 890         2

Teil 1 der Lösung ist die "innere" Klammer; dies ist das gleiche Verfahren wie im Abschnitt Ergebnisse einfacher Abfragen. Teil 2 der Lösung ist die "äußere" Klammer; Ergebnis ist eine Liste von IDs der Tabelle Fahrzeugtyp, die als Werte für den Vergleich der WHERE-IN-Klausel verwendet werden.

Wenn im Ergebnis der Fahrzeugtyp als Text angezeigt werden soll, muss die Abfrage erweitert werden, weil die Bezeichnung in der Tabelle Fahrzeugtyp zu finden ist. Dafür kann diese Tabelle ein zweites Mal benutzt werden, wie es unter Mehrere Tabellen erläutert wird; es ist auch ein Verfahren möglich, wie es unten im Abschnitt Ergebnis in Form einer Tabelle erläutert wird.

Das obige Beispiel mit der durchschnittlichen Schadenshöhe kann auch so gebaut werden:

Gib alle Informationen zu den Schadensfällen des Jahres 2008, die von der durchschnittlichen Schadenshöhe 2008 maximal 300 € abweichen.

Lösung Teil 1: Bestimme den Durchschnitt aller Schadensfälle innerhalb von 2008.
Lösung Teil 2: Hole alle IDs von Schadensfällen, deren Schadenshöhe innerhalb des Bereichs "Durchschnitt plus/minus 300" liegen.
Lösung Teil 3: Hole alle anderen Informationen zu diesen IDs.

SQL-Quelltext:

SELECT *
  FROM Schadensfall
 WHERE ID IN ( SELECT ID
                 FROM Schadensfall
                WHERE ( ABS(Schadenshoehe - ( SELECT AVG(sf2.Schadenshoehe)
                                                FROM Schadensfall sf2
                                               WHERE EXTRACT(YEAR FROM sf2.Datum) = 2008
                                            )
                           ) <= 300 )
                  AND ( EXTRACT(YEAR FROM Datum) = 2008 )
             );

SQL-Ausgabe:

 ID  DATUM       ORT                   BESCHREIBUNG           SCHADENSHOEHE   VERLETZTE  MIT-ID
  4  27.05.2008  Recklinghausen,       Fremdes parkendes           1.438,75       N          16
                 Südgrabenstr. 23      Auto gestreift     
  5  05.10.2008  Dorsten,              beim Ausparken hat          1.983,00       N          14
                 Oberhausener Str. 18  ein fremder Wagen die
                                       Vorfahrt missachtet

Diese Situation wird dadurch einfacher, dass das Jahr 2008 fest vorgegeben ist. Die innerste Klammer bestimmt als Teil 1 der Lösung die durchschnittliche Schadenshöhe dieses Jahres. Die nächste Klammer vergleicht diesen Wert (absolut gesehen) mit der Schadenshöhe eines jeden einzelnen Schadensfalls im Jahr 2008; alle "passenden" IDs werden in der äußersten Klammer als Teil 2 der Lösung in einer weiteren Unterabfrage zusammengestellt. Diese Liste liefert die Werte für die eigentliche Abfrage.

Ganz offensichtlich ist dieses Beispiel konstruiert: Weil immer dieselbe Tabelle verwendet wird, kann die WHERE-Klausel der Unterabfrage in der äußersten Klammer auch als WHERE-Klausel der Hauptabfrage verwendet werden (die Einrückungen habe ich zum besseren Vergleich nicht geändert):

SQL-Quelltext:

SELECT *
  FROM Schadensfall
                WHERE ( ABS(Schadenshoehe - ( SELECT AVG(sf2.Schadenshoehe)
                                                FROM Schadensfall sf2
                                               WHERE EXTRACT(YEAR FROM sf2.Datum) = 2008
                                            )
                           ) <= 300 )
                  AND ( EXTRACT(YEAR FROM Datum) = 2008 )
             ;

Hoch zum Seitenanfang

[Bearbeiten] Ergebnis in Form einer Tabelle

Das Ergebnis einer Abfrage kann innerhalb der eigentlichen Abfrage überall dort eingesetzt werden, wo eine Tabelle vorgesehen ist. Die Struktur dieser Situation sieht so aus:

 SELECT <spaltenliste>
   FROM <haupttabelle>,
        ( SELECT <spaltenliste>
            FROM <zusatztabellen>
           <weitere Bestandteile der Unterabfrage>
        ) <name>
 <weitere Bestandteile der Hauptabfrage>

Eine solche Unterabfrage kann grundsätzlich alle SELECT-Bestandteile enthalten. Bitte beachten Sie dabei:

Nach der schließenden Klammer muss ein Name als Tabellen-Alias angegeben werden, der als Ergebnistabelle in der Hauptabfrage verwendet wird.
Die Unterabfrage kann eine oder mehrere Tabellen umfassen – wie jede andere Abfrage auch.
In der Spaltenliste sollte jeweils ein Name als Spalten-Alias vor allem dann vorgesehen werden, wenn mehrere Tabellen verknüpft werden; andernfalls erzeugt SQL selbständig Namen wie "ID", "ID1", die man nicht ohne Weiteres versteht.
ORDER BY kann nicht sinnvoll genutzt werden, weil das Ergebnis der Unterabfrage als Tabelle behandelt wird und mit der Haupttabelle oder einer anderen Tabelle verknüpft wird, wodurch eine Sortierung sowieso verlorenginge.

Wie gesagt: Eine solche Unterabfrage kann überall stehen, wo eine Tabelle vorgesehen ist. In der vorstehenden Syntax steht sie nur beispielhaft innerhalb der FROM-Klausel.

Überarbeiten wir jetzt, wie oben angekündigt, einige Beispiele. Dabei wird die Unterabfrage, die bisher zur WHERE-Klausel gehörte, als Tabelle in die FROM-Klausel eingebaut.

Bestimme alle Schadensfälle, die von der durchschnittlichen Schadenshöhe eines Jahres maximal 300 € abweichen.

Lösung Teil 1: Stelle alle Jahre zusammen und bestimme den Durchschnitt aller Schadensfälle innerhalb eines Jahres.
Lösung Teil 2: Hole alle Schadensfälle, deren Schadenshöhe im jeweiligen Jahr innerhalb des Bereichs "Durchschnitt plus/minus 300" liegen.

SQL-Quelltext:

SELECT sf.ID, sf.Datum, sf.Schadenshoehe, temp.Jahr, temp.Durchschnitt
  FROM Schadensfall sf,
       ( SELECT AVG(sf2.Schadenshoehe) AS Durchschnitt,
                EXTRACT(YEAR FROM sf2.Datum) AS Jahr
           FROM Schadensfall sf2
          GROUP BY EXTRACT(YEAR FROM sf2.Datum)
       ) temp
 WHERE temp.Jahr = EXTRACT(YEAR FROM sf.Datum)
   AND ABS(Schadenshoehe - temp.Durchschnitt) <= 300;

Zuerst stellen wir durch eine Gruppierung alle Jahreszahlen und die durchschnittlichen Schadenshöhen zusammen (Teil 1 der Lösung). Für Teil 2 der Lösung muss für jeden Schadensfall nur noch Jahr und Schadenshöhe mit dem betreffenden Eintrag in der Ergebnistabelle temp verglichen werden.

Das ist der wesentliche Unterschied und entscheidende Vorteil zur obigen Lösung: Die Durchschnittswerte werden einmalig zusammengestellt und nur noch abgerufen; sie müssen nicht bei jedem Datensatz neu (und ständig wiederholt) berechnet werden.

Bestimme alle Fahrzeuge eines bestimmten Herstellers mit Angabe des Typs.

Lösung Teil 1: Hole die ID des gewünschten Herstellers.
Lösung Teil 2: Hole alle IDs und Bezeichnungen der Tabelle Fahrzeugtyp, die zu dieser Hersteller-ID gehören.
Lösung Teil 3: Hole alle Fahrzeuge, die zu dieser Liste von Fahrzeugtyp-IDs gehören.

SQL-Quelltext:

SELECT Fahrzeug.ID, Kennzeichen, Typen.ID AS TYP, Typen.Bezeichnung
  FROM Fahrzeug,
       ( SELECT ID, Bezeichnung
           FROM Fahrzeugtyp
          WHERE Hersteller_ID =
                ( SELECT ID
                    FROM Fahrzeughersteller
                   WHERE Name = 'Volkswagen' )
       ) Typen
 WHERE Fahrzeugtyp_ID = Typen.ID;

SQL-Ausgabe:

 ID  KENNZEICHEN   TYP BEZEICHNUNG
 23  BOR-RS 890     2  Golf
 22  BOR-PQ 567     3  Passat

Teil 1 der Lösung ist die "innere" Klammer; dies entspricht dem obigen Verfahren. Teil 2 der Lösung ist die "äußere" Klammer; Ergebnis ist eine Tabelle von IDs und Bezeichnungen, also ein Teil der Tabelle Fahrzeugtyp, deren Werte für den Vergleich der WHERE-Klausel und außerdem für die Ausgabe verwendet werden.

Hoch zum Seitenanfang

[Bearbeiten] Verwendung bei Befehlen zum Speichern

Bisher hatten wir Abfragen als Teil von anderen Abfragen benutzt; deshalb wird das Ganze auch als "Unterabfrage" bezeichnet. Man kann das Ergebnis einer Abfrage aber auch zum Speichern verwenden: sowohl für einen einzelnen Wert als auch als vollständigen Datensatz.

[Bearbeiten] Verwendung bei INSERT INTO ... SELECT

Eine Abfrage in einen INSERT-Befehl einzubinden, ist eines der Standardverfahren für INSERT:

 INSERT INTO <zieltabelle>
      ( <spaltenliste> )
 SELECT <spaltenliste>
   FROM <quelltabelle/n>
[       <weitere Festlegungen> ]
[ WHERE <bedingungen> ]

Der SELECT-Befehl kann dabei beliebig aufgebaut sein: Daten aus einer oder mehreren Tabellen holen, mit oder ohne Einschränkungen, mit oder ohne weitere Festlegungen. Lediglich drei Punkte sind zu beachten:

Die Spaltenliste in der Zieltabelle muss mit den Spalten in der SELECT-Auflistung genau übereinstimmen; genauer: Die Datentypen müssen zueinander passen, also gleich sein oder automatisch konvertiert werden können.
Es ist möglich, Daten aus der Zieltabelle zu holen und somit zu verdoppeln. Dann muss die ID automatisch vergeben werden können; und es ist unbedingt mit WHERE zu arbeiten, weil es andernfalls zu einer Endlosschleife kommen kann (siehe das erste nachfolgende Beispiel).
ORDER BY kann nicht sinnvoll genutzt werden, weil das Ergebnis in die Zieltabelle eingefügt wird, wodurch eine Sortierung sowieso verlorenginge.

Um weitere Testdaten zu erhalten, könnte so verfahren werden:

Kopiere die vorhandenen Schadensfälle.

SQL-Quelltext:

INSERT INTO Schadensfall
     ( Datum, Ort, Beschreibung, Schadenshoehe, Verletzte, Mitarbeiter_ID )
SELECT Datum, Ort, Beschreibung, Schadenshoehe, Verletzte, Mitarbeiter_ID
  FROM Schadensfall
 WHERE ID < 10000;

Auf die doppelte Angabe der Spalten kann nicht verzichtet werden, weil ID nicht benutzt werden darf (sie soll automatisch neu vergeben werden) und das DBMS wissen muss, welche Werte wie zugeordnet werden sollen. Auf diese Weise kann man ganz leicht 100 oder 1000 Testdatensätze erzeugen. Für den produktiven Betrieb wird man diese Syntax wohl eher seltener brauchen.

Hinweis: In einem ersten Versuch hatte ich ohne WHERE-Bedingung gearbeitet; ich dachte, dass nur die vor dem Befehl vorhandenen Datensätze bearbeitet würden. Tatsächlich hatte Firebird endlos kopiert, bis ich mit <Strg>-<Alt>-<Entf> den "Stecker zog". Danach war die Datenbank von 3 MB auf 740 MB aufgebläht worden und beschädigt, sodass ich auf diese Tabelle nicht mehr richtig zugreifen konnte. In weiteren Versuchen mit WHERE-Bedingung wurde aber wie erwartet gearbeitet; nur die vorhandenen Datensätze wurden einmalig kopiert.

Die "neuen" Daten können auch aus einer anderen Tabelle geholt und mit konstanten Werten gemischt werden, wie es in der Beispieldatenbank geschieht:

Jeder Abteilungsleiter erhält einen persönlichen Dienstwagen.

SQL-Quelltext:

INSERT INTO Dienstwagen
     ( Kennzeichen, Farbe, Fahrzeugtyp_ID, Mitarbeiter_ID )
SELECT 'DO-WB 42' || Abteilung_ID, 
       'elfenbein', 14, ID 
  FROM Mitarbeiter 
 WHERE Ist_Leiter = 'J';

Die Spaltenliste der Zieltabelle Dienstwagen enthält alle Spalten mit Ausnahme der ID; diese wird automatisch vergeben. Diesen Spalten werden die Spalten der SELECT-Abfrage zugewiesen:

Die Mitarbeiter_ID ist das einzige Feld aus der Quelltabelle Mitarbeiter, das unbedingt benötigt wird und übernommen werden muss.
Die Farbe wird als Konstante eingetragen: Alle Abteilungsleiter bekommen den gleichen Wagentyp.
Der Fahrzeugtyp wird ebenso als Konstante eingetragen. Er könnte auch durch eine Unterabfrage wie oben bei Ergebnisse einfacher Abfragen bestimmt werden.
Für das Kennzeichen habe ich eine String-Verknüpfung vorgesehen, bei der zusätzlich die Abteilung_ID übernommen wird. Bitte benutzen Sie die für Ihr DBMS richtige Art der String-Verknüpfung.

Die WHERE-Bedingung sichert, dass nur Abteilungsleiter "automatisch" einen Dienstwagen erhalten.

Hoch zum Seitenanfang

[Bearbeiten] Verwendung bei INSERT INTO ... VALUES

Die andere Version des INSERT-Befehls arbeitet mit einer Liste von Werten, die direkt angegeben werden:

 INSERT INTO <zieltabelle>
      [ ( <spaltenliste> ) ]
 VALUES ( <werteliste> )

Ein einzelner Befehl sieht dabei wie folgt aus:

Der Mitarbeiter 2 bekommt einen gelben Dienstwagen Typ 2 mit einem bestimmten Kennzeichen.

SQL-Quelltext:

INSERT INTO Dienstwagen
       ( Kennzeichen, Farbe, Fahrzeugtyp_ID, Mitarbeiter_ID )
VALUES ( 'DO-WB 202', 'gelb', 2, 2 );

Versuchen wir, diesen Befehl variabel zu gestalten, sodass Mitarbeiter_ID und Kennzeichen (wie oben) aus einer Abfrage kommen können. Dazu setzen wir diese beiden Spalten hintereinander und ordnen diesen das "Ergebnis als Liste mehrerer Werte" zu unter Verwendung der Funktion LPAD.

SQL-Quelltext:

INSERT INTO Dienstwagen
       ( Kennzeichen, Mitarbeiter_ID, Farbe, Fahrzeugtyp_ID )
VALUES ( ( SELECT 'DO-WB 2' || LPAD(ID, 2, '0'), ID
             FROM Mitarbeiter
            WHERE ID = 2 ),
         'gelb', 2 );

SQL-Ausgabe: Fehlermeldung

SQL error code = -804.
Count of read-write columns does not equal count of values.

Aha, der SELECT-Befehl wird nur als 1 Wert interpretiert, obwohl er zwei passende Werte liefert. Können wir die Abfrage an beiden Stellen verwenden?

SQL-Quelltext:

INSERT INTO Dienstwagen
       ( Kennzeichen, Mitarbeiter_ID, Farbe, Fahrzeugtyp_ID )
VALUES ( ( SELECT 'DO-WB 2' || LPAD(ID, 2, '0') FROM Mitarbeiter WHERE ID = 2 ),
         ( SELECT ID                            FROM Mitarbeiter WHERE ID = 2 ),
         'gelb', 2 );

So funktioniert es, aber das ist natürlich nicht schön, wenn eine fast identische Abfrage doppelt auftauchen muss (auch wenn davon auszugehen ist, dass ein DBMS einen vernünftigen Ausführungsplan erstellt). Dann ist die obige Version mit INSERT INTO ... SELECT mit einer Mischung aus Konstanten und Tabellenspalten die bessere Fassung.

Hoch zum Seitenanfang

[Bearbeiten] Verwendung bei UPDATE

Schauen wir uns die grundsätzliche Struktur eines UPDATE-Befehls an:

 UPDATE <tabellenname>
    SET <spalte1> = <wert1> [ ,
        <spalte2> = <wert2> ]
  WHERE <bedingungsliste>;

Daraus ergibt sich, dass Abfragen benutzt werden können, um einen oder mehrere Werte zu speichern oder um Vergleichswerte für die Bedingungsliste zu liefern (ebenso wie in verschiedenen früheren Beispielen).

Alle Mitarbeiter mit Dienstort 'Bochum' bekommen eine neue Telefonnummer, die neben der Zentrale die Abteilung und die Personalnummer enthält.

Lösung 1: Die WHERE-Bedingung muss die betreffenden Datensätze (genauer: die IDs) der Tabelle Abteilung prüfen und vergleichen.
Lösung 2: Der zugeordnete neue Wert muss passend gestaltet werden. (LPAD und SUBSTRING werden nur verwendet, damit mit festen Längen gearbeitet werden kann.)

SQL-Quelltext:

UPDATE Mitarbeiter
   SET Telefon = '0234/66' 
       || LPAD(Abteilung_ID, 3, '0') 
       || SUBSTRING(LPAD(Personalnummer, 6, '0') FROM 4 FOR 3)
 WHERE Abteilung_ID IN ( SELECT ID
                           FROM Abteilung
                          WHERE Ort = 'Bochum' );

Diese Lösung enthält nichts Neues: Die Abfrage wird mit der IN-Abfrage in die WHERE-Klausel eingebunden; die neuen Werte werden aus den vorhandenen (Abteilung und Personalnummer) gebildet.

Die Abteilung 'Ausbildung' soll dorthin umziehen, wo die Abteilung 'Personalverwaltung' sitzt.

Lösung: Der gewünschte Ort wird aus einer Abfrage geholt.

SQL-Quelltext:

UPDATE Abteilung
   SET Ort = ( SELECT Ort
                 FROM Abteilung
                WHERE Kuerzel = 'Pers' )
 WHERE Kuerzel = 'Ausb';

Bitte wundern Sie sich nicht über ein solch konstruiertes Beispiel; der neue Ort könnte natürlich im Klartext angegeben werden. Mir fällt es bei der vorgegebenen Datenstruktur manchmal schwer, sinnvolle Beispiele zu entwickeln. Wichtig ist mir, dass Sie die möglichen Zusammenhänge zwischen einer Abfrage und einem Speichern-Befehl erkennen.

Man kann sich in der Unterabfrage auch auf Spalten beziehen, die aus der Tabelle stammen, die geändert werden soll. Beispiel (nur teilweiser Bezug auf die Beispieldatenbank):

SQL-Quelltext:

UPDATE Abteilung
   SET Ort = ( SELECT Ort
                 FROM Adressbuch
                WHERE Abteilung.PLZ = Adressbuch.PLZ )
;

Das Problem dieser Abfrage ist, dass sie nur dann funktioniert, wenn es im Adressbuch nur exakt einen einzigen Eintrag zu einer bestimmten PLZ gibt. Sobald man zu einer PLZ mehrere Adressen notiert hat, findet die Unterabfrage mehrere Sätze. Das ist bei dieser Unterabfrage nicht zulässig. Damit der Update auch in solchen Fällen funktioniert, muss ein DISTINCT eingefügt werden oder man verwendet die MAX-oder die MIN-Funktion:

SQL-Quelltext:

UPDATE Abteilung
   SET Ort = ( SELECT max(Ort)
                 FROM Adressbuch
                WHERE Abteilung.PLZ = Adressbuch.PLZ )
;

Man kann auch mehrere Spalten aus Unterabfragen befüllen. Beispiel:

SQL-Quelltext:

UPDATE Abteilung
   SET Ort    = ( SELECT max(Ort)
                    FROM Telefonbuch
                   WHERE PLZ = '12345' ),
       Leiter = ( SELECT Manager
                    FROM Mitarbeiter
                   WHERE Kuerzel = 'A073' )
 WHERE Kuerzel = 'Ausb';
;

Wenn man mehrere Werte aus der selben Unterabfrage übernehmen will, dann könnte man dieselbe Unterabfrage mehrfach angeben. Aber oft kann das Datenbanksystem nicht erkennen, dass es sich immer wieder um dieselbe Unterabfrage handelt, und müsste dieselbe Unterabfrage mehrfach ausführen. Einfacher, übersichtlicher und dann auch schneller ist die folgende Variante. In der Tabelle Abteilung werden die Spalten Ort, Leiter, Telefon geändert.

SQL-Quelltext:

UPDATE Abteilung
   SET ( Ort, Leiter, Telefon ) 
     = ( SELECT Ort, Name, Telefon
           FROM Adressbuch
          WHERE Adressbuch.Personalnummer = Abteilung.Chef_Personalnummer )

Hier werden alle Sätze in der Abteilungstabelle aktualisiert aus der Tabelle Adressbuch; die Personalnummer des Abteilungsleiters kann wie oben bestimmt werden.

Hoch zum Seitenanfang

[Bearbeiten] Verwendung bei DELETE

Schauen wir uns noch die grundsätzliche Struktur eines DELETE-Befehls an:

 DELETE FROM <tabellenname>
[ WHERE <bedingungsliste> ];

Daraus ergibt sich, dass Abfragen nur für Vergleichswerte der Bedingungsliste sinnvoll sind (ebenso wie in verschiedenen früheren Beispielen).

Die Abteilung 'Forschung und Entwicklung' wird ausgelagert; alle zugeordneten Mitarbeiter werden in der Datenbank gelöscht.

Lösung: Suche in einer Abfrage die benötigte ID dieser Abteilung.

SQL-Quelltext:

DELETE FROM Mitarbeiter
 WHERE Abteilung_ID IN ( SELECT ID
                           FROM Abteilung
                          WHERE Bezeichnung = 'Forschung und Entwicklung' );

Hoch zum Seitenanfang

[Bearbeiten] Zusammenfassung

In diesem Kapitel benutzten wir Unterabfragen:

Sowohl einzelne Werte als auch Listen als Ergebnis einer Abfrage können als Vergleichswerte in der WHERE-Klausel verwendet werden.
Eine Tabelle als Ergebnis von Abfragen kann wie jede "echte" Tabelle als Teil der FROM- oder JOIN-Klausel verwendet werden.

Ähnlich können Ergebnisse von Abfragen beim Speichern genutzt werden: