Zum Inhalt springen

Unterabfragen

Aus Wikibooks

Seitentitel: Einführung in SQL: Unterabfragen
(Einführung in SQL: Unterabfragen)
(Einführung in SQL: Unterabfragen)


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. Solche „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 etwas 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 JOIN-Varianten vorzuziehen.

Ergebnis als einzelner Wert

[Bearbeiten]

Ergebnisse einfacher Abfragen

[Bearbeiten]

Immer wieder kennt der Anwender den Namen eines Objekts, benötigt aber für Abfragen die ID. Diese holt er sich mit einer Unterabfrage und übergibt das Ergebnis an die eigentliche Abfrage.

Aufgabe
Aufgabe

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.
select Personalnummer, Name, Vorname
  from Mitarbeiter
 where Abteilung_ID = 
       ( select ID from Abteilung
          where Kuerzel = 'ScAb' );
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:

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.

Aufgabe
Aufgabe

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

  • Lösung Teil 1: Hole die Abteilung_ID, die bei Christina Schindler 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.

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' );
Ausgabe
PERSONALNUMMER  NAME    VORNAME
--------------  ------  -------
80002           Aliman  Zafer
80003           Langer  Norbert
80004           Kolic   Ivana

Ergebnisse von Spaltenfunktionen

[Bearbeiten]

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

Aufgabe
Aufgabe

Hole die Schadensfälle mit unterdurchschnittlicher Schadenshöhe.

  • Lösung Teil 1: Berechne die durchschnittliche Schadenshöhe aller Schadensfälle.
  • Lösung Teil 2: Übernimm das Ergebnis als Vergleichswert in die eigentliche Abfrage.
SELECT ID, Datum, Ort, Schadenshoehe
  from Schadensfall
 where Schadenshoehe < 
       ( select AVG(Schadenshoehe) from Schadensfall );
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
Aufgabe
Aufgabe

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.
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;
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 absolute 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“ (siehe unten).

Ergebnis als Liste mehrerer Werte

[Bearbeiten]

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

Aufgabe
Aufgabe

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 zu dieser Hersteller-ID.
  • Lösung Teil 3: Hole alle Fahrzeuge, die zu dieser Liste von Fahrzeugtypen-IDs passen.
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' ) );
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 im Kapitel 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:

Aufgabe
Aufgabe

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.
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 )
             );
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 wurden zum besseren Vergleich nicht geändert):

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 )
             ;

Ergebnis in Form einer Tabelle

[Bearbeiten]

Das Ergebnis einer Abfrage kann in der Hauptabfrage ü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 usw., die man nicht ohne Weiteres versteht und zuordnen kann.
  • 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.

Aufgabe
Aufgabe

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

Aufgabe
Aufgabe

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

Hinweis: Mit den Möglichkeiten des nächsten Kapitels Erstellen von Views ergeben sich wesentliche Verbesserungen: Mit einer VIEW lassen sich Unterabfragen, die – wie die Liste von Typen und Herstellern – immer wieder benötigt werden, dauerhaft bereitstellen. Und mit einer Inline-View werden verschachtelte Abfragen deutlich übersichtlicher.

Verwendung bei Befehlen zum Speichern

[Bearbeiten]

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.

Verwendung bei INSERT INTO ... SELECT

[Bearbeiten]

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:

Aufgabe
Aufgabe

Kopiere die vorhandenen Schadensfälle.

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.

In einem ersten Versuch fehlte die WHERE-Bedingung; erwartet wurde, dass nur die vor dem Befehl vorhandenen Datensätze bearbeitet würden. Tatsächlich hatte Firebird endlos kopiert, bis mit Strg+Alt+Entf der „Stecker gezogen“ wurde. Danach war die Datenbank von 3 MB auf 740 MB aufgebläht worden und (natürlich) beschädigt, sodass auf diese Tabelle nicht mehr richtig zugegriffen werden konnte. Weitere Versuche mit WHERE-Bedingung arbeiteten wie vorgesehen: 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:

Aufgabe
Aufgabe

Jeder Abteilungsleiter erhält einen persönlichen Dienstwagen.

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. Auch eine Unterabfrage wie oben als Ergebnis einer einfachen Abfrage wäre möglich.
  • Für das Kennzeichen ist 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 beschränkt alles auf Abteilungsleiter.

Verwendung bei INSERT INTO ... VALUES

[Bearbeiten]

Die andere Version des INSERT-Befehls nutzt direkt eine Liste von Werten:

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

Ein einzelner Befehl sieht dabei wie folgt aus:

Aufgabe
Aufgabe

Der Mitarbeiter 2 bekommt einen gelben Dienstwagen Typ 2.

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 LPAD-Funktion (siehe Funktionen (2)).

 Fehler
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 );
Ausgabe
SQL error code = -804.
Count of read-write columns does not equal count of values.

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

 So kann es funktionieren.
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 es 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 Lösung.

Verwendung bei UPDATE

[Bearbeiten]

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

Aufgabe
Aufgabe

Die Mitarbeiter am Dienstort „Bochum“ erhalten 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.)
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.

Aufgabe
Aufgabe

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

  • Lösung: Der gewünschte Ort wird aus einer Abfrage geholt.
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. Bei der vorgegebenen Datenstruktur ist es manchmal schwer, sinnvolle Beispiele zu entwickeln. Wichtig ist, 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):

 Fehler
update Abteilung
   set Ort = ( SELECT Ort
                 from Adressbuch
                where Abteilung.PLZ = Adressbuch.PLZ )
;

Diese Abfrage führt zu folgendem Problem: Sie funktioniert nur dann, 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:

 Genau ein Datensatz wird geliefert.
update Abteilung
   set Ort = ( SELECT MAX(Ort)
                 from Adressbuch
                where Abteilung.PLZ = Adressbuch.PLZ )
;

Bei solchen Unterabfragen mit einem Bezug zu dem Satz, der verändert werden soll, kann es vorkommen, dass die Ausführung dieser Anweisung ziemlich lange dauert. Das liegt daran, dass alle Sätze aus der Tabelle Abteilung verändert werden sollen. Für jeden Satz muss die Unterabfrage erneut ausgeführt werden. Wenn eine einzelne Ausführung dieser Unterabfrage eine Sekunde dauert, und wir haben z. B. 1000 Sätze in der Tabelle Abteilung, dann dauert die Ausführung des gesamten Statements 1000 Sekunden, also ca. 16 Minuten.

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

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 derselben 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 sie mehrfach ausführen. Einfacher, übersichtlicher und dann auch schneller ist die folgende Variante, die aber nicht jedes DBMS kennt.

Aufgabe
Aufgabe

In der Tabelle Abteilung werden die Spalten Ort, Leiter, Telefon gemeinsam geändert.

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 Tabelle Abteilung aktualisiert unter Verwendung der Tabelle Adressbuch; die Personalnummer des Abteilungsleiters kann wie oben bestimmt werden.

Verwendung bei DELETE

[Bearbeiten]

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

Aufgabe
Aufgabe

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

delete from Mitarbeiter
 where Abteilung_ID in ( SELECT ID
                           from Abteilung
                          where Bezeichnung = 'Forschung und Entwicklung' ) ;

Zusammenfassung

[Bearbeiten]

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:

  • Ganze Datensätze werden mit INSERT in eine Tabelle eingefügt werden.
  • Einzelne Werte kommen in die WHERE-Klausel oder SET-Anweisung.

Übungen

[Bearbeiten]

Übung 1 Definitionen Zur Lösung

Welche der folgenden Feststellungen sind richtig, welche sind falsch?

  1. Das Ergebnis einer Unterabfrage kann verwendet werden, wenn es ein einzelner Wert oder eine Liste in Form einer Tabelle ist. Andere Ergebnisse sind nicht möglich.
  2. Ein einzelner Wert als Ergebnis kann durch eine direkte Abfrage oder durch eine Spaltenfunktion erhalten werden.
  3. Unterabfragen sollten nicht verwendet werden, wenn die WHERE-Bedingung für jede Zeile der Hauptabfrage einen anderen Wert erhält und deshalb die Unterabfrage neu ausgeführt werden muss.
  4. Mehrere Unterabfragen können verschachtelt werden.
  5. Für die Arbeitsgeschwindigkeit ist es gleichgültig, ob mehrere Unterabfragen oder JOINs verwendet werden.
  6. Eine Unterabfrage mit einer Tabelle als Ergebnis kann GROUP BY nicht sinnvoll nutzen.
  7. Eine Unterabfrage mit einer Tabelle als Ergebnis kann ORDER BY nicht sinnvoll nutzen.
  8. Bei einer Unterabfrage mit einer Tabelle als Ergebnis ist ein Alias-Name für die Tabelle sinnvoll, aber nicht notwendig.
  9. Bei einer Unterabfrage mit einer Tabelle als Ergebnis sind Alias-Namen für die Spalten sinnvoll, aber nicht notwendig.

Übung 2 Ein einzelner Wert Zur Lösung

Welche Verträge (mit einigen Angaben) hat der Mitarbeiter „Braun, Christian“ abgeschlossen? Ignorieren Sie die Möglichkeit, dass es mehrere Mitarbeiter dieses Namens geben könnte.

Übung 3 Ein einzelner Wert Zur Lösung

Zeigen Sie alle Verträge, die zum Kunden „Heckel Obsthandel GmbH“ gehören. Ignorieren Sie die Möglichkeit, dass der Kunde mehrfach gespeichert sein könnte.

Übung 4 Eine Liste von Werten Zur Lösung

Ändern Sie die Lösung von Übung 3, sodass auch mehrere Kunden mit diesem Namen als Ergebnis denkbar sind.

Übung 5 Eine Liste von Werten Zur Lösung

Zeigen Sie alle Fahrzeuge, die im Jahr 2008 an einem Schadensfall beteiligt waren.

Übung 6 Eine Liste von Werten Zur Lösung

Zeigen Sie alle Fahrzeugtypen (mit ID, Bezeichnung und Name des Herstellers), die im Jahr 2008 an einem Schadensfall beteiligt waren.

Übung 7 Eine Tabelle als Ergebnis Zur Lösung

Bestimmen Sie alle Fahrzeuge eines bestimmten Herstellers mit Angabe des Typs.

Hinweis: Es handelt sich um das letzte Beispiel aus dem Abschnitt „Ergebnis in Form einer Tabelle“. Benutzen Sie jetzt JOIN.

Übung 8 Eine Tabelle als Ergebnis Zur Lösung

Zeigen Sie zu jedem Mitarbeiter der Abteilung „Vertrieb“ den ersten Vertrag (mit einigen Angaben) an, den er abgeschlossen hat. Der Mitarbeiter soll mit ID und Name/Vorname angezeigt werden.

Übung 9 Speichern mit Unterabfrage Zur Lösung

Von der Deutschen Post AG wird eine Tabelle PLZ_Aenderung mit folgenden Inhalten geliefert:

ID  PLZalt  Ortalt           PLZneu  Ortneu
 1  45658   Recklinghausen   45659   Recklinghausen
 2  45721   Hamm-Bossendorf  45721   Haltern OT Hamm
 3  45772   Marl             45770   Marl
 4  45701   Herten           45699   Herten

Ändern Sie die Tabelle Versicherungsnehmer so, dass bei allen Adressen, bei denen PLZ/Ort mit PLZalt/Ortalt übereinstimmen, diese Angaben durch PLZneu/Ortneu geändert werden.

Hinweise: Beschränken Sie sich auf die Änderung mit der ID=3. (Die vollständige Lösung ist erst mit SQL-Programmierung möglich.) Bei dieser Änderungsdatei handelt es sich nur um fiktive Daten, keine echten Änderungen.

Lösungen

Lösung zu Übung 1 Definitionen Zur Übung

Richtig sind 2, 3, 4, 7, 9; falsch sind 1, 5, 6, 8.

Lösung zu Übung 2 Ein einzelner Wert Zur Übung
select ID, Vertragsnummer, Abschlussdatum, Art
  from Versicherungsvertrag
 where Mitarbeiter_ID
       in ( select ID
              from Mitarbeiter
             where Name = 'Braun'
               and Vorname = 'Christian' );

Lösung zu Übung 3 Ein einzelner Wert Zur Übung
select ID, Vertragsnummer, Abschlussdatum, Art
  from Versicherungsvertrag
 where Versicherungsnehmer_ID 
        = ( select ID from Versicherungsnehmer
             where Name ='Heckel Obsthandel GmbH' );

Lösung zu Übung 4 Eine Liste von Werten Zur Übung
select ID, Vertragsnummer, Abschlussdatum, Art
  from Versicherungsvertrag
 where Versicherungsnehmer_ID
       in ( select ID from Versicherungsnehmer
             where Name ='Heckel Obsthandel GmbH' );

Lösung zu Übung 5 Eine Liste von Werten Zur Übung
select ID, Kennzeichen, Fahrzeugtyp_ID as TypID
  from Fahrzeug fz
 where ID in ( select Fahrzeug_ID
                 from Zuordnung_sf_fz zu
                      join Schadensfall sf on sf.ID = zu.Schadensfall_ID
                where EXTRACT(YEAR from sf.Datum) = 2008 );

Lösung zu Übung 6 Eine Liste von Werten Zur Übung
SELECT distinct ft.ID as TypID, ft.Bezeichnung as Typ, fh.Name as Hersteller
  FROM Fahrzeugtyp ft
       inner join Fahrzeughersteller fh on fh.ID = ft.Hersteller_ID
       right join Fahrzeug           fz on ft.ID = fz.Fahrzeugtyp_ID
 WHERE fz.ID IN ( SELECT Fahrzeug_ID
                    FROM Zuordnung_sf_fz zu
                         JOIN Schadensfall sf ON sf.ID = zu.Schadensfall_ID
                   WHERE EXTRACT(YEAR FROM sf.Datum) = 2008 );

Beachten Sie vor allem, dass die WHERE-Bedingung übernommen werden konnte, aber die Tabellen anders zu verknüpfen sind. Die Bedingung könnte in die ON-Klausel einbezogen werden; da sie aber die Auswahl beschränken soll, ist die WHERE-Klausel vorzuziehen.

Lösung zu Übung 7 Eine Tabelle als Ergebnis Zur Übung
SELECT fz.ID, fz.Kennzeichen, Typen.ID AS TYP, Typen.Bezeichnung
  FROM Fahrzeug fz
       join ( SELECT ID, Bezeichnung
                FROM Fahrzeugtyp
               WHERE Hersteller_ID =
                   ( SELECT ID FROM Fahrzeughersteller
                      WHERE Name = 'Volkswagen' )
            ) Typen on fz.Fahrzeugtyp_ID = Typen.ID;

Lösung zu Übung 8 Eine Tabelle als Ergebnis Zur Übung
SELECT vv.ID as VV, vv.Vertragsnummer, vv.Abschlussdatum, vv.Art,
       mi.ID as MI, mi.Name, mi.Vorname
  from Versicherungsvertrag vv
       right join ( select MIN(vv2.ID) as ID, vv2.Mitarbeiter_ID
                      from Versicherungsvertrag vv2
                     group by vv2.Mitarbeiter_id ) Temp
                  on Temp.ID = vv.ID
       right join Mitarbeiter mi on mi.ID = vv.Mitarbeiter_ID
 where mi.Abteilung_ID = ( select ID from Abteilung
                            where Bezeichnung = 'Vertrieb' );

Erläuterungen: Wir benötigen eine einfache Unterabfrage, um die Liste der Mitarbeiter für „Vertrieb“ zu erhalten, und wir benötigen eine Unterabfrage, die uns zur Mitarbeiter-ID die kleinste Vertrags-ID liefert. Wegen der Aufgabenstellung „zu jedem Mitarbeiter“ sowie „mit einigen Angaben“ muss es sich bei beiden Verknüpfungen um einen RIGHT JOIN handeln.

Lösung zu Übung 9 Speichern mit Unterabfrage Zur Übung
update Versicherungsnehmer
   set PLZ, Ort 
           = ( Select PLZneu, Ortneu
                 from PLZ_Aenderg
                where ID = 3 )
 where PLZ = ( Select PLZalt
                 from PLZ_Aenderg
                where ID = 3 )
   and Ort = ( Select Ortalt
                 from PLZ_Aenderg
                where ID = 3 );

Vielleicht funktioniert diese Variante bei Ihrem DBMS nicht; dann ist die folgende Version nötig:

update Versicherungsnehmer
   set PLZ = ( Select PLZneu
                 from PLZ_Aenderg
                where ID = 3 ),
       Ort = ( Select Ortneu
                 from PLZ_Aenderg
                where ID = 3 )
 where PLZ = ( Select PLZalt
                 from PLZ_Aenderg
                where ID = 3 )
   and Ort = ( Select Ortalt
                 from PLZ_Aenderg
                where ID = 3 );