Einführung in SQL: Druckversion: Mehr zu Abfragen

Inhaltsverzeichnis

Wegen des Umfangs und der Komplexität der einzelnen Kapitel besteht die Druckversion aus mehreren Teilen.

Mehr zu Abfragen

• Ausführliche SELECT-Struktur liefert umfangreiche Erläuterungen zur SELECT-Syntax.
• Funktionen (2) erläutert eine Reihe weiterer eingebauter Funktionen.
• WHERE-Klausel im Detail erläutert die Selektionsbedingungen.
• Mehrere Tabellen enthält eine Übersicht, wie sie verknüpft werden können:
  • Einfache Tabellenverknüpfung beschreibt den „traditionellen“ Weg.
  • Arbeiten mit JOIN beschreibt die Grundlagen des „modernen“ Wegs.
  • Durch OUTER JOIN können auch NULL-Werte berücksichtigt werden.
  • Mehr zu JOIN beschreibt weitere Möglichkeiten von JOIN.
• Nützliche Erweiterungen erläutert verschiedene Klauseln des SELECT-Befehls.
• Berechnete Spalten behandelt Spalten, die nicht aus einer Datenbanktabelle kommen, sondern durch SELECT erzeugt werden.
• Gruppierungen ermöglichen zusammenfassende Informationen.
• Durch Unterabfragen können Informationen in Hauptabfragen und zum Speichern weiterverwendet werden.
• Über Erstellen von Views werden Abfragen zur ständigen Benutzung fest in der Datenbank gespeichert.

Die anderen Teile

Über das Inhaltsverzeichnis des Buches sind die anderen Teile der Druckversion zu erreichen:

• Einführung
• Grundlagen
• Erweiterungen
• Anhang

Sonstiges

• Lizenz

Ausführliche SELECT-Struktur

Dieses Kapitel erläutert die Syntax des SELECT-Befehls. Anstelle von Beispielen gibt es Verweise auf diejenigen Kapitel, die die betreffenden Klauseln genauer behandeln.

In diesem Kapitel werden vorzugsweise die englischen Begriffe aus der SQL-Dokumentation benutzt.

Bitte beachten Sie: Der SELECT-Befehl bietet so umfangreiche Möglichkeiten, dass auch bei dieser Übersicht nicht alle Einzelheiten vorgestellt werden können.

Allgemeine Syntax

Der SELECT-Befehl wird als <query specification> oder <select expression>, also Abfrage-Ausdruck bezeichnet und setzt sich grundsätzlich aus diesen Bestandteilen zusammen:

 SELECT 
   [ DISTINCT | ALL ] 
     <select list>
     FROM <table reference list>
   [ <where clause> ]
   [ <group by clause> ]
   [ <having clause> ]
   [ UNION [ALL] <query specification> ]
   [ <order by clause> ]

Diese Bestandteile setzen sich je nach Situation aus weiteren einfachen oder komplexen Bestandteilen zusammen und werden in den folgenden Abschnitten erläutert.

Unbedingt erforderlich sind:

• das Schlüsselwort SELECT
• eine Liste von Spalten
• das Schlüsselwort FROM mit einem oder mehreren Verweisen auf Tabellen

Alle anderen Bestandteile sind optional, können also auch weggelassen werden. Wenn sie benutzt werden, müssen sie in genau der genannten Reihenfolge verwendet werden.

Set Quantifier – Mengenquantifizierer

Als Mengenquantifizierer stehen DISTINCT und ALL zur Verfügung.

Außerdem ist es sehr oft möglich (wenn auch nicht im SQL-Standard vorgeschrieben), das Ergebnis auf eine bestimmte Anzahl von Zeilen zu beschränken, z. B. FIRST 3 oder LIMIT 7.

Erläuterungen dazu finden sich im Kapitel Nützliche Erweiterungen.

Select List – Auswahlliste

Die Liste der Spalten, die ausgewählt werden sollen, wird angegeben durch den Asterisk oder mit einer <column list>.

Asterisk *

Der Asterisk, d. h. das Sternchen *, ist eine Kurzfassung und steht für die Liste aller Spalten (Felder) einer einzelnen Tabelle.

Wenn mehrere Tabellen verknüpft werden, ist der Asterisk zusammen mit dem Namen oder dem Alias einer Tabelle anzugeben.

 <Tabellenname>.*   /* oder */
 <Tabellen-Alias>.*

Erläuterungen dazu finden sich in allen Kapiteln, die sich mit Abfragen befassen.

Column List

Die <Spaltenliste> ist eine Liste von einem oder mehreren Elementen, die in der SQL-Dokumentation als <select sublist> bezeichnet werden:

 <select sublist> [ , <select sublist> , <select sublist> , ... ]

Jedes einzelne Element ist eine Spalte einer Tabelle oder eine abgeleitete Spalte <derived column> – siehe den nächsten Abschnitt. Jedem Element kann ein Spalten-Alias zugewiesen, das Wort AS kann dabei auch weggelassen werden:

 <element> [ [AS] <column name> ]

Bei Spalten aus Tabellen wird einfach deren Name angegeben. Wenn mehrere Tabellen verknüpft werden und ein Spaltenname nicht eindeutig ist, ist der Spaltenname zusammen mit dem Namen oder dem Alias einer Tabelle anzugeben.

 <spaltenname>                   /* oder */
 <Tabellenname>.<spaltenname>    /* oder */
 <Tabellen-Alias>.<spaltenname>

Hinweis: In manchen DBMS darf der Tabellenname nicht mehr benutzt werden, wenn ein Tabellen-Alias angegeben ist.

Erläuterungen und Beispiele dazu finden sich in allen Kapiteln, die sich mit Abfragen befassen.

Derived Column

Eine abgeleitete Spalte bezeichnet den Wert, der von einem <value expression> zurückgeliefert wird. Das kann ein beliebiger Ausdruck sein, der genau einen Wert als Ergebnis hat: vor allem eine Funktion oder eine passende Unterabfrage.

Erläuterungen dazu finden sich vor allem in den Kapiteln Berechnete Spalten und Unterabfragen.

Table Reference List – Tabellen-Verweise

Als Bestandteil der FROM-Klausel werden die beteiligten Tabellen und Verweise darauf aufgeführt:

 FROM <reference list>     /* nämlich */
 FROM <reference1> [ , <reference2> , <reference3> ... ]

In der <Tabellenliste> stehen eine oder (mit Komma getrennt) mehrere Verweise (Referenzen); diese können direkt aus der Datenbank übernommen oder auf verschiedene Arten abgeleitet werden.

Jede dieser Varianten kann erweitert werden:

 <reference> [ [ AS ] <Alias-Name>
               [ ( <derived column list> ) ] ]

Mit AS kann ein Alias-Name dem Verweis hinzugefügt werden; das Schlüsselwort AS kann auch entfallen. Diesem Tabellen-Alias kann (je nach DBMS) in Klammern eine Liste von Spaltennamen hinzugefügt werden, die anstelle der eigentlichen Spaltennamen angezeigt werden sollen.

Die folgenden Varianten sind als Verweise möglich.

Tabellen, Views

Primär verwendet man die Tabellen sowie die Views (fest definierte Sichten).

Erläuterungen dazu finden sich in allen Kapiteln, die sich mit Abfragen befassen, sowie in Erstellen von Views.

Derived Table – Abgeleitete Tabellen

Vor allem können Unterabfragen wie eine Tabelle benutzt werden.

Hinweis: Es gibt eine Reihe weiterer Varianten, um andere Tabellen „vorübergehend“ abzuleiten. Wegen der DBMS-Unterschiede würde die Übersicht zu kompliziert; wir verzichten deshalb auf eine vollständige Darstellung.

Erläuterungen dazu finden sich im Kapitel Unterabfragen.

Joined Table – Verknüpfte Tabelle

Wie eine Tabelle kann auch eine Verknüpfung verwendet werden. Dabei handelt es sich um zwei Tabellen, die nach bestimmten Bedingungen verbunden werden.

Für die Verknüpfung zweier (oder mehrerer) Tabellen gibt es zwei Verfahren: Beim (traditionellen) direkten Weg werden die beiden Tabellen einfach nacheinander aufgeführt. Beim (modernen) Weg wird zu einer Tabelle eine weitere mit JOIN genannt, die durch eine Verknüpfungsbedingung über ON verbunden wird.

Erläuterungen dazu finden sich ab dem Kapitel Mehrere Tabellen.

Where Clause – WHERE-Klausel

Mit der WHERE-Klausel wird eine Suchbedingung festgelegt, welche Zeilen der Ergebnistabelle zur Auswahl gehören sollen. Dieser Filter wird zuerst erstellt; erst anschließend werden Bedingungen wie GROUP BY und ORDER BY ausgewertet.

 WHERE <search condition>

Die Suchbedingung <search condition> ist eine Konstruktion mit einem eindeutigen Prüfergebnis: Entweder die Zeile gehört zur Auswahl, oder sie gehört nicht zur Auswahl. Es handelt sich also um eine logische Verknüpfung von einer oder mehreren booleschen Variablen.

Erläuterungen zu den folgenden Einzelheiten finden sich vor allem in den Kapiteln WHERE-Klausel im Detail und Unterabfragen.

Eine einzelne Suchbedingung

Eine Suchbedingung hat eine der folgenden Formen, deren Ergebnis immer WAHR oder FALSCH (TRUE bzw. FALSE) lautet:

 <wert1> [ NOT ] <operator> <wert2>
 <wert1> [ NOT ] BETWEEN <wert2> AND <wert3>
 <wert1> [ NOT ] LIKE <wert2> [ ESCAPE <wert3> ]
 <wert1> [ NOT ] CONTAINING <wert2>
 <wert1> [ NOT ] STARTING [ WITH ] <wert2>
 <wert1> IS [ NOT ] NULL
 <wert1> [ NOT ] IN <werteliste>
 EXISTS <select expression>
 NOT <search condition>

Anstelle eines Wertes kann auch ein Wertausdruck <value expression> stehen, also eine Unterabfrage, die genau einen Wert als Ergebnis liefert.

Anstelle einer Werteliste kann auch ein Auswahl-Ausdruck <select expression> stehen, also eine Unterabfrage, die eine Liste mehrerer Werte als Ergebnis liefert.

Als <operator> sind folgende Vergleiche möglich, und zwar sowohl für Zahlen als auch für Zeichenketten und Datumsangaben:

 =  <  >  <=  >=  <>

Mehrere Suchbedingungen

Mehrere Suchbedingungen können miteinander verbunden werden:

 NOT <search condition> 
 <search condition1> AND <search condition2>
 <search condition1> OR  <search condition2>

Bitte beachten Sie: NOT hat die stärkste Verbindung und wird zuerst ausgewertet. Danach hat AND eine stärkere Verbindung und wird als nächstes untersucht. Erst zum Schluss kommen die OR-Verbindungen. Um Unklarheiten zu vermeiden, wird dringend empfohlen, zusammengesetzte Suchbedingungen in Klammern zu setzen, um Prioritäten deutlich zu machen.

Group By Clause – GROUP BY-Klausel

Mit der GROUP BY-Klausel werden alle Zeilen, die in einer oder mehreren Spalten den gleichen Wert enthalten, in jeweils einer Gruppe zusammengefasst. Dies macht in der Regel nur dann Sinn, wenn in der Spaltenliste des SELECT-Befehls eine gruppenweise Auswertung, also eine der Spaltenfunktionen enthalten ist.

Die allgemeine Syntax lautet:

 GROUP BY <Spaltenliste>

Die Spaltenliste enthält, durch Komma getrennt, die Namen von einer oder mehreren Spalten. Bei jeder Spalte kann eine eigene Sortierung angegeben werden (wie bei den Datentypen erläutert):

   <Spaltenname>
   -- oder
   <Spaltenname> COLLATE <Collation-Name>

Erläuterungen dazu finden sich vor allem im Kapitel Gruppierungen.

Having Clause – HAVING-Klausel

Die HAVING-Klausel dient dazu, nicht alle ausgewählten Zeilen in die Ausgabe zu übernehmen, sondern nur diejenigen, die den zusätzlichen Bedingungen entsprechen. Sie wird in der Praxis überwiegend als Ergänzung zur GROUP BY-Klausel verwendet und folgt ggf. direkt danach.

 HAVING <bedingungsliste>

Erläuterungen dazu finden sich ebenfalls im Kapitel Gruppierungen.

Union Clause – UNION-Klausel

Mit der UNION-Klausel werden mehrere eigentlich getrennte Abfragen zusammengefasst, um ein einheitliches Ergebnis zu liefern. Dabei sind die einzelnen Bedingungen zu komplex, um sie zusammenzufassen; oder sie können nicht sinnvoll verbunden werden. Es setzt eine weitgehend identische Spaltenliste voraus.

 SELECT <spaltenliste1> FROM <tabellenliste1> WHERE <bedingungsliste1>
 UNION
 SELECT <spaltenliste2> FROM <tabellenliste2> WHERE <bedingungsliste2>

Erläuterungen dazu finden sich im Kapitel Nützliche Erweiterungen.

Order By Clause – ORDER BY-Klausel

Mit der ORDER BY-Klausel werden die Datensätze der Ergebnismenge in eine bestimmte Reihenfolge gebracht.

Erläuterungen dazu finden sich in allen Kapiteln, die sich mit Abfragen befassen.

Zusammenfassung

In diesem Kapitel erhielten Sie einen umfangreichen Überblick über die Syntax des SELECT-Befehls:

• Die Listen der gewünschten Spalten und der beteiligten Tabellen sind Pflichtangaben, alle anderen Klauseln sind optional.
• Für die Verknüpfung mehrerer Tabellen gibt es einen (traditionellen) direkten Weg und den (modernen) Weg über JOIN.
• Die WHERE-Klausel ermöglicht komplexe Bedingungen darüber, welche Datensätze abgefragt werden sollen.
• Mit Gruppierung, Sortierung und Zusammenfassung gibt es weitere Möglichkeiten für Abfragen.

Übungen

Übung 1

Allgemeine Syntax

Zur Lösung

Bringen Sie die folgenden Bestandteile des SELECT-Befehls in die richtige Reihenfolge (es gibt Begriffe, die an zwei bzw. drei Stellen gehören):

<bedingung> – DISTINCT – FROM – GROUP BY – HAVING – 
ORDER BY – SELECT – <spalten> – <tabelle> – WHERE 

Übung 2

Allgemeine Syntax

Zur Lösung

Welche der genannten Bestandteile eines SELECT-Befehls sind unbedingt erforderlich?

Übung 3

Spaltenliste

Zur Lösung

Welche der folgenden Spaltenlisten aus der Beispieldatenbank sind richtig, welche nicht?

1. SELECT * FROM Mitarbeiter;
2. SELECT ID, Name FROM Mitarbeiter;
3. SELECT ID, Name FROM Mitarbeiter, Abteilung;
4. SELECT ID, Name, Kuerzel FROM Mitarbeiter, Abteilung;
5. SELECT ab.ID, Name FROM Mitarbeiter, Abteilung ab;
6. SELECT ab.ID, Name, Krz Kuerzel FROM Mitarbeiter, Abteilung ab;
7. SELECT ab.ID, Name, Kuerzel Krz FROM Mitarbeiter, Abteilung ab;
8. SELECT ab.ID, mi.Name, ab.Kuerzel FROM Mitarbeiter mi, Abteilung ab;

Übung 4

Spaltenliste

Zur Lösung

Schreiben Sie für folgende Abfragen die Spalten und Tabellen auf.

1. Zeige alle Informationen zu den Mitarbeitern.
2. Zeige zu jedem Mitarbeiter Name, Vorname und Nummer der Abteilung.
3. Zeige zu jedem Mitarbeiter Name, Vorname und Kuerzel der Abteilung.
4. Zeige zu jedem Mitarbeiter ID, Name, Vorname sowie das Kennzeichen des Dienstwagens.

Übung 5

Suchbedingungen

Zur Lösung

Welche der folgenden Suchbedingungen sind richtig, welche nicht? Welche korrekten Bedingungen liefern immer FALSE als Ergebnis?

1. WHERE Name NOT = 'Meyer';
2. WHERE 1 = 2;
3. WHERE NOT Name LIKE 'M%';
4. WHERE Geburtsdatum LIKE '1980';
5. WHERE ID BETWEEN 20 AND 10;
6. WHERE Mobil IS NULL;
7. WHERE Name IS NULL;
8. WHERE Name STARTING WITH 'L' AND CONTAINING 'a';
9. WHERE ID IN (1, 3, 'A');
10. WHERE ID IN (1, 3, '5');

Übung 6

Suchbedingungen

Zur Lösung

Formulieren Sie die folgenden Aussagen als Bedingungen der WHERE-Klausel zur Tabelle Mitarbeiter.

1. Der Vorname lautet 'Petra'.
2. Der Name enthält die Zeichen 'mann'.
3. Der Name beginnt mit 'A', es handelt sich um Abteilung 8.
4. Es ist keine Mobil-Nummer gespeichert.

Lösungen

Lösung zu Übung 1

Allgemeine Syntax

Zur Übung

Richtig ist dies (mit Vervollständigung zu einem Befehl):

SELECT DISTINCT <spalten>
  FROM <tabelle>
 WHERE <bedingung>
 GROUP BY <spalten>
   HAVING <bedingung>
 ORDER BY <spalten>;

Lösung zu Übung 2

Allgemeine Syntax

Zur Übung

SELECT <spalten> FROM <tabelle>

Lösung zu Übung 3

Spaltenliste

Zur Übung

Richtig sind 1, 2, 5, 7, 8.

Falsch sind 3, 4 (ID ist mehrdeutig), 6 (Spaltenname und -Alias in falscher Reihenfolge).

Lösung zu Übung 4

Spaltenliste

Zur Übung

1. SELECT * FROM Mitarbeiter
2. SELECT Name, Vorname, Abteilung_ID FROM Mitarbeiter
3. SELECT Name, Vorname, Kuerzel FROM Mitarbeiter, Abteilung
   /* oder mit Tabellen-Alias: */
   SELECT mi.Name, mi.Vorname, ab.Kuerzel FROM Mitarbeiter mi, Abteilung ab
4. SELECT Name, Vorname, Dienstwagen.ID, Kennzeichen FROM Mitarbeiter, Dienstwagen
/* auch einheitlich mit Tabellen-Namen oder Tabellen-Alias möglich */

Lösung zu Übung 5

Suchbedingungen

Zur Übung

1. Richtig.
2. Richtig, immer FALSE: 1 ist immer ungleich 2.
3. Richtig, weil die Teilbedingung "Name LIKE 'M%'" verneint wird. Ebenfalls richtig wäre es, das NOT hinter <wert1>, also hinter Name zu schreiben.
4. Richtig, weil das Jahr laut ISO 8601 am Anfang steht.
5. Richtig, immer FALSE: es gibt keine Zahl „größer/gleich 20“ und gleichzeitig „kleiner/gleich 10“.
6. Richtig.
7. Richtig, immer FALSE, weil der Name als Pflichtangabe niemals NULL sein kann.
8. Falsch, weil Name in der zweiten Bedingung hinter AND fehlt.
9. Falsch, weil 'A' keine Zahl ist, aber zu ID bei IN eine Liste von Zahlen gehört.
10. Richtig, weil '5' automatisch als Zahl konvertiert wird.

Lösung zu Übung 6

Suchbedingungen

Zur Übung

1. WHERE Vorname = 'Petra';
2. WHERE Name CONTAINING 'mann';
3. WHERE Name STARTING WITH 'A' AND Abteilung_ID = 8;
4. WHERE Mobil = '' OR Mobil IS NULL;

---

Funktionen (2)

Dieses Kapitel behandelt weitere Skalarfunktionen in Ergänzung zu den grundlegenden Funktionen.

Allgemeine Hinweise

Auch hier gelten die dort aufgeführten Hinweise:

• Jedes DBMS bietet eigene Funktionen sowie Varianten.
• Die Klammern werden in den Beschreibungen der Funktionen oft nicht angegeben.
• Die Beispiele werden durch eine verkürzte Schreibweise dargestellt, wobei der Zusatz "from fiktiv" als optional gekennzeichnet ist und für Firebird/Interbase durch "from rdb$database" bzw. für Oracle durch "from dual" zu ersetzen ist.

SELECT 2 * 3 [from fiktiv];

Funktionen für Zahlen

Auch bei diesen weiteren Funktionen müssen Sie auf den Typ achten.

POWER und SQRT – Potenzen und Wurzeln

Mit POWER wird eine beliebige Potenz oder Wurzel berechnet:

POWER( <basis>, <exponent> )

Sowohl für <basis> als auch für <exponent> sind nicht nur ganze positive Zahlen, sondern alle Zahlen zulässig. Mit Dezimalzahlen als <exponent> werden (genau nach mathematischen Regeln) beliebige Wurzeln berechnet; in diesem Fall sind als <basis> negative Zahlen unzulässig. Beispiele:

SELECT POWER(  5,    3  )  [from fiktiv];   /* Ergebnis:  125,000 */
SELECT POWER(  5,    2.5)  [from fiktiv];   /* Ergebnis:   55,902 */
SELECT POWER(  5,    0.5)  [from fiktiv];   /* Ergebnis:    2,236 also Wurzel  aus 5 */
SELECT POWER(  0.5, -3  )  [from fiktiv];   /* Ergebnis:    8,000 also 3.Potenz zu 2 */
SELECT POWER( 12.35, 1.5)  [from fiktiv];   /* Ergebnis:   43,401 */
SELECT POWER(-12.35, 1.5)  [from fiktiv];   /* expression evaluation not supported. */
SELECT POWER( 12.35,-1.5)  [from fiktiv];   /* Ergebnis:    0,023 */
SELECT POWER(-12.35,-1.5)  [from fiktiv];   /* expression evaluation not supported */

Mit SQRT (= Square Root) gibt es für die Quadratwurzel eine kürzere Schreibweise anstelle von POWER(x,0.5):

SELECT  SQRT(12.25)        [from fiktiv];   /* Ergebnis:    3,500 */

EXP und LOG – Exponentialfunktion und Logarithmen

Mit EXP wird die Exponentialfunktion im engeren Sinne bezeichnet, also mit der Eulerschen Zahl e als Basis.

SELECT EXP(1)           [from fiktiv];   /* Ergebnis: 2,71828182845905 */

Mit LOG(<wert>, <basis>) wird umgekehrt ein Logarithmus bestimmt, mit LN der natürliche und mit LOG10 der dekadische Logarithmus.

SELECT LOG(10, EXP(1))  [from fiktiv];   /* Ergebnis: 2,30258509299405 */
SELECT LOG(10, 10)      [from fiktiv];   /* Ergebnis: 1,000            */
SELECT LN(10)           [from fiktiv];   /* Ergebnis: 2,30258509299405 */

Winkelfunktionen

Die trigonometrischen Funktionen arbeiten mit dem Bogenmaß (nicht mit einer Grad-Angabe).

SIN    Sinus
COS    Cosinus
TAN    Tangens
COT    Cotangens
ASIN   Arcussinus   als Umkehrfunktion des Sinus
ACOS   Arcuscosinus als Umkehrfunktion des Cosinus
ATAN   Arcustangens als Umkehrfunktion des Tangens

Mit DEGREES wird ein Bogenmaß in Grad umgerechnet, mit RADIANS ein Gradmaß in das Bogenmaß.

PI liefert die entsprechende Zahl und kann auch für die trigonometrischen Funktionen verwendet werden:

SELECT SIN( PI()/6 )  [from fiktiv];   /* $\pi /6$ sind 30°, also Ergebnis 0,5 */

ABS, RAND, SIGN – verschiedene Funktionen

Mit ABS wird der absolute Betrag der gegebenen Zahl zurückgegeben.

SIGN liefert als Hinweis auf das Vorzeichen der gegebenen Zahl einen der Werte 1, 0, -1 – je nachdem, ob die gegebene Zahl positiv, 0 oder negativ ist.

SELECT SIGN(12.34), SIGN(0), SIGN(-5.67)  [from fiktiv];
/* Ergebnis:   1         0        -1     */

RAND liefert eine Zufallszahl im Bereich zwischen 0 und 1 (jeweils einschließlich). Bitte beachten Sie, dass dies keine echten Zufallszahlen sind, sondern Pseudozufallszahlen.

Mit RAND(<vorgabewert>) wird innerhalb einer Sitzung immer dieselbe Zufallszahl erzeugt.

Mit einer Kombination von RAND und FLOOR erhält man eine „zufällige“ Folge ganzer Zahlen:

FLOOR( <startwert> + ( RAND() * ( <zielwert> - <startwert> + 1 )) )

Beispielsweise liefert die mehrfache Wiederholung der folgenden Abfrage diese Zahlen zwischen 7 und 12:

SELECT FLOOR(7 + (RAND() * 6)) [from fiktiv];   
/* Ergebnisse:  10  9  9  7  8  7  9  9  10  12  usw. */

Diese Funktion ist geeignet, um Datensätze mit SELECT in beliebiger Reihenfolge oder einen zufällig ausgewählten Datensatz abzurufen:

SELECT * FROM <tabelle> ORDER BY RAND();
SELECT FIRST 1 * FROM <tabelle> ORDER BY RAND();

Funktionen für Zeichenketten

Auch zur Bearbeitung und Prüfung von Zeichenketten (Strings) gibt es weitere Funktionen.

Verknüpfen von Strings

Zu den Standardverfahren || + CONCAT gibt es Ergänzungen.

MySQL bietet mit CONCAT_WS eine nützliche Erweiterung, bei der zwischen den Teiltexten ein Trennzeichen gesetzt wird.

SPACE(n) – für MS-SQL und MySQL – erzeugt einen String, der aus n Leerzeichen besteht.

REPEAT( <text>, <n> ) – für MySQL – und REPLICATE( <text>, <n> ) – für MS-SQL – erzeugen eine neue Zeichenkette, in der der <text> n-mal wiederholt wird.

Mit LPAD wird <text1>, sofern erforderlich, auf die gewünschte <länge> gebracht und dabei von links mit <text2> bzw. Leerzeichen aufgefüllt. Mit RPAD wird von rechts aufgefüllt. MS-SQL kennt diese Funktionen nur für Access.

LPAD ( <text1>, <länge> [ , <text2> ] )
RPAD ( <text1>, <länge> [ , <text2> ] )

Wenn der dadurch erzeugte Text zu lang wird, wird zuerst <text2> und notfalls auch <text1> abgeschnitten. Beispiele:

SELECT LPAD( CAST(12345 AS CHAR(8)), 10, '0')  [from fiktiv];  -- Ergebnis: '0012345   '

Nanu, das sind doch nur 7 statt 10 Ziffern? Achso, zuerst wird mit CAST ein 8 Zeichen langer String erzeugt; dann ist nur noch Platz für 2 Nullen. Also muss es mit einer dieser Varianten gehen:

SELECT LPAD( CAST(12345 AS CHAR(5)), 10, '0')  [from fiktiv];  -- Ergebnis: '0000012345'
SELECT LPAD( 12345,   10, '0'  )               [from fiktiv];  -- Ergebnis: '0000012345'
SELECT LPAD( 'Hilfe', 10, '-_/')               [from fiktiv];  -- Ergebnis: '-_/-_Hilfe'
SELECT LPAD( 'Ich brauche Hilfe', 10, '-_/')   [from fiktiv];  -- Ergebnis: 'Ich brauch'
SELECT RPAD( 'Hilfe', 10, '-_/')               [from fiktiv];  -- Ergebnis: 'Hilfe-_/-_'
SELECT RPAD( 'Ich brauche Hilfe', 10, '-_/')   [from fiktiv];  -- Ergebnis: 'Ich brauch'

LEFT, RIGHT – Teile von Zeichenketten

Als Ergänzung zu SUBSTRING wird mit LEFT( <text>, <anzahl> ) der linke Teil, also der Anfang eines Textes mit der gewünschten Länge <anzahl> ausgegeben. Ebenso erhält man mit RIGHT( <text>, <anzahl> ) den rechten Teil, also das Ende eines Textes.

SELECT LEFT ('Abc Def Ghi', 5)  [from fiktiv];   /* Ergebnis: 'Abc D'  */
SELECT RIGHT('Abc Def Ghi', 5)  [from fiktiv];   /* Ergebnis: 'f Ghi'  */

TRIM, LTRIM, RTRIM – Leerzeichen u. a. entfernen

Mit der TRIM-Funktion werden bestimmte Zeichen – meistens Leerzeichen – am Anfang und/oder am Ende eines Textes entfernt:

TRIM( [ [ LEADING | TRAILING | BOTH ] [ <zeichen> ] FROM ] <text> )

Die Parameter werden wie folgt benutzt:

• Es soll <zeichen> entfernt werden. Es kann sich um ein einzelnes Zeichen, aber auch um einen Text handeln.
• Wenn dieser Parameter fehlt, wird nach führenden bzw. abschließenden Leerzeichen gesucht.
• Wenn LEADING angegeben ist, werden nur führende Zeichen geprüft, bei TRAILING nachfolgende und bei BOTH sowohl als auch. Wenn nichts davon angegeben wird, wird BOTH angenommen.

Beispiele:

SELECT TRIM( '   Dies ist ein Text.   ' )  [from fiktiv];    /* Ergebnis: 'Dies ist ein Text.' */
SELECT TRIM( LEADING  'a' FROM 'abcde'  )  [from fiktiv];    /* Ergebnis: 'bcde'  */
SELECT TRIM( TRAILING 'e' FROM 'abcde'  )  [from fiktiv];    /* Ergebnis: 'abcd'  */
SELECT TRIM( 'Test' FROM 'Test als Test')  [from fiktiv];    /* Ergebnis: ' als ' */

LTRIM (= Left-Trim) und RTRIM (= Right-Trim) sind Kurzfassungen, bei denen Leerzeichen am Anfang bzw. am Ende entfernt werden; MS-SQL kennt nur diese beiden Kurzfassungen.

Suchen und Ersetzen

Mit POSITION wird der Anfang eines Textes innerhalb eines anderen gesucht.

POSITION( <text1> IN <text2> )              SQL-Standard
POSITION( <text1>, <text2> [, <start>] )    nicht bei MySQL
LOCATE  ( <text1>, <text2> [, <start>] )    bei MySQL

Bei MS-SQL gibt es diese Funktionen nicht; stattdessen kann (mit abweichender Bedeutung) PATINDEX verwendet werden. Oracle bietet zusätzlich INSTR (= "in string") an.

Die Bedeutung der Parameter dürfte offensichtlich sein:

• <text2> ist der Text, in dem gesucht werden soll.
• <text1> ist ein Teiltext, der in <text2> gesucht wird.
• Sofern <start> angegeben ist, wird erst ab dieser Position innerhalb <text2> gesucht. Wenn <start> fehlt, wird ab Position 1 gesucht.
• Die Funktion gibt die Startposition von <text1> innerhalb von <text2> an. Wenn <text1> nicht gefunden wird, lautet das Ergebnis 0.

Beispiele:

SELECT POSITION( 'ch', 'Ich suche Text' )     [from fiktiv];   /* Ergebnis:  2 */
SELECT POSITION( 'ch', 'Ich suche Text', 3 )  [from fiktiv];   /* Ergebnis:  7 */
SELECT POSITION('sch', 'Ich suche Text' )     [from fiktiv];   /* Ergebnis:  0 */

REPLACE dient zum Ersetzen eines Teiltextes durch einen anderen innerhalb eines Gesamttextes:

REPLACE( <quelltext>, <suche>, <ersetze> )

Die verschiedenen SQL-Dialekte verhalten sich unterschiedlich, ob NULL-Werte oder leere Zeichenketten zulässig sind.

SELECT REPLACE('Ich suche Text', 'ch', 'sch') [from fiktiv];   /* Ergebnis: 'Isch susche Text' */
SELECT REPLACE('Die liebe Seele', 'e', ’’)    [from fiktiv];   /* Ergebnis: 'Di lib Sl' */

REVERSE passt zwar nicht zu dem, was man in diesem Zusammenhang erwartet; aber auch diese Funktion ändert einen vorhandenen String, und zwar dadurch, dass die Reihenfolge aller Zeichen umgekehrt wird:

SELECT REVERSE( 'Hilfe' )                     [from fiktiv];   /* Ergebnis: 'efliH' */

Funktionen für Datums- und Zeitwerte

Bitte beachten Sie wiederum die Besonderheiten der Datentypen je nach DBMS.

Differenzen bei Datum oder Uhrzeit

Dafür gibt es vorzugsweise die DATEDIFF-Funktion in unterschiedlicher Version:

DATEDIFF ( <part>, <start>, <end> )   /* bei MS-SQL oder Firebird */
DATEDIFF ( <start>, <end> )           /* bei MySQL nur die Anzahl der Tage */

Das Ergebnis ist vom gleichen Typ wie die gesuchte Differenz (also meistens ein ganzzahliger Wert). Als <part> gibt es die gleichen Varianten wie bei den wichtigsten Funktionen:

YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MILLISECOND

Beim Vergleich von Start- und Enddatum gilt:

• Das Ergebnis ist positiv, wenn der zweite Wert größer ist als der erste.
• Das Ergebnis ist 0, wenn beide Werte gleich sind.
• Das Ergebnis ist negativ, wenn der zweite Wert kleiner ist als der erste.

Bitte beachten Sie: Die DBMS verhalten sich unterschiedlich, ob die Datumsangaben verglichen werden oder der jeweilige Bestandteil. Beispielsweise kann das Ergebnis für beide der folgenden Prüfungen 1 lauten, obwohl die „echte“ Differenz im einen Fall ein Tag, im anderen fast zwei Jahre sind:

DATEDIFF( YEAR, '31.12.2008', '01.01.2009' )
DATEDIFF( YEAR, '01.01.2008', '31.12.2009' )

Aufgabe

Bestimme die Anzahl der Tage seit dem letzten gemeldeten Schadensfall.

SELECT DATEDIFF(DAY, MAX(Datum), CURRENT_DATE) 
  FROM Schadensfall;         /* Ergebnis: 49 */

Aufgabe

Bestimme die Anzahl der Minuten seit Tagesbeginn.

SELECT DATEDIFF(MINUTE, CAST('00:00' AS TIME), CURRENT_TIME) [from fiktiv];   /* Ergebnis: 967 */

Datumsangaben können grundsätzlich auch per Subtraktion verglichen werden, weil „intern“ häufig ein Tag gleich 1 ist. Darauf kann man sich aber nicht immer verlassen; und es ist schwierig, die Bruchteile eines Tages zu berücksichtigen. Beispiel:

SELECT (CURRENT_DATE - MAX(Datum)) FROM Schadensfall;         /* Ergebnis: 49 */

Werte für Datum oder Uhrzeit ändern

Sehr häufig muss aus einem vorhandenen Datum oder Uhrzeit ein neuer Wert berechnet werden. Der SQL-Standard sieht dazu die direkte Addition und Subtraktion vor:

<datetime> + <value>
<datetime> - <value>
<value>    + <datetime>

<datetime> steht für den gegebenen Wert, <value> für den Zeitraum, der addiert oder subtrahiert werden soll.

Aufgabe

Aus dem aktuellen Zeitwert '19.09.2009 16:10' wird ein neuer Wert bestimmt, der einen halben Tag in der Zukunft liegt:

SELECT CURRENT_TIMESTAMP + 0.5   [from fiktiv];       /* Ergebnis: '20.09.2009 04:10:39' */

MySQL akzeptiert nur ganze Zahlen; deshalb ist explizit die Art des Intervalls anzugeben (siehe Dokumentation).

Da das Umrechnen von Zahlen in Datums- und Zeitwerte und umgekehrt für den Anwender umständlich ist, werden viele zusätzliche Funktionen bereitgestellt. Sehr verbreitet ist DATEADD:

DATEADD( <part>, <value>, <datetime> )                      /* Firebird, MS-SQL */
DATE_ADD( <datetime> , INTERVAL <value> <part> )            /* MySQL */

Aufgabe

Welche Versicherungsverträge laufen schon mehr als 10 Jahre?

SELECT ID, Vertragsnummer, Abschlussdatum  FROM Versicherungsvertrag
 WHERE DATEADD(YEAR, 10, Abschlussdatum) <= CURRENT_DATE;   /* Ergebnis: 18 Datensätze */

Als Ergänzung oder Alternative gibt es weitere Funktionen, beispielsweise DATE_SUB als Subtraktion, ADDDATE oder ADD_MONTHS.

Funktionen für logische und NULL-Werte

Neben den Standardprüfungen vor allem bei der WHERE-Klausel (siehe nächstes Kapitel) und den Operatoren AND, OR, NOT gibt es weitere Prüfungen.

COALESCE – Suche Wert ungleich NULL

Die COALESCE-Funktion sucht in einer Liste von Werten (bzw. Ausdrücken) den ersten, der nicht NULL ist. Wenn alle Werte NULL sind, ist der Rückgabewert (zwangsläufig) NULL.

Aufgabe

Nenne zu jedem Mitarbeiter eine Kontaktmöglichkeit: vorzugsweise Mobilnummer, dann Telefonnummer, dann Email-Adresse.

SELECT Name, Vorname, COALESCE(Mobil, Telefon, Email) AS Kontakt FROM Mitarbeiter;

Das Ergebnis überrascht zunächst, denn einige Mitarbeiter hätten danach keine Kontaktmöglichkeit. Bei der Abfrage nach IS NULL zur WHERE-Klausel wird aber erläutert, dass eine leere Zeichenkette ungleich NULL ist; bei diesen Mitarbeitern wird also ein leerer Eintrag, aber nicht "nichts" angezeigt.

Bitte nehmen Sie diesen Hinweis als Empfehlung, lieber NULL zu speichern als den leeren String ’’.

NULLIF

Die Funktion NULLIF vergleicht zwei Werte und liefert NULL zurück, wenn beide Werte gleich sind; andernfalls liefert der erste Wert das Ergebnis.

Aufgabe

Suche alle Versicherungsnehmer, die im Alter von 18 Jahren ihren Führerschein gemacht haben.

select Name, Vorname, EXTRACT(YEAR from Geburtsdatum) + 18 as Jahr
  from Versicherungsnehmer
 where Geburtsdatum is not null
   and NULLIF( EXTRACT(YEAR from Geburtsdatum) + 18, EXTRACT(YEAR from Fuehrerschein) ) is null;

Ausgabe

NAME          VORNAME    JAHR
------------  ---------  ----
Liebermann    Maria      1988

Hinweis: Sowohl COALESCE als auch NULLIF sind Kurzfassungen für spezielle Fallunterscheidungen mit CASE WHEN, in denen zusätzlich IS NULL eingebunden wird – siehe dazu Nützliche Erweiterungen.

Verschiedene Funktionen

Auch wenn die Funktionen in diesem Abschnitt beim SQL-Standard vorgesehen sind, sind sie nicht immer vorhanden. Wir verzichten deshalb wiederum auf nähere Erläuterungen und verweisen auf die jeweilige Dokumentation.

ROW_NUMBER – Zeilen nummerieren

Mit der ROW_NUMBER-Funktion werden die Zeilen im Ergebnis einer Abfrage nach der betreffenden Sortierung durchnummeriert. MS-SQL verwendet die folgende Syntax:

ROW_NUMBER()  OVER ( [ <partition_by_clause> ] <order_by_clause> )

CURRENT_USER – der aktuelle Benutzer

Mit CURRENT_USER (in der Regel ohne Klammer) wird der aktuelle Benutzername abgefragt. Dieser kann auch per DEFAULT bei Neuaufnahmen automatisch in einer Spalte einer Tabelle eingetragen werden.

SELECT CURRENT_USER   [from fiktiv];    /* Ergebnis: SYSDBA */

Zusammenfassung

In diesem Kapitel lernten Sie weitere eingebaute Funktionen kennen:

• Für Zahlen gibt es viele mathematische Funktionen wie Potenzen, Wurzeln, Exponential- oder Winkelfunktionen.
• Zeichenketten können auf vielfache Weise verknüpft oder zum Erstellen neuer Strings bearbeitet werden.
• Datums- und Zeitwerte können im Detail verglichen und verrechnet werden.

Übungen

Übung 1

Funktionen für Zahlen

Zur Lösung

Geben Sie mit SQL-Funktionen die Formeln für die folgenden Aufgaben an. Es geht nur um die Formeln, nicht um einen passenden SELECT-Befehl. Bei den Aufgaben 1 bis 3 sind jeweils zwei Lösungen möglich.

1. Gegeben seien zwei Zahlen a, b. Berechnen Sie a² + 2ab + b².
2. Berechnen Sie die Quadratwurzel von 216,09.
3. Ein Auftragsverwaltungsprogramm speichert in der Spalte Bestellung die Bestellwerte und in der Spalte Zahlung die Einzahlungen. Bestimmen Sie mit einem einzigen Ausdruck die Prüfung, ob der Kunde Guthaben oder Schulden (Zahlungsverpflichtung) hat.
4. Bestimmen Sie den Betrag von Aufgabe 3 (unabhängig davon, ob es sich um Guthaben oder Schulden handelt).

Übung 2

Zufallszahlen

Zur Lösung

Bestimmen Sie mit SQL-Funktionen die folgenden Zufallszahlen.

1. eine beliebige Zufallszahl zwischen 0 und 1
2. eine beliebige Zufallszahl zwischen 0 und 4
3. eine beliebige Zufallszahl zwischen 1 und 5
4. eine Zufallszahl als ganze Zahl zwischen 1 und 5
5. eine Zufallszahl als ganze Zahl zwischen 1 und 26
6. einen zufällig ausgewählten Buchstaben aus der Liste letters der Großbuchstaben 'ABC...XYZ'

Übung 3

Zeichenketten bearbeiten

Zur Lösung

Aus der Tabelle Mitarbeiter sollen die Werte ID und Abteilung_ID zusammengesetzt werden. Dabei soll die ID immer 4-stellig und die Abteilung_ID immer 2-stellig geschrieben werden, bei Bedarf sollen die Teile mit '0' aufgefüllt werden.

Übung 4

Zeichenketten bearbeiten

Zur Lösung

Geben Sie für die Tabelle Mitarbeiter eine der vorhandenen Telefonnummern an – vorrangig die Mobilnummer; berücksichtigen Sie dabei auch, ob überhaupt eine Nummer gespeichert ist.

Übung 5

Zeichenketten bearbeiten

Zur Lösung

Zeigen Sie für die Spalte Kennzeichen der Tabelle Fahrzeug den zugehörigen Kreis an.

Übung 6

Zeichenketten bearbeiten

Zur Lösung

In der Beschreibung für den Schadensfall mit der ID 6 ist das Kennzeichen 'RE-LM 903' durch 'RE-LM 902' zu berichtigen.

Übung 7

Datum und Zeit bearbeiten

Zur Lösung

Die folgenden Teilaufgaben werden benutzt, um im Kapitel Testdaten erzeugen Geburtsdatum, Führerschein-Erwerb oder Abschluss des Versicherungsvertrags zufällig zu bestimmen.

1. Bestimmen Sie aus dem Geburtsdatum das Datum des 18. Geburtstags.
2. Bestimmen Sie (ausgehend vom 01.01.1950) Datumsangaben bis zum 31.12.1990, bei denen der Monat und das Jahr per Zufallsfunktion bestimmt werden.
3. Bestimmen Sie ebenso Datumsangaben, bei denen auch der Tag zufällig festgelegt wird und immer ein gültiges Datum erzeugt wird.
4. Prüfen Sie, ob ein neuer Kunde seinen Führerschein bei Vertragsabschluss bereits drei Jahre besitzt. Sie können annehmen, dass sowohl Fuehrerschein als auch Abschlussdatum in derselben Tabelle liegen; Schaltjahre können ignoriert werden.

Lösungen

Lösung zu Übung 1

Funktionen für Zahlen

Zur Übung

1. a. POWER(a, 2) + 2*a*b + POWER(b, 2)
   b. POWER(a + b, 2) als einfachste Binomische Formel
2. a. SQRT(216.09)
   b. POWER(216.09, 0.5)
3. a. IF ( SUM(Bestellung) > SUM(Zahlung) )
   b. IF( SIGN( SUM(Bestellung) – SUM(Zahlung) ) = 1)
4. ABS( SUM(Bestellung) – SUM(Zahlung) )

Lösung zu Übung 2

Zufallszahlen

Zur Übung

1. RAND()
2. RAND() * 4
3. 1 + RAND() * 4
4. FLOOR( 1 + RAND() * 4 )
5. FLOOR( 1 + RAND() * 25 )
6. SUBSTRING( letters FROM FLOOR( 1 + RAND() * 25 ) FOR 1 )

Lösung zu Übung 3

Zeichenketten bearbeiten

Zur Übung

SELECT LPAD( ID, 4, '0') + LPAD( Abteilung_ID, 2, '0') FROM Mitarbeiter;

Lösung zu Übung 4

Zeichenketten bearbeiten

Zur Übung

SELECT COALESCE( NULLIF(Mobil, ’’), Telefon) AS Tel FROM Mitarbeiter;

Erklärung: Wenn Mobil einen Wert enthält, kommt bei NULLIF dieser Wert heraus; andernfalls wird immer NULL geliefert – entweder als Feldinhalt oder als Ergebnis des Vergleichs mit der leeren Zeichenkette.

Lösung zu Übung 5

Zeichenketten bearbeiten

Zur Übung

SELECT SUBSTRING( Kennzeichen FROM 1 FOR Position('-', Kennzeichen) - 1) FROM Fahrzeug;

Lösung zu Übung 6

Zeichenketten bearbeiten

Zur Übung

UPDATE Schadensfall 
   SET Beschreibung = REPLACE( Beschreibung, 'RE-LM 903', 'RE-LM 902' )
 WHERE ID = 6;

Lösung zu Übung 7

Datum und Zeit bearbeiten

Zur Übung

Die Teilaufgaben liefern diese Einzellösungen.

1. DATEADD( YEAR, 18, Geburtsdatum )
2. DATEADD( MONTH, FLOOR(RAND()*11), DATEADD( YEAR, FLOOR(RAND()*40), '1950-01-01'))
   Es handelt sich um eine verschachtelte Funktion: Zuerst wird das Jahr neu bestimmt, es wird maximal ein Wert von 40 addiert. Zu diesem Ergebnis wird der Monat neu bestimmt, es wird maximal 11 addiert. In manchen Fällen sind einzelne Angaben per CAST genauer zu definieren.
3. DATEADD( DAY, FLOOR(RAND()*(41*365+10)), '1950-01-01')
   Es handelt sich um 41 Jahre zu je 365 Tagen, dazu 10 Schalttage.
4. SELECT Fuehrerschein, Abschlussdatum FROM (Vertrag/Kunde)
WHERE DATEDIFF( DAY, Fuehrerschein, Abschlussdatum) < 3*365
   Hinweis: DATEDIFF(YEAR...) ist nicht unbedingt geeignet, weil ein DBMS nicht die Termine, sondern die Jahreszahlen vergleichen könnte.

Siehe auch

Bei Wikipedia gibt es fachliche Erläuterungen:

• Potenzen
• Exponentialfunktion
• Trigonometrische Funktionen und Bogenmaß
• Betragsfunktion – der absolute Betrag
• Zufallszahl gibt auch Hinweise zu „Pseudozufallszahlen“.
• Binomische Formeln

---

WHERE-Klausel im Detail

In diesem Kapitel werden die Einzelheiten der WHERE-Klausel genauer behandelt. Diese Angaben sind vor allem für den SELECT-Befehl, aber auch für UPDATE und DELETE von Bedeutung.

Die Beispiele beziehen sich auf den Anfangsbestand der Beispieldatenbank; auf die Ausgabe der selektierten Datensätze wird verzichtet. Bitte probieren Sie alle Beispiele aus und nehmen Sie verschiedene Änderungen vor, um die Auswirkungen zu erkennen.

Allgemeine Hinweise

Die WHERE-Klausel ist (neben der Verknüpfung mehrerer Tabellen) der wichtigste Bestandteil des SELECT-Befehls: Je sorgfältiger die Auswahlbedingungen formuliert werden, desto genauer ist das Ergebnis der Abfrage.

Neben den hier erläuterten Varianten bietet jedes DBMS noch andere, z. B. STARTING WITH oder SIMILAR.

Anstelle konstanter Werte können auch passende Ausdrücke angegeben werden, z. B. Funktionen oder Unterabfragen.

Verwenden Sie bei den Beispielen möglichst immer auch die Umkehrung der Auswahl mit bzw. ohne NOT. Wie im Kapitel „Ausführliche SELECT-Struktur“ zur WHERE-Klausel angegeben, steht das NOT ggf. unmittelbar vor dem Parameter-Namen.

Eine einzelne Bedingung

Größenvergleich zweier Werte

Der einfachste Weg ist der direkte Vergleich zweier Werte, nämlich der Inhalt einer Spalte mit einem konstanten Wert. Dies ist möglich mit den folgenden Vergleichsoperatoren, und zwar für alle Datentypen, die verglichen werden können – Zahlen, Zeichenketten, Datumsangaben.

 =  <  >  <=  >=  <>

Beispiele:

Aufgabe

Suche einen Datensatz, bei dem der Wert in der Spalte ID gleich ist zu einem vorgegebenen Wert.

select * from Versicherungsnehmer
 where ID = 10;

Aufgabe

Suche Datensätze, bei denen der Name kleiner als 'B' ist, also mit 'A' anfängt.

select * from Versicherungsnehmer
 where Name < 'B';

Aufgabe

Suche Führerschein-Neulinge.

select * from Versicherungsnehmer
 where Fuehrerschein >= '01.01.2007';

Aufgabe

Suche Fahrzeugtypen mit kurzer Bezeichnung.

select * from Fahrzeugtyp
 where Char_Length(Bezeichnung) <= 3;

Bei diesen Vergleichen ist NOT zwar ebenfalls möglich; besser verständlich ist aber ein anderer passender Operator.

BETWEEN AND – Werte zwischen zwei Grenzen

Mit der Bedingung BETWEEN <wert1> AND <wert2> wird direkt mit einem Bereich verglichen; die Grenzwerte gehören meistens zum Bereich (abhängig vom DBMS). Auch dies ist möglich für Zahlen, Zeichenketten, Datumsangaben.

Aufgabe

Suche Datensätze, bei denen die PLZ außerhalb eines Bereichs 45000...45999 liegt.

select * from Versicherungsnehmer
 where PLZ NOT BETWEEN '45000' AND '45999';

LIKE – Ähnlichkeiten (1)

Die LIKE-Bedingung vergleicht Zeichenketten „ungenau“: Der gesuchte Text soll als Wert in einer Spalte enthalten sein; dazu werden „Wildcards“ benutzt: Der Unterstrich '_' steht für ein beliebiges einzelnes Zeichen, das an der betreffenden Stelle vorkommen kann. Das Prozentzeichen '%' steht für eine beliebige Zeichenkette mit 0 oder mehr Zeichen.

Diese Bedingung wird vor allem in zwei Situationen gerne benutzt:

• Der Suchbegriff ist sehr lang; dem Anwender soll es genügen, den Anfang einzugeben.
• Der Suchbegriff ist nicht genau bekannt (z. B. nicht richtig lesbar).

Beispiele:

Aufgabe

Der Ortsname beginnt nicht mit 'B'; der Inhalt dahinter ist beliebig.

select * from Versicherungsnehmer
 where Ort NOT LIKE 'B%';

Aufgabe

Der Ortsname enthält irgendwo 'alt' mit beliebigem Inhalt davor und dahinter.

select * from Versicherungsnehmer
 where Ort LIKE '%alt%';

Aufgabe

Der Anfangsbuchstabe des Namens ist unklar, aber danach folgen die Buchstaben 'ei' und noch etwas mehr.

select * from Versicherungsnehmer
 where Name LIKE '_ei%';

Ein Problem haben wir, wenn eines der Wildcard-Zeichen Teil des Suchbegriffs sein soll. Dann muss dem LIKE-Parameter mitgeteilt werden, dass '%' bzw. '_' als „echtes“ Zeichen zu verstehen ist. Das geschieht dadurch, dass ein spezielles Zeichen davor gesetzt wird und dieses Zeichen als „ESCAPE-Zeichen“ angegeben wird:

Aufgabe

Innerhalb der Beschreibung kommt die Zeichenfolge '10%' vor.

select * from Schadensfall
 where Beschreibung LIKE '%10\%%' ESCAPE '\';

Das erste und das letzte Prozentzeichen stehen dafür, dass vorher und nachher beliebige Inhalte möglich sind. Das mittlere Prozentzeichen wird mit dem Escape-Zeichen '\' verbunden und ist damit Teil der gesuchten Zeichenfolge. Diese Angabe '\%' ist als ein Zeichen zu verstehen.

Vergleichen Sie das Abfrageergebnis, wenn der ESCAPE-Parameter weggelassen wird oder wenn eines oder mehrere der Sonderzeichen im LIKE-Parameter fehlen.

CONTAINS u. a. – Ähnlichkeiten (2)

Ein Problem des LIKE-Parameters ist die Verwendung der Wildcard-Zeichen '%' und '_', die man gerne vergisst oder (wie im letzten Beispiel) nicht genau genug beachtet. Deshalb gibt es verschiedene Vereinfachungen.

CONTAINS – in Firebird CONTAINING – prüft, ob eine Zeichenkette im Feldinhalt enthalten ist.

select * from Schadensfall
 where Beschreibung CONTAINS '10%';

Teil der Beschreibung ist die Zeichenkette '10%'

Bitte prüfen Sie in der Beschreibung Ihres DBMS, welche Möglichkeiten für die Suche nach Ähnlichkeiten außerdem angeboten werden.

IS NULL – null-Werte prüfen

Wie schon bei den relationalen Datenbanken besprochen, haben NULL-Werte eine besondere Bedeutung. Mit den folgenden beiden Abfragen werden nicht alle Datensätze gefunden:

select ID, Name, Vorname, Mobil
  from Mitarbeiter
 where Mobil <> '';

8 Mitarbeiter mit Mobil-Nummer

select ID, Name, Vorname, Mobil
  from Mitarbeiter
 where Mobil = '';

10 Mitarbeiter ohne Mobil-Nummer

Nanu, es gibt doch 28 Mitarbeiter; wo sind die übrigen geblieben? Für diese Fälle gibt es mit IS NULL eine spezielle Abfrage:

select ID, Name, Vorname, Mobil
  from Mitarbeiter
 where Mobil is null;

10 Mitarbeiter ohne Angabe

Der Vollständigkeit halber sei darauf hingewiesen, dass die folgende Abfrage tatsächlich die richtige Gegenprobe liefert.

select ID, Name, Vorname, Mobil
  from Mitarbeiter
 where Mobil is not null;

18 Mitarbeiter mit irgendeiner Angabe (auch mit "leerer" Angabe)

Die folgende Abfrage liefert eine leere Ergebnismenge zurück, weil NULL eben kein Wert ist.

select ID, Name, Vorname, Mobil
  from Mitarbeiter
 where Mobil = null;

Es gibt keine einzelne Bedingung, die alle Datensätze ohne explizite Mobil-Angabe auf einmal angibt. Es gibt nur die Möglichkeit, die beiden Bedingungen "IS NULL" und "ist leer" zu verknüpfen:

select ID, Name, Vorname, Mobil
  from Mitarbeiter
 where ( Mobil is null ) or ( Mobil = '' );

20 Mitarbeiter ohne ausdrückliche Angabe

Beachten Sie auch bei "WHERE ... IS [NOT] NULL" die Bedeutung von NULL:

• Bei Zeichenketten ist zu unterscheiden zwischen dem „leeren“ String und dem NULL-Wert.
• Bei Zahlen ist zu unterscheiden zwischen der Zahl '0' (null) und dem NULL-Wert.
• Bei Datumsangaben ist zu unterscheiden zwischen einem vorhandenen Datum und dem NULL-Wert; ein Datum, das der Zahl 0 entspräche, gibt es nicht. (Man könnte allenfalls das kleinste mögliche Datum wie '01.01.0100' benutzen, aber dies ist bereits ein Datum.)

IN – genauer Vergleich mit einer Liste

Der IN-Parameter vergleicht, ob der Inhalt einer Spalte in der angegebenen Liste enthalten ist. Die Liste kann mit beliebigen Datentypen arbeiten.

Aufgabe

Hole die Liste aller Fahrzeuge, deren Typen als „VW-Kleinwagen“ registriert sind.

select * from Fahrzeug
 where Fahrzeugtyp_ID in (1, 2);

Aufgabe

Suche nach einem Unfall Fahrzeuge mit einer von mehreren möglichen Farben.

select * from Fahrzeug
 where Farbe in ('ocker', 'gelb');

Vor allem das erste Beispiel wird sehr oft mit einer Unterabfrage versehen; vergleichen Sie dazu auch den folgenden Abschnitt zu EXISTS.

Aufgabe

Hole die Liste aller Fahrzeuge vom Typ „Volkswagen“.

select * from Fahrzeug
 where Fahrzeugtyp_ID in 
     ( select ID from Fahrzeugtyp
        where Hersteller_ID = 1 );

Dabei wird zuerst mit der Unterabfrage eine Liste aller Fahrzeugtypen-IDs für den Hersteller 1 (= Volkswagen) zusammengestellt; diese wird dann für den Vergleich über den IN-Parameter benutzt.

EXISTS – schneller Vergleich mit einer Liste

Im Gegensatz zu den anderen Parametern der WHERE-Klausel arbeitet der EXISTS-Parameter nicht mit fest vorgegebenen Werten, sondern nur mit dem Ergebnis einer Abfrage, also einer Unterabfrage. Das letzte Beispiel zum IN-Parameter kann auch so formuliert werden:

Liste aller Fahrzeuge vom Typ 'Volkswagen'

select * from Fahrzeug fz
 where EXISTS
     ( select * from Fahrzeugtyp ft
        where ft.Hersteller_ID = 1
          and fz.Fahrzeugtyp_ID = ft.ID );

Zu jedem Datensatz aus der Tabelle Fahrzeug wird zu dieser Fahrzeugtyp_ID eine Unterabfrage aus den Fahrzeugtypen erstellt: Wenn es dort einen Datensatz mit passender ID und Hersteller-ID 1 (= Volkswagen) gibt, gehört der Fahrzeug-Datensatz zur Auswahl, andernfalls nicht.

Da Unterabfragen zuerst ausgeführt werden, wird eine EXISTS-Prüfung in aller Regel schneller erledigt als die entsprechende IN-Prüfung: Bei EXISTS handelt es sich um eine Feststellung „ist überhaupt etwas vorhanden“; bei IN dagegen muss ein exakter Vergleich mit allen Werten einer Liste durchgeführt werden. Bei unserer kleinen Beispieldatenbank spielt das natürlich keine Rolle, aber bei einer „echten“ Datenbank mit Millionen von Einträgen schon.

Mehrere Bedingungen verknüpfen

Bei der WHERE-Klausel geht es darum festzustellen, ob ein Datensatz Teil des Abfrageergebnisses ist oder nicht; bei der <search condition> handelt sich also um einen booleschen Ausdruck, d. h. einen Ausdruck, der einen der booleschen Werte WAHR oder FALSCH – TRUE bzw. FALSE – als Ergebnis hat. Nur bei einfachen Abfragen genügt dazu eine einzelne Bedingung; meistens müssen mehrere Bedingungen verknüpft werden (wie beim letzten Beispiel unter IS NULL).

Dazu gibt es die booleschen Operatoren NOT, AND, OR.

NOT als Negation

Dieser Operator kehrt das Ergebnis um: aus TRUE wird FALSE, aus FALSE wird TRUE.

Siehe oben: keine Führerschein-Neulinge

SELECT * FROM Versicherungsnehmer
 WHERE NOT (Fuehrerschein >= '01.01.2007');

AND als Konjunktion

Eine Bedingung, die durch eine AND-Verknüpfung gebildet wird, ist genau dann TRUE, wenn beide (bzw. alle) Bestandteile TRUE sind.

Die nach Alphabet erste Hälfte der Versicherungsnehmer eines PLZ-Bereichs

SELECT ID, Name, Vorname, PLZ, Ort 
  FROM Versicherungsnehmer
 WHERE PLZ BETWEEN '45000' AND '45999'
   AND Name < 'K';

OR als Adjunktion

Eine Bedingung, die durch eine OR-Verknüpfung gebildet wird, ist genau dann TRUE, wenn mindestens ein Bestandteil TRUE ist; dabei ist es gleichgültig, ob die anderen Bestandteile TRUE oder FALSE sind.

Die nach Alphabet erste Hälfte der Versicherungsnehmer und alle eines PLZ-Bereichs

SELECT ID, Name, Vorname, PLZ, Ort 
  FROM Versicherungsnehmer
 WHERE PLZ BETWEEN '45000' AND '45999'
    OR Name < 'K';

Bitte beachten Sie, dass der normale Sprachgebrauch „alle ... und alle ...“ sagt. Gemeint ist nach logischen Begriffen aber, dass <Bedingung 1> erfüllt sein muss ODER <Bedingung 2> ODER BEIDE.

XOR als Kontravalenz

Eine Bedingung, die durch eine XOR-Verknüpfung gebildet wird, ist genau dann TRUE, wenn ein Bestandteil TRUE ist, aber der andere Bestandteil FALSE ist – „ausschließendes oder“ bzw. „entweder – oder“. Diese Verknüpfung gibt es selten, z. B. bei MySQL; hier wird es der Vollständigkeit halber erwähnt.

MySQL-Version: Die nach Alphabet erste Hälfte der Versicherungsnehmer oder alle eines PLZ-Bereichs

SELECT ID, Name, Vorname, PLZ, Ort 
  FROM Versicherungsnehmer
 WHERE PLZ BETWEEN '45000' AND '45999'
   XOR Name < 'K';

Bitte beachten Sie, dass hier der normale Sprachgebrauch „oder“ sagt und „entweder – oder“ gemeint ist.

Anstelle von XOR kann immer eine Kombination verwendet werden:

( <Bedingung 1> AND ( NOT <Bedingung 2> ) ) OR ( <Bedingung 2> AND ( NOT <Bedingung 1> ) )

Klammern benutzen oder weglassen?

Bereits im Kapitel „Ausführliche SELECT-Struktur“ wurde die Hierarchie genannt:

• NOT ist die engste Verbindung und wird vorrangig ausgewertet.
• AND ist die nächststärkere Verbindung und wird danach ausgewertet.
• OR ist die schwächste Verbindung und wird zuletzt ausgewertet.
• Was in Klammern steht, wird vor allem anderen ausgewertet.

Bitte setzen Sie im folgenden Beispiel Klammern an anderen Stellen oder streichen Sie Klammern, und vergleichen Sie die Ergebnisse.

SELECT ID, Name, Vorname, PLZ, Ort 
  FROM Versicherungsnehmer
 WHERE not ( PLZ BETWEEN '45000' AND '45999'
             AND ( Name LIKE 'B%'
                OR Name LIKE 'K%'
                OR NOT Name CONTAINING 'ei'
                 )
           )
 order by PLZ, Name;

Sie werden ziemlich unterschiedliche Ergebnisse feststellen. Es empfiehlt sich deshalb, an allen sinnvollen Stellen Klammern zu setzen – auch dort, wo sie nicht erforderlich sind – und das, was zusammengehört, durch Einrückungen sinnvoll zu gliedern.

Zusammenfassung

In diesem Kapitel lernten wir neben dem Vergleich von Werten viele Möglichkeiten kennen, mit denen Bedingungen für Abfragen festgelegt werden können:

• Mit BETWEEN AND werden Werte innerhalb eines Bereichs geprüft.
• Mit LIKE und CONTAINS werden Werte gesucht, die mit vorgegebenen Werten teilweise übereinstimmen.
• Mit IS NULL werden null-Werte gesucht.
• Mit IN und EXISTS werden Spaltenwerte mit einer Liste verglichen.

Mit AND, OR, NOT werden Bedingungen zusammengefasst.

Übungen

Bei den folgenden Aufgaben kommt es nur auf die WHERE-Klausel an; Sie dürfen ein SELECT „mit allen Spalten“ benutzen.

Übung 1

Auswahl nach Zeichenketten

Zur Lösung

Suchen Sie alle Versicherungsnehmer, die folgenden Bedingungen entsprechen:

• Der erste Buchstabe des Nachnamens ist nicht bekannt, der zweite ist ein 'r'.
• Der Vorname enthält ein 'a'.
• Die Postleitzahl gehört zum Bereich Essen (PLZ 45...).

Übung 2

Auswahl nach Datumsbereich

Zur Lösung

Suchen Sie alle Versicherungsnehmer, die in den Jahren 1967 bis 1970 ihren 18. Geburtstag hatten.

Übung 3

Auswahl nach Ähnlichkeit

Zur Lösung

Zeigen Sie alle Schadensfälle an, bei denen in der Beschreibung auf eine prozentuale Angabe hingewiesen wird.

Übung 4

Auswahl für unbekannte Werte

Zur Lösung

Zeigen Sie alle Dienstwagen an, die keinem Mitarbeiter persönlich zugeordnet sind.

Hinweis: Die Prüfung „Mitarbeiter ohne Dienstwagen“ ist komplizierter; das dafür erforderliche OUTER JOIN wird erst später behandelt.

Übung 5

Bedingungen verknüpfen

Zur Lösung

Zeigen Sie alle Mitarbeiter der Abteilungen „Vertrieb“ (= 'Vert') und „Ausbildung“ (= 'Ausb') an.

Hinweis: Bestimmen Sie zunächst die IDs der gesuchten Abteilungen und benutzen Sie das Ergebnis für die eigentliche Abfrage.

Übung 6

Bedingungen verknüpfen

Zur Lösung

Gesucht werden die Versicherungsverträge für Haftpflicht (= 'HP') und Teilkasko (= 'TK'), die mindestens seit dem Ende des Jahres 1980 bestehen und aktuell nicht mit dem minimalen Prämiensatz berechnet werden.

Hinweis: Tragen Sie ausnahmsweise nur die notwendigen Klammern ein, nicht alle sinnvollen.

Lösungen

Lösung zu Übung 1

Auswahl nach Zeichenketten

Zur Übung

select * from Versicherungsnehmer
 where Name like '_r%' and Vorname like '%a%'
   and PLZ STARTING WITH '45'   /* oder: */
       PLZ like '45%';

Lösung zu Übung 2

Auswahl nach Datumsbereich

Zur Übung

select * from Versicherungsnehmer
 where DATEADD(YEAR, 18, Geburtsdatum) BETWEEN '01.01.1967' AND '31.12.1970';

Lösung zu Übung 3

Auswahl nach Ähnlichkeit

Zur Übung

SELECT * from Schadensfall
 where Beschreibung like '%\%%' escape '\';

Lösung zu Übung 4

Auswahl für unbekannte Werte

Zur Übung

SELECT * from Dienstwagen
 where Mitarbeiter_ID is null;

Lösung zu Übung 5

Bedingungen verknüpfen

Zur Übung

SELECT * from Mitarbeiter
 where Abteilung_ID in (
       select id from Abteilung
        where Kuerzel in ('Vert', 'Ausb') );

Lösung zu Übung 6

Bedingungen verknüpfen

Zur Übung

SELECT * from Versicherungsvertrag
 where (Art = 'HP' or Art = 'TK')
   and Abschlussdatum <= '31.12.1980'
   and (not Praemiensatz = 30)    /* oder */
   and Praemiensatz > 30;

Siehe auch

Dieses Kapitel verweist auf die folgenden Kapitel:

• Funktionen
• Unterabfragen
• Ausführliche SELECT-Struktur
• Relationale Datenbanken
• OUTER JOIN

Bei Wikipedia gibt es weitere fachliche Hinweise:

• Wildcards
• ESCAPE-Zeichen
• Boolesche Operatoren
  • Negation
  • Konjunktion
  • Adjunktion
  • Kontravalenz

---

Mehrere Tabellen

Ein besonderes Merkmal von relationalen Datenbanken und damit von SQL ist, dass die Informationen fast immer über mehrere Tabellen verteilt sind und bei Abfragen in der Ergebnismenge zusammengeführt werden müssen. Dieses Kapitel gibt einen Überblick über die Möglichkeiten dazu; Einzelheiten stehen in den folgenden Kapiteln.

Schreibweisen bei mehreren Tabellen

Bitte beachten Sie bei allen Befehlen, die mehrere Tabellen verwenden (das sind zwangsläufig nur SELECT-Befehle):

• Wenn ein Spaltenname in Bezug auf den gesamten SQL-Befehl eindeutig ist, genügt dieser Name.
• Wenn ein Spaltenname mehrfach vorkommt (wie ID), dann muss der Tabellenname vorangesetzt werden; der Spaltenname wird nach einem Punkt angefügt.

SELECT
  Personalnummer AS MitNr, 
  Name, Vorname, 
  Dienstwagen.ID, Kennzeichen, Fahrzeugtyp_ID AS Typ
FROM Mitarbeiter, Dienstwagen;

• Wegen der Übersichtlichkeit wird die Tabelle meistens auch dann bei jeder Spalte angegeben, wenn es wegen der ersten Regel nicht erforderlich wäre.

SELECT 
  Mitarbeiter.Personalnummer AS MitNr, 
  Mitarbeiter.Name, Mitarbeiter.Vorname, 
  Dienstwagen.ID AS DIW, Dienstwagen.Kennzeichen, Dienstwagen.Fahrzeugtyp_ID AS Typ
FROM Mitarbeiter, Dienstwagen;

• Anstelle des Namens einer Tabelle kann überall auch ein Tabellen-Alias benutzt werden; dieser muss einmal hinter ihrem Namen (in der FROM- oder in der JOIN-Klausel) angegeben werden.

SELECT 
  mi.Personalnummer AS MitNr, 
  mi.Name, mi.Vorname, 
  dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
FROM Mitarbeiter mi, Dienstwagen dw;

Alle diese Befehle für „Liste der Mitarbeiter mit Dienstwagen“ sind gleichwertig. Zu empfehlen ist die vollständige Schreibweise mit Alias wie im vorigen Beispiel.

Ein ähnlicher Befehl unter Verwendung der JOIN-Klausel sieht dann so aus:

SELECT 
  mi.Personalnummer AS MitNr, 
  mi.Name, mi.Vorname, 
  dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
FROM Mitarbeiter mi
     JOIN Dienstwagen dw on mi.ID = dw.Mitarbeiter_ID;

Der Alias ist nur für den betreffenden SQL-Befehl gültig. Ein und dieselbe Tabelle kann mal als 'a', dann als 'mi' oder auch als 'xyz' bezeichnet werden. Wegen des leichteren Verständnisses sind aussagefähige Kürzel sinnvoll; auch deshalb sind sie im Kapitel Tabellenstruktur der Beispieldatenbank angegeben.

Verknüpfung über WHERE – der traditionelle Weg

Beim einfachsten Verfahren, mehrere Tabellen gleichzeitig abzufragen, stehen alle Tabellen in der FROM-Klausel; die WHERE-Klausel enthält neben den Auswahlbedingungen auch Bedingungen zur Verknüpfung der Tabellen.

Einzelheiten werden in Einfache Tabellenverknüpfung behandelt.

JOINs - der moderne Weg

Beim „modernen“ Weg, mehrere Tabellen in einer gemeinsamen Abfrage zu verknüpfen, wird jede Tabelle in einer JOIN-Klausel aufgeführt; der ON-Parameter enthält die Verknüpfungsbedingung. Die WHERE-Klausel enthält nur die Auswahlbedingungen.

Die Einführung dazu wird in Arbeiten mit JOIN besprochen.

OUTER JOIN - auch null-Werte zurückgeben

Bei Abfragen mit einem „einfachen“ JOIN werden nicht alle Datensätze aufgeführt. Zeilen, zu denen es in der einen oder anderen Tabelle keine Verknüpfung gibt, fehlen im Ergebnis. Mit einem OUTER JOIN können auch solche „fehlenden“ Zeilen aufgeführt werden.

Einzelheiten dazu werden in OUTER JOIN behandelt.

Weitere Möglichkeiten von JOIN

Als SELF JOIN wird eine Tabelle mit sich selbst verknüpft.

Oft kommt es vor, dass man die Daten aus einer Tabelle erst bearbeiten möchte, bevor man sie mit einer anderen Tabelle verknüpft. Dazu gibt es die Möglichkeit einer „Inline-View“.

Diese Ergänzungen werden in Mehr zu JOIN besprochen.

Zusammenfassung

In diesem Kapitel erhielten wir Hinweise darauf, wie mehrere Tabellen verknüpft werden können:

• einfach über die FROM-Klausel und passende WHERE-Bedingungen
• übersichtlich über die JOIN-Klausel mit verschiedenen Varianten

Übungen

Übung 1

Was ist an diesem SELECT-Befehl falsch?

Zur Lösung

Zeigen Sie zu bestimmten Versicherungsverträgen die Daten der Fahrzeuge an.

select ID, Abschlussdatum, Art,
       vv.Kennzeichen, Farbe
  from Versicherungsvertrag vv, Fahrzeug
 where vv.Fahrzeug_ID = Fahrzeug.ID
   and Kennzeichen LIKE 'BO%';

Übung 2

Was ist an diesem SELECT-Befehl falsch?

Zur Lösung

Zeigen Sie zu einem Versicherungsvertrag die Daten des Versicherungsnehmers und des Sachbearbeiters an.

select ID, Vorname + ' ' + Name AS Kunde, Ort
       Name AS Sachbearbeiter, Telefon
  from Versicherungsvertrag, Versicherungsnehmer, Mitarbeiter
 where ID = 27
   and Versicherungsvertrag.Versicherungsnehmer_ID = Versicherungsnehmer.ID
   and Versicherungsvertrag.Mitarbeiter_ID = Mitarbeiter.ID;

Übung 3

Berichtigen Sie den folgenden SELECT-Befehl.

Zur Lösung

Zeigen Sie zu jedem Mitarbeiter die Daten seines Dienstwagens (Kennzeichen, Typ, Hersteller) an.

select ID, Name, Vorname,
       Kennzeichen, Bezeichnung, Name
  from Mitarbeiter mi, Dienstwagen dw,
       Fahrzeugtyp ft, Fahrzeughersteller fh
 where ID = dw.Mitarbeiter_ID
   and ID = dw.Fahrzeugtyp_ID
   and ID = ft.Hersteller_ID
 order by Name, Vorname;

Lösungen

Lösung zu Übung 1

Was ist an diesem SELECT-Befehl falsch?

Zur Übung

1. Die ID muss mit Tabellennamen oder Alias versehen sein, weil sie in beiden Tabellen enthalten ist.
2. Die Spalte Kennzeichen gehört zur Tabelle Fahrzeug, also ist der Alias vv falsch.

Lösung zu Übung 2

Was ist an diesem SELECT-Befehl falsch?

Zur Übung

1. Die ID muss sowohl in der Spaltenliste als auch in der WHERE-Klausel mit Tabellennamen oder Alias versehen sein, weil sie in allen Tabellen enthalten ist.
2. Gleiches gilt für Name und Vorname, weil diese Angaben in mehreren Tabellen enthalten sind.

Wenn (wie in den Anmerkungen zur Beispieldatenbank erwähnt) auch für die Kunden Kontaktdaten gespeichert wären, müsste das auch bei der Spalte Telefon beachtet werden. Für die Spalte Ort gilt das nicht, weil diese nicht zur Tabelle Mitarbeiter gehört, sondern zur Tabelle Abteilung, die hier nicht benutzt wird.

Lösung zu Übung 3

Berichtigen Sie den folgenden SELECT-Befehl.

Zur Übung

select mi.ID, mi.Name, mi.Vorname,
       dw.Kennzeichen, ft.Bezeichnung, fh.Name
  from Mitarbeiter mi, Dienstwagen dw,
       Fahrzeugtyp ft, Fahrzeughersteller fh
 where mi.ID = dw.Mitarbeiter_ID
   and ft.ID = dw.Fahrzeugtyp_ID
   and fh.ID = ft.Hersteller_ID
 order by mi.Name, mi.Vorname;

Siehe auch

Selfhtml: JOIN – eine andere Beschreibung und Beispielserie zum JOIN-Befehl.

---

Einfache Tabellenverknüpfung

Dieses Kapitel behandelt den „traditionellen“ Weg, mehrere Tabellen gleichzeitig abzufragen. Dazu werden in der FROM-Klausel alle Tabellen aufgeführt; die WHERE-Klausel enthält neben den Auswahlbedingungen auch Verknüpfungsbedingungen, wie die Tabellen zueinander gehören.

Alle Kombinationen aller Datensätze

Der einfachste Weg, Tabellen zu verknüpfen, ist ein Befehl wie der folgende, in dem verschiedene Spalten aus zwei Tabellen zusammengefasst werden. Aber das Ergebnis sieht reichlich seltsam aus.

select mi.Personalnummer as MitNr, 
       mi.Name, mi.Vorname, 
       dw.ID as DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID as Typ
  FROM Mitarbeiter mi, Dienstwagen dw;

Ausgabe

MITNR  NAME          VORNAME    DIW  KENNZEICHEN  Typ
-----  ------------  ---------  ---  -----------  ---
10001  Müller        Kurt         1  DO-WB 421     14
10002  Schneider     Daniela      1  DO-WB 421     14
20001  Meyer         Walter       1  DO-WB 421     14
20002  Schmitz       Michael      1  DO-WB 421     14
30001  Wagner        Gaby         1  DO-WB 421     14
30002  Feyerabend    Werner       1  DO-WB 421     14
40001  Langmann      Matthias     1  DO-WB 421     14
40002  Peters        Michael      1  DO-WB 421     14
/* usw. */
10001  Müller        Kurt         2  DO-WB 422     14
10002  Schneider     Daniela      2  DO-WB 422     14
20001  Meyer         Walter       2  DO-WB 422     14
20002  Schmitz       Michael      2  DO-WB 422     14
/* usw. */

Tatsächlich erzeugt dieser Befehl das „kartesische Produkt“ der beiden Tabellen: Jeder Datensatz der einen Tabelle wird (mit den gewünschten Spalten) mit jedem Datensatz der anderen Tabelle verbunden. Das sieht also so aus, als wenn alle Dienstwagen zu jedem Mitarbeiter gehören würden, was natürlich Quatsch ist.

Diese Variante ist also in aller Regel sinnlos (wenn auch syntaktisch korrekt). Nützlich ist sie nur dann, wenn auf einfachem Wege große Mengen von Testdaten erzeugt werden sollen, wie es im Kapitel Testdaten erzeugen benutzt wird.

Zwei Tabellen einfach verbinden

Sinnvoll wird die vorstehende Abfrage durch eine kleine Ergänzung. Was will man denn eigentlich wissen?

Gib mir (einige) Spalten der Tabelle Mitarbeiter zusammen mit (einigen) Spalten der Tabelle Dienstwagen, und zwar bei jedem Mitarbeiter denjenigen Dienstwagen, der zu diesem Mitarbeiter gehört.

Woran erkennt man, zu welchem Mitarbeiter ein Dienstwagen gehört? Nun, in der Tabelle Dienstwagen ist eine Spalte Mitarbeiter_ID enthalten; dieser Wert ist identisch mit der ID eines Eintrags in der Tabelle Mitarbeiter.

Wenn man diese Anfrage und diese Information in „Pseudocode“ übersetzt, dann kommt so etwas heraus:

 Hole Spalten der Tabelle Mitarbeiter
 sowie Spalten der Tabelle Dienstwagen
 wobei die Mitarbeiter_ID eines Dienstwagens gleich ist der ID eines Mitarbeiters

Das können wir nun in eine vollständige SQL-Abfrage übersetzen; die obige Abfrage muss nur minimal erweitert werden:

SELECT mi.Personalnummer AS MitNr,
       mi.Name, mi.Vorname,
       dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
  FROM Mitarbeiter mi, Dienstwagen dw
 WHERE dw.Mitarbeiter_ID = mi.ID
 order by MitNr;

Ausgabe

MITNR     NAME         VORNAME    DIW  KENNZEICHEN  TYP
100001    Grosser      Horst       10  DO-WB 4210    14
10001     Müller       Kurt         1  DO-WB 421     14
110001    Eggert       Louis       11  DO-WB 4211    14
120001    Carlsen      Zacharias   12  DO-WB 4212    14
20001     Meyer        Walter       2  DO-WB 422     14
30001     Wagner       Gaby         3  DO-WB 423     14
40001     Langmann     Matthias     4  DO-WB 424     14
50001     Pohl         Helmut       5  DO-WB 425     14
50002     Braun        Christian   14  DO-WB 352      2
50003     Polovic      Frantisek   15  DO-WB 353      3
50004     Kalman       Aydin       16  DO-WB 354      4
/* usw. */

Wir bekommen also tatsächlich genau diejenigen Mitarbeiter, die über einen (persönlichen) Dienstwagen verfügen.

Hinweis: Wundern Sie sich nicht über die seltsame Reihenfolge. Die Personalnummer wurde als VARCHAR definiert; also kommt das Ergebnis in alphabetischer und nicht in numerischer Reihenfolge.

Mehrere Tabellen verbinden

In der gleichen Weise können auch mehr als zwei Tabellen verknüpft werden. Im Kapitel Gruppierungen steht ein Beispiel ähnlich wie dieses:

Aufgabe

Gesucht wird für jeden Fahrzeughersteller (mit Angabe von ID und Name) und jedes Jahr die Summe der Schadenshöhe aus der Tabelle Schadensfall.

SELECT fh.ID AS Hersteller_ID,
       fh.Name AS Hersteller_Name,
       EXTRACT(YEAR FROM sf.Datum) AS Jahr,
       SUM(sf.Schadenshoehe) AS Schadenssumme
  FROM Schadensfall sf, Zuordnung_SF_FZ zu,
       Fahrzeug fz, Fahrzeugtyp ft, Fahrzeughersteller fh
 where sf.ID = zu.Schadensfall_ID
   and fz.ID = zu.Fahrzeug_ID
   and ft.ID = fz.Fahrzeugtyp_ID
   and fh.ID = ft.Hersteller_ID
 GROUP BY Hersteller_ID, Hersteller_Name, Jahr
 ORDER BY Jahr, Hersteller_ID;

Wichtig ist, dass es immer eine eindeutige Zuordnung zwischen jeweils einer Spalte einer Tabelle und einer Spalte einer anderen Tabelle gibt. Bitte beachten Sie dabei:

• Statt einer einzigen Spalte kann auch eine Gruppe von Spalten verknüpft werden (z. B. Name + Vorname). Dies macht aber alles umständlicher, unübersichtlicher und unsicherer. Deshalb sollte vorzugsweise über eindeutige IDs o. ä. verknüpft werden.
• Wenn es zwischen einzelnen Tabellen keine „gemeinsamen“ Spalten gibt, dann kommt wieder das kartesische Produkt heraus; das Ergebnis ist dann eher sinnlos.

Verknüpfungs- und Abfragebedingungen

Je mehr Kombinationen benötigt werden, desto unübersichtlicher wird diese Konstruktion. Dabei enthält die WHERE-Klausel bisher nur die Verknüpfungen zwischen den Tabellen, aber noch keine Suchbedingungen wie hier:

select ... from ... where ...
  and Jahr in [2006, 2007, 2008]
  and fhe.Land in ['Schweden', 'Norwegen', 'Finnland']
order by Jahr, Hersteller_ID;

Das führt außerdem dazu, dass die WHERE-Klausel sachlich gewünschte Suchbedingungen und logisch benötigte Verknüpfungsbedingungen vermischt. Wer soll da noch durchblicken? Besser ist das in den nächsten Kapiteln ausführlich behandelte Verfahren mit JOIN.

Zusammenfassung

Dieses Kapitel erläutert, wie mehrere Tabellen einfach durch die FROM-Klausel und passende WHERE-Bedingungen verknüpft werden können:

• In der Spaltenliste sollte immer der jeweilige Tabellenname angegeben werden; es kann auch ein Kürzel als Tabellen-Alias verwendet werden.
• In der FROM-Klausel werden alle Tabellen aufgelistet und in der WHERE-Klausel durch geeignete Bedingungen aufeinander bezogen.
• Durch die Vermischung zwischen Verknüpfungs- und Auswahlbedingungen wird dieses Verfahren schnell unübersichtlich.

Übungen

Bei den folgenden Abfragen beziehen wir uns auf den Bestand der Beispieldatenbank im „Anfangszustand“: die Tabellen Versicherungsvertrag, Fahrzeug, Mitarbeiter mit jeweils etwa 28 Einträgen und Versicherungsnehmer mit etwa 26 Einträgen.

Übung 1

Eine einfache Abfrage

Zur Lösung

Erstellen Sie eine Abfrage zur Tabelle Versicherungsvertrag, die nur die wichtigsten Informationen (einschließlich der IDs auf andere Tabellen) enthält. Wie viele Einträge zeigt die Ergebnismenge an?

Übung 2

Das kartesische Produkt

Zur Lösung

Erweitern Sie die Abfrage von Aufgabe 1, sodass anstelle der Versicherungsnehmer_ID dessen Name und Vorname angezeigt werden, und verzichten Sie auf eine WHERE-Klausel. Wie viele Einträge zeigt die Ergebnismenge an?

Übung 3

Das kartesische Produkt

Zur Lösung

Erweitern Sie die Abfrage von Aufgabe 2, sodass anstelle der Fahrzeug_ID das Kennzeichen und anstelle der Mitarbeiter_ID dessen Name und Vorname angezeigt werden, und verzichten Sie auf eine WHERE-Klausel. Wie viele Einträge zeigt die Ergebnismenge an?

Übung 4

Sinnvolle Verknüpfung von Tabellen

Zur Lösung

Erweitern Sie die Abfrage von Aufgabe 2, sodass Name und Vorname des Versicherungsnehmers genau zu einem jeden Vertrag passen. Wie viele Einträge zeigt die Ergebnismenge an?

Übung 5

Sinnvolle Verknüpfung von Tabellen

Zur Lösung

Erweitern Sie die Abfrage von Aufgabe 3, sodass Name und Vorname des Mitarbeiters sowie das Fahrzeug-Kennzeichen genau zu einem jeden Vertrag passen. Wie viele Einträge zeigt die Ergebnismenge an?

Übung 6

Sinnvolle Verknüpfung von Tabellen

Zur Lösung

Erweitern Sie die Abfrage von Aufgabe 5, sodass die ausgewählten Zeilen den folgenden Bedingungen entsprechen:

• Es geht ausschließlich um Eigene Kunden.
• Vollkasko-Verträge sollen immer angezeigt werden, ebenso Fahrzeuge aus dem Kreis Recklinghausen 'RE'.
• Teilkasko-Verträge sollen angezeigt werden, wenn sie nach 1990 abgeschlossen wurden.
• Haftpflicht-Verträge sollen angezeigt werden, wenn sie nach 1985 abgeschlossen wurden.

Wie viele Einträge zeigt die Ergebnismenge an?

Lösungen

Lösung zu Übung 1

Eine einfache Abfrage

Zur Übung

SELECT Vertragsnummer, Abschlussdatum, Art, 
       Versicherungsnehmer_ID, Fahrzeug_ID, Mitarbeiter_ID
  from Versicherungsvertrag

Es werden 28 Zeilen angezeigt.

Lösung zu Übung 2

Das kartesische Produkt

Zur Übung

SELECT vv.Vertragsnummer, vv.Abschlussdatum, vv.Art,
       vn.Name, vn.Vorname,
       Fahrzeug_ID,
       Mitarbeiter_ID
  from Versicherungsvertrag vv, Versicherungsnehmer vn;

Es werden etwa 728 Zeilen angezeigt.

Lösung zu Übung 3

Das kartesische Produkt

Zur Übung

SELECT vv.Vertragsnummer, vv.Abschlussdatum, vv.Art,
       vn.Name, vn.Vorname,
       fz.Kennzeichen,
       mi.Name, mi.Vorname
  from Versicherungsvertrag vv, Versicherungsnehmer vn,
       Fahrzeug fz, Mitarbeiter mi;

Es werden etwa 570 752 Zeilen angezeigt.

Lösung zu Übung 4

Sinnvolle Verknüpfung von Tabellen

Zur Übung

SELECT vv.Vertragsnummer, vv.Abschlussdatum, vv.Art,
       vn.Name, vn.Vorname,
       Fahrzeug_ID,
       Mitarbeiter_ID
  from Versicherungsvertrag vv, Versicherungsnehmer vn
 where vn.ID = vv.Versicherungsnehmer_ID;

Es werden etwa 28 Zeilen angezeigt.

Lösung zu Übung 5

Sinnvolle Verknüpfung von Tabellen

Zur Übung

SELECT vv.Vertragsnummer, vv.Abschlussdatum, vv.Art,
       vn.Name, vn.Vorname,
       fz.Kennzeichen,
       mi.Name, mi.Vorname
  from Versicherungsvertrag vv, Versicherungsnehmer vn,
       Fahrzeug fz, Mitarbeiter mi
 where vn.ID = vv.Versicherungsnehmer_ID
   and fz.ID = vv.Fahrzeug_ID
   and mi.ID = vv.Mitarbeiter_ID;

Es werden etwa 28 Zeilen angezeigt.

Lösung zu Übung 6

Sinnvolle Verknüpfung von Tabellen

Zur Übung

SELECT vv.Vertragsnummer, vv.Abschlussdatum, vv.Art,
       vn.Name, vn.Vorname,
       fz.Kennzeichen,
       mi.Name, mi.Vorname
  from Versicherungsvertrag vv, Versicherungsnehmer vn,
       Fahrzeug fz, Mitarbeiter mi
 where vn.ID = vv.Versicherungsnehmer_ID
   and fz.ID = vv.Fahrzeug_ID
   and mi.ID = vv.Mitarbeiter_ID
   and vn.Eigener_kunde = 'J'
   and (  ( vv.Art = 'HP' and vv.Abschlussdatum > '31.12.1985' )
       or ( vv.Art = 'TK' and vv.Abschlussdatum > '31.12.1990' )
       OR ( vv.Art = 'VK' )
       or ( fz.Kennzeichen STARTING WITH 'RE-' ) );

Es werden etwa 19 Zeilen angezeigt. Die OR-Verknüpfungen könnten teilweise auch mit CASE geschrieben werden.

Siehe auch

Bei Wikipedia stehen weitere Hinweise:

• Kartesisches Produkt
• Pseudocode

---

Arbeiten mit JOIN

Dieses Kapitel enthält die Einführung in die Variante mit JOIN, mit der mehrere Tabellen verknüpft werden können. Dazu wird jede der in Betracht kommenden Tabellen in einer JOIN-Klausel aufgeführt; der ON-Parameter enthält die Verknüpfungsbedingung. Die WHERE-Klausel enthält „nur“ die Auswahlbedingungen.

Die Syntax von JOIN

Um Tabellen sinnvoll miteinander zu verknüpfen (= verbinden, engl. join), wurde die JOIN-Klausel für den SELECT-Befehl mit folgender Syntax eingeführt.

SELECT <spaltenliste>
  FROM <haupttabelle>
  [<join-typ>] JOIN <verknüpfte tabelle> ON <bedingung>

Als <join-typ> stehen zur Verfügung:

• [INNER] JOIN, auch Equi-Join genannt, ist eine Verknüpfung innerhalb zweier Tabellen, d. h. ein Teil des kartesischen Produkts, bei dem ein Wert in beiden Tabellen vorhanden ist. INNER JOIN ist der Inhalt dieses Kapitels.
• OUTER JOIN bezeichnet Verknüpfungen, bei denen auch Datensätze geliefert werden, für die eine Vergleichsbedingung nicht erfüllt ist.
  • LEFT JOIN, RIGHT JOIN, FULL JOIN bezeichnen Spezialfälle von OUTER JOIN, je nachdem in welcher Tabelle ein gesuchter Wert fehlt.

OUTER JOIN wird im nächsten Kapitel behandelt.

Einige Sonderfälle und Ergänzungen zu JOIN werden im Kapitel Mehr zu JOIN behandelt.

Als <bedingung> wird normalerweise nur eine Übereinstimmung (also eine Gleichheit) zwischen zwei Tabellen geprüft, auch wenn jede Kombination von Bedingungen erlaubt ist. Genauer: es geht um die Gleichheit von Werten je einer Spalte in zwei Tabellen. (Zwei Beispiele für andere Übereinstimmungen lernen Sie in „Mehr zu JOIN“ kennen.)

Auch mehrere Verknüpfungen sind möglich, entweder direkt hintereinander:

SELECT <spaltenliste>
  FROM <haupttabelle>
  [<join-typ>] JOIN <zusatztabelle1> ON <bedingung1>
  [<join-typ>] JOIN <zusatztabelle2> ON <bedingung2>
  [<join-typ>] JOIN <zusatztabelle3> ON <bedingung3>

oder durch Klammern gegliedert:

SELECT <spaltenliste>
  FROM <haupttabelle>
  [<join-typ>] JOIN 
     ( <zusatztabelle1>
         [<join-typ>] JOIN 
            ( <zusatztabelle2> 
                [<join-typ>] JOIN <zusatztabelle3> ON <bedingung3>
            ) ON <bedingung2>
     ) ON <bedingung1>

Bitte beachten Sie dabei genau, wo und wie die Klammern und die dazugehörigen ON-Bedingungen gesetzt werden. Beide Varianten können unterschiedliche Ergebnisse liefern – abhängig vom JOIN-Typ und dem Zusammenhang zwischen den Tabellen.

Alle diese Möglichkeiten werden in den nächsten Abschnitten und Kapiteln genauer erläutert.

INNER JOIN von zwei Tabellen

Aufgabe

Das Beispiel „alle Mitarbeiter mit den zugehörigen Dienstwagen“ aus dem vorigen Kapitel benötigt nur geringe Änderungen.

select mi.Personalnummer as MitNr, 
       mi.Name, mi.Vorname, 
       dw.ID, dw.Kennzeichen, dw.Fahrzeugtyp_ID as Typ
  from Mitarbeiter mi
       join Dienstwagen dw on dw.Mitarbeiter_ID = mi.ID
 ORDER BY MitNr;

Das Ergebnis dieser Abfrage ist identisch mit der Liste im vorigen Kapitel; wir verzichten deshalb auf die erneute Ausgabe.

Die zweite Tabelle wird in die JOIN-Klausel verschoben, die Verknüpfungsbedingung in den ON-Parameter – fertig.

WHERE-Klausel bei JOINs

Eine solche Abfrage kann wie üblich durch eine WHERE-Klausel eingeschränkt werden. Eine Suchbedingung auf die verknüpfte Tabelle Dienstwagen kann wahlweise in der WHERE-Klausel oder in der JOIN-Klausel stehen. In den beiden folgenden Beispielen geht es nur um die Dienstwagen von Mercedes. Die Information, welche Typen zu Mercedes gehören, kommt über eine Unterabfrage, die ebenfalls einen JOIN verwendet und die in Klammern gesetzt ist.

Aufgabe

Suche die Dienstwagen vom Typ Mercedes.

 Zulässig, aber nicht so schön, weil Verknüpfungsbedingung und Auswahlbedingung vermischt werden

select mi.Personalnummer as MitNr, 
       mi.Name, mi.Vorname, 
       dw.ID, dw.Kennzeichen, dw.Fahrzeugtyp_ID as Typ
  from Mitarbeiter mi
       join Dienstwagen dw 
         on mi.ID = dw.Mitarbeiter_ID 
        and dw.Fahrzeugtyp_ID in ( SELECT ft.ID
                                  from Fahrzeugtyp ft
                                       join Fahrzeughersteller fh 
                                         on ft.Hersteller_ID = fh.ID
                                        and fh.Name = 'Mercedes-Benz' );

 Besseres Vorgehen, weil die Auswahlbedingungen als solche direkt zu erkennen sind

select mi.Personalnummer as MitNr, 
       mi.Name, mi.Vorname, 
       dw.ID, dw.Kennzeichen, dw.Fahrzeugtyp_ID as Typ
  from Mitarbeiter mi
       join Dienstwagen dw 
         on mi.ID = dw.Mitarbeiter_ID 
 where dw.Fahrzeugtyp_ID in ( SELECT ft.ID
                                from Fahrzeugtyp ft
                                     join Fahrzeughersteller fh
                                       on ft.Hersteller_ID = fh.ID
                               where fh.Name = 'Mercedes-Benz');

Natürlich sind Einschränkungen auf beide Tabellen möglich:

Aufgabe

Gesucht werden Mitarbeiter mit 'M' und Mercedes als Dienstwagen.

select mi.Personalnummer as MitNr, 
       mi.Name, mi.Vorname, 
       dw.ID, dw.Kennzeichen, dw.Fahrzeugtyp_ID as Typ
  from Mitarbeiter mi
       join Dienstwagen dw 
         on mi.ID = dw.Mitarbeiter_ID 
 where dw.Fahrzeugtyp_ID in ( SELECT ft.ID
                                from Fahrzeugtyp ft
                                     join Fahrzeughersteller fh
                                       on ft.Hersteller_ID = fh.ID
                               where fh.Name = 'Mercedes-Benz')
   and mi.Name like 'M%';

Ausgabe

MITNR   NAME       VORNAME  ID  KENNZEICHEN  TYP
------  ---------  -------  --  -----------  ---
10001   Müller     Kurt      1  DO-WB 421     14
20001   Meyer      Walter    2  DO-WB 422     14

Bei diesem Beispiel wird sofort deutlich, welche Bedingungen die Verknüpfung und welche Bedingungen die Auswahl bezeichnen. Auf diese Übersichtlichkeit sollten Sie immer achten.

Übrigens gibt es keine allgemeine Regel, was als Haupttabelle und was als verknüpfte Tabelle zu verwenden ist. In den bisherigen Beispielen können die beiden Tabellen ohne weiteres vertauscht werden:

select mi.Personalnummer as MitNr, 
       mi.Name, mi.Vorname, 
       dw.ID, dw.Kennzeichen, dw.Fahrzeugtyp_ID as Typ
  from Dienstwagen dw 
       join Mitarbeiter mi
         on mi.ID = dw.Mitarbeiter_ID 
 where dw.Fahrzeugtyp_ID in ( SELECT ft.ID
                                from Fahrzeugtyp ft
                                     join Fahrzeughersteller fh
                                       on ft.Hersteller_ID = fh.ID
                               where fh.Name = 'Mercedes-Benz') 
   and mi.Name like 'M%';

Mitarbeiter mit 'M' und Mercedes als Dienstwagen

Die Haupttabelle kann nach folgenden Überlegungen gewählt werden:

• Es sollte die Tabelle sein, die die „wichtigste“ bei der Abfrage ist.
• Es sollte diejenige mit den größten Einschränkungen sein; das beschleunigt die Abfrage besonders stark.

INNER JOIN mehrerer Tabellen

Dazu nehmen wir wiederum das komplexe Beispiel aus dem vorigen Kapitel, das bei den Gruppierungen genauer besprochen wird. In diesem Fall spielt die Reihenfolge der JOIN-Klauseln eher keine Rolle, weil es sich sowieso um direkte Übereinstimmungen handelt und nur solche Datensätze benutzt werden, die es zu den betreffenden Werten tatsächlich gibt.

Aufgabe

Gesucht wird für jeden Fahrzeughersteller (mit Angabe von ID und Name) und jedes Jahr die Summe der Schadenshöhe aus der Tabelle Schadensfall.

SELECT fh.ID AS Hersteller_ID,
       fh.Name AS Hersteller_Name,
       EXTRACT(YEAR FROM sf.Datum) AS Jahr,
       SUM(sf.Schadenshoehe) AS Schadenssumme
  FROM Schadensfall   sf
       JOIN Zuordnung_SF_FZ    zu ON sf.ID = zu.Schadensfall_ID
       JOIN Fahrzeug           fz ON fz.ID = zu.Fahrzeug_ID
       JOIN Fahrzeugtyp        ft ON ft.ID = fz.Fahrzeugtyp_ID
       JOIN Fahrzeughersteller fh ON fh.ID = ft.Hersteller_ID
 GROUP BY Hersteller_ID, Hersteller_Name, Jahr
 ORDER BY Jahr, Hersteller_ID;

Übrigens ist es zulässig, den „traditionellen“ Weg mit mehreren Tabellen in der FROM-Klausel und den „modernen“ Weg über JOIN zu mischen. Wenn Sie in einem Ausnahmefall wirklich so vorgehen wollen, sollten Sie erst recht genau auf die Übersichtlichkeit und den Zusammenhang der Bedingungen achten. Der Autor dieses Hinweises kann sich keine passende Situation vorstellen, aber vielleicht ist es auch einmal sinnvoll.

Zusammenfassung

In diesem Kapitel lernten Sie die Verknüpfung von Tabellen über die JOIN-Klausel kennen.

• Mit einem INNER JOIN werden Datensätze abgefragt, bei denen ein Wert in je einer Spalte beider Tabellen vorhanden ist.
• In der ON-Klausel steht diese Verknüpfungsbedingung.
• In der WHERE-Klausel stehen die „normalen“ Auswahlbedingungen.

Genauso können mehrere Tabellen verknüpft werden.

Übungen

Übung 1

Definition von JOINs

Zur Lösung

Welche der folgenden Aussagen sind wahr, welche falsch, welche sinnvoll?

1. Der INNER JOIN liefert das kartesische Produkt zwischen den Tabellen.
2. LEFT JOIN ist ein Spezialfall von OUTER JOIN.
3. Für einen JOIN ist ON Fahrzeug.ID >= Versicherungsvertrag.Fahrzeug_ID eine zulässige Verknüpfungsbedingung.
4. Eine Einschränkung auf die mit JOIN verknüpfte Tabelle gehört in die ON-Klausel:

... FROM Zuordnung_SF_FZ zu
         JOIN Schadensfall sf
           ON sf.ID = zu.Schadensfall_ID AND EXTRACT(YEAR from sf.Datum) = 2008;

Übung 2

Definition von JOINs

Zur Lösung

Erläutern Sie, was am folgenden Befehl falsch oder äußerst ungünstig ist. Es handelt sich um diese Abfrage:

Aufgabe

Gesucht sind die Schadensfälle des Jahres 2008. Zu jedem Schadensfall sind die beteiligten Fahrzeuge, der Schadensanteil sowie die Versicherungsdaten des Fahrzeugs (einschließlich Name des Halters) anzugeben.

SELECT Datum, SUBSTRING(Ort from 1 for 30) as Ort, Schadenshoehe,
       zu.Schadenshoehe,
       fz.Kennzeichen,
       Vertragsnummer as Vertrag, Abschlussdatum, Art,
       vn.Name as VN-Name, vn.Vorname as VN-Vorname
  from Schadensfall sf
       join Zuordnung_SF_FZ      zu on ID = zu.Schadensfall_ID
       join Fahrzeug             fz on ID = zu.Fahrzeug_ID
       join Versicherungsnehmer  vn on ID = vv.Versicherungsnehmer_ID
       join Versicherungsvertrag vv on vv.Fahrzeug_ID = zu.Fahrzeug_ID
 where EXTRACT(YEAR from Datum) = 2008
 order by Schadensfall_ID, Fahrzeug_ID;

Die folgenden Aufgaben entsprechen teilweise Aufgaben aus dem Kapitel „Einfache Tabellenverknüpfung“. Sie sollen jetzt an den passenden Stellen JOINs verwenden, anstatt die Tabellen einfach aufzulisten.

Übung 3

Sinnvolle Verknüpfung von Tabellen

Zur Lösung

Erstellen Sie eine Abfrage zur Tabelle Versicherungsvertrag mit den wichtigsten Informationen (einschließlich der IDs auf andere Tabellen). Beim Versicherungsnehmer sollen dessen Name und Vorname angezeigt werden. Es werden nur Verträge ab 1990 gesucht.

Übung 4

Sinnvolle Verknüpfung von Tabellen

Zur Lösung

Erweitern Sie die Abfrage von Aufgabe 3, sodass Name und Vorname des Mitarbeiters sowie das Fahrzeug-Kennzeichen eines jeden Vertrags angezeigt werden.

Übung 5

Sinnvolle Verknüpfung von Tabellen

Zur Lösung

Ändern Sie die Abfrage von Aufgabe 4 so, dass die ausgewählten Zeilen den folgenden Bedingungen entsprechen:

• Es geht ausschließlich um Eigene Kunden.
• Vollkasko-Verträge sollen immer angezeigt werden, ebenso Fahrzeuge aus dem Kreis Recklinghausen 'RE'.
• Teilkasko-Verträge sollen angezeigt werden, wenn sie nach 1990 abgeschlossen wurden.
• Haftpflicht-Verträge sollen angezeigt werden, wenn sie nach 1985 abgeschlossen wurden.

Lösungen

Lösung zu Übung 1

Definition von JOINs

Zur Übung

1. Falsch; es liefert einen Teil des kartesischen Produkts, der durch die ON-Bedingung bestimmt wird.
2. Richtig.
3. Diese Bedingung ist zulässig, aber nicht sinnvoll. JOIN-ON passt in der Regel nur für Gleichheiten.
4. Diese Bedingung ist zulässig. Besser ist es aber, eine Einschränkung der Auswahl in die WHERE-Klausel zu setzen.

Lösung zu Übung 2

Definition von JOINs

Zur Übung

Richtig ist beispielsweise die folgende Version. Als Haupttabelle wurde wegen der WHERE-Klausel die Tabelle Schadensfall gewählt; wegen der Reihenfolge der Verknüpfungen wäre auch Zuordnung_SF_FZ als Haupttabelle geeignet.

SELECT sf.Datum, SUBSTRING(sf.Ort from 1 for 30) as Ort, sf.Schadenshoehe,
       zu.Schadenshoehe as Teilschaden,
       fz.Kennzeichen,
       vv.Vertragsnummer as Vertrag, vv.Abschlussdatum, vv.Art,
       vn.Name as VN_Name, vn.Vorname as VN_Vorname
  from Schadensfall sf
       join Zuordnung_SF_FZ      zu on sf.ID = zu.Schadensfall_ID
       join Fahrzeug             fz on fz.ID = zu.Fahrzeug_ID
       join Versicherungsvertrag vv on fz.ID = vv.Fahrzeug_ID
       join Versicherungsnehmer  vn on vn.ID = vv.Versicherungsnehmer_ID
 where EXTRACT(YEAR from sf.Datum) = 2008
 order by zu.Schadensfall_ID, zu.Fahrzeug_ID;

Die Variante aus der Aufgabenstellung enthält folgende Problemstellen:

• Zeile 1: Der Tabellen-Alias sf fehlt bei Schadenshoehe und bei Ort. Bei Datum fehlt er auch, aber das ist kein Problem, weil es diese Spalte nur bei dieser Tabelle gibt.
• Zeile 2: Diese Spalte sollte einen Spalten-Alias bekommen wegen der abweichenden Bedeutung zu sf.Schadenshoehe.
• Zeile 4: Es ist schöner, auch hier mit einem Tabellen-Alias zu arbeiten.
• Zeile 5: Der Bindestrich in der Bezeichnung des Spalten-Alias wird nicht bei allen DBMS akzeptiert.
• Zeile 7, 8, 9: Zur Spalte ID ist jeweils die Tabelle anzugeben, ggf. mit dem Alias. Die JOIN-ON-Bedingung bezieht sich nicht automatisch auf diese Spalte und diese Tabelle.
• Zeile 9, 10: In Zeile 9 ist die Tabelle Versicherungsvertrag vv noch nicht bekannt. Wegen der Verknüpfungen ist zuerst Zeile 10 zu verwenden, danach Zeile 9. Die Verknüpfung über vv.Fahrzeug_ID = zu.Fahrzeug_ID ist nicht glücklich (wenn auch korrekt); besser ist der Bezug auf die direkt zugeordnete Tabelle Fahrzeug und deren PrimaryKey, nämlich ID.
• Zeile 11: Es ist klarer, auch hier den Tabellen-Alias sf zu verwenden.
• Zeile 12: Der Tabellen-Alias zu fehlt bei beiden Spalten. Bei Fahrzeug_ID ist er erforderlich (doppelte Verwendung bei vv), bei Schadensfall_ID sinnvoll.

Lösung zu Übung 3

Sinnvolle Verknüpfung von Tabellen

Zur Übung

SELECT Vertragsnummer, Abschlussdatum, Art,
       Name, Vorname,
       Fahrzeug_ID,
       Mitarbeiter_ID
  from Versicherungsvertrag vv
       join Versicherungsnehmer vn on vn.ID = vv.Versicherungsnehmer_ID
 where vv.Abschlussdatum >= '01.01.1990';

Lösung zu Übung 4

Sinnvolle Verknüpfung von Tabellen

Zur Übung

SELECT vv.Vertragsnummer as Vertrag, vv.Abschlussdatum, vv.Art,
       vn.Name as VN_Name, vn.Vorname as VN_Vorname,
       fz.Kennzeichen,
       mi.Name as MI_Name, mi.Vorname as MI_Vorname
  from Versicherungsvertrag vv
       join Versicherungsnehmer vn on vn.ID = vv.Versicherungsnehmer_ID
       join Fahrzeug            fz on fz.ID = vv.Fahrzeug_ID
       join Mitarbeiter         mi on mi.ID = vv.Mitarbeiter_ID
 where vv.Abschlussdatum >= '01.01.1990';

Lösung zu Übung 5

Sinnvolle Verknüpfung von Tabellen

Zur Übung

SELECT vv.Vertragsnummer as Vertrag, vv.Abschlussdatum, vv.Art,
       vn.Name as VN_Name, vn.Vorname as VN_Vorname,
       fz.Kennzeichen,
       mi.Name as MI_Name, mi.Vorname as MI_Vorname
  from Versicherungsvertrag vv
       join Versicherungsnehmer vn on vn.ID = vv.Versicherungsnehmer_ID
       join Fahrzeug            fz on fz.ID = vv.Fahrzeug_ID
       join Mitarbeiter         mi on mi.ID = vv.Mitarbeiter_ID
 where vn.Eigener_kunde = 'J'
   and (  ( vv.Art = 'HP' and vv.Abschlussdatum > '31.12.1985' )
       or ( vv.Art = 'TK' and vv.Abschlussdatum > '31.12.1990' )
       OR ( vv.Art = 'VK' )
       or ( fz.Kennzeichen STARTING WITH 'RE-' ) );

Siehe auch

In diesem Kapitel werden Sachverhalte der folgenden Themen angesprochen:

• Einfache Tabellenverknüpfung
• Unterabfragen
• Gruppierungen

---

OUTER JOIN

Bei den Abfragen im vorigen Kapitel nach „alle Mitarbeiter und ihre Dienstwagen“ werden nicht alle Mitarbeiter aufgeführt, weil in der Datenbank nicht für alle Mitarbeiter ein Dienstwagen registriert ist. Ebenso gibt es einen Dienstwagen, der keinem bestimmten Mitarbeiter zugeordnet ist.

Mit einem OUTER JOIN werden auch Mitarbeiter ohne Dienstwagen oder Dienstwagen ohne Mitarbeiter aufgeführt.

Die Syntax von OUTER JOIN

Die Syntax entspricht derjenigen von JOIN allgemein. Wegen der speziellen Bedeutung sind die Tabellen nicht gleichberechtigt, sondern werden begrifflich unterschieden:

SELECT <spaltenliste>
  FROM <linke tabelle>
  [<join-typ>] JOIN <rechte tabelle> ON <bedingung>

Als Spezialfälle des OUTER JOIN gibt es die JOIN-Typen LEFT JOIN, RIGHT JOIN, FULL JOIN.

Anstelle von <haupttabelle> und <zusatztabelle> wird bei OUTER JOIN von <linke tabelle> und <rechte tabelle> gesprochen, weil diese Tabellen unterschiedlich behandelt werden.

Allgemeine Hinweise zu OUTER JOIN

Das Wort OUTER kann entfallen und wird üblicherweise nicht benutzt, weil durch die Begriffe LEFT, RIGHT, FULL bereits ein OUTER JOIN gekennzeichnet wird.

Die Begriffe <linke tabelle> und <rechte tabelle> beziehen sich auf die beiden Tabellen bezüglich der normalen Lesefolge: Wir lesen von links nach rechts, also ist die unter FROM genannte Tabelle die <linke Tabelle> (bisher <Haupttabelle> genannt) und die unter JOIN genannte Tabelle die <rechte Tabelle> (bisher <Zusatztabelle> genannt). Bei Verknüpfungen mit mehreren Tabellen ist ebenfalls die unter JOIN genannte Tabelle die <rechte Tabelle>; die unmittelbar vorhergehende Tabelle ist die <linke Tabelle>.

Auch wenn die folgenden Beispiele so aussehen, als wenn die Datensätze sinnvoll sortiert wären, ist das Zufall; bitte denken Sie daran, dass SQL unsortierte Datenmengen liefert. Eine bestimmte Reihenfolge erhalten Sie erst durch ORDER BY.

Die Anzeige der Ergebnismengen bei den Beispielen ist in der Regel nur ein Auszug des vollständigen Ergebnisses.

LEFT OUTER JOIN

Dieser JOIN liefert alle Datensätze der linken Tabelle, ggf. unter Berücksichtigung der WHERE-Klausel. Aus der rechten Tabelle werden nur diejenigen Datensätze übernommen, die nach der Verknüpfungsbedingung passen.

SELECT <spaltenliste>
  FROM <linke Tabelle>
       LEFT [OUTER] JOIN <rechte Tabelle> ON <bedingung>;

Für unser Beispiel sieht das dann so aus:

Aufgabe

Hole alle Mitarbeiter und (sofern vorhanden) die Angaben zum Dienstwagen.

SELECT mi.Personalnummer AS MitNr, 
       mi.Name, mi.Vorname, 
       dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
  FROM Mitarbeiter mi
       LEFT JOIN Dienstwagen dw ON dw.Mitarbeiter_ID = mi.ID;

Ausgabe

MITNR     NAME          VORNAME    DIW  KENNZEICHEN  TYP
--------  ------------  ---------  ---  -----------  ---
30001     Wagner        Gaby         3  DO-WB 423     14
30002     Feyerabend    Werner
40001     Langmann      Matthias     4  DO-WB 424     14
40002     Peters        Michael
50001     Pohl          Helmut       5  DO-WB 425     14
50002     Braun         Christian   14  DO-WB 352      2
50003     Polovic       Frantisek   15  DO-WB 353      3
50004     Kalman        Aydin       16  DO-WB 354      4
60001     Aagenau       Karolin      6  DO-WB 426     14
60002     Pinkart       Petra

Und wenn wir jetzt die beiden Tabellen vertauschen?

Aufgabe

Dann erhalten wir alle Dienstwagen und dazu die passenden Mitarbeiter.

SELECT mi.Personalnummer AS MitNr, 
       mi.Name, mi.Vorname, 
       dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
  FROM Dienstwagen dw
       LEFT JOIN Mitarbeiter mi ON dw.Mitarbeiter_ID = mi.ID;

Ausgabe

MITNR    NAME          VORNAME    DIW  KENNZEICHEN  TYP
-------  ------------  ---------  ---  -----------  ---
80001    Schindler     Christina    8  DO-WB 428     14
90001    Janssen       Bernhard     9  DO-WB 429     14
100001   Grosser       Horst       10  DO-WB 4210    14
110001   Eggert        Louis       11  DO-WB 4211    14
120001   Carlsen       Zacharias   12  DO-WB 4212    14
                                   13  DO-WB 111     16
50002    Braun         Christian   14  DO-WB 352      2
50003    Polovic       Frantisek   15  DO-WB 353      3
50004    Kalman        Aydin       16  DO-WB 354      4

Bitte überlegen Sie selbst, wie sich WHERE-Klauseln auf das Ergebnis einer Abfrage auswirken.

RIGHT OUTER JOIN

Dieser JOIN liefert alle Datensätze der rechten Tabelle, ggf. unter Berücksichtigung der WHERE-Klausel. Aus der linken Tabelle werden nur diejenigen Datensätze übernommen, die nach der Verknüpfungsbedingung passen.

SELECT <spaltenliste>
  FROM <linke Tabelle>
       RIGHT [OUTER] JOIN <rechte Tabelle> ON <bedingung>;

Für unser Beispiel „Mitarbeiter und Dienstwagen“ sieht das dann so aus:

SELECT mi.Personalnummer AS MitNr, 
       mi.Name, mi.Vorname, 
       dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
  FROM Mitarbeiter mi
       RIGHT JOIN Dienstwagen dw ON dw.Mitarbeiter_ID = mi.ID;

Ausgabe

MITNR    NAME          VORNAME    DIW  KENNZEICHEN  TYP
-------  ------------  ---------  ---  -----------  ---
80001    Schindler     Christina    8  DO-WB 428     14
90001    Janssen       Bernhard     9  DO-WB 429     14
100001   Grosser       Horst       10  DO-WB 4210    14
110001   Eggert        Louis       11  DO-WB 4211    14
120001   Carlsen       Zacharias   12  DO-WB 4212    14
                                   13  DO-WB 111     16
50002    Braun         Christian   14  DO-WB 352      2
50003    Polovic       Frantisek   15  DO-WB 353      3
50004    Kalman        Aydin       16  DO-WB 354      4

Nanu, dieses Ergebnis hatten wir doch gerade? Bei genauerem Überlegen wird klar: Beim LEFT JOIN gibt es alle Datensätze der linken Tabelle mit Informationen der rechten Tabelle; nun haben wir die beiden Tabellen vertauscht. Beim RIGHT JOIN werden alle Datensätze der rechten Tabelle mit Daten der linken Tabelle verknüpft; das entspricht diesem Beispiel.

Ob wir also die beiden Tabellen vertauschen oder LEFT gegen RIGHT, bleibt sich zwangsläufig gleich. Kurz und „knackig“ formuliert kann man sich also merken:

"A LEFT JOIN B" liefert dasselbe Ergebnis wie "B RIGHT JOIN A".

Bitte überlegen Sie, welches Ergebnis die Vertauschung der beiden Tabellen beim RIGHT JOIN liefert und welche Auswirkung WHERE-Klauseln haben.

FULL OUTER JOIN

Dieser JOIN liefert alle Datensätze beider Tabellen, ggf. unter Berücksichtigung der WHERE-Klausel. Wenn Datensätze nach der Verknüpfungsbedingung zusammenpassen, werden sie in einer Zeile angegeben; wo es keinen „Partner“ gibt, wird ein NULL-Wert angezeigt.

SELECT <spaltenliste>
  FROM <linke Tabelle>
       FULL [OUTER] JOIN <rechte Tabelle> ON <bedingung>;

Für unser Beispiel sieht das dann so aus:

SELECT mi.Personalnummer AS MitNr, 
       mi.Name, mi.Vorname, 
       dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
  FROM Mitarbeiter mi
       FULL JOIN Dienstwagen dw ON dw.Mitarbeiter_ID = mi.ID;

Ausgabe

MITNR     NAME          VORNAME     DIW  KENNZEICHEN  TYP
--------  ------------  ----------  ---  -----------  ---
100001    Grosser       Horst        10  DO-WB 4210    14
110001    Eggert        Louis        11  DO-WB 4211    14
120001    Carlsen       Zacharias    12  DO-WB 4212    14
                                     13  DO-WB 111     16
50002     Braun         Christian    14  DO-WB 352      2
50003     Polovic       Frantisek    15  DO-WB 353      3
50004     Kalman        Aydin        16  DO-WB 354      4
80002     Aliman        Zafer        17  DO-WB 382      2
80003     Langer        Norbert      18  DO-WB 383      3
80004     Kolic         Ivana        19  DO-WB 384      4
10002     Schneider     Daniela
20002     Schmitz       Michael
30002     Feyerabend    Werner
40002     Peters        Michael

Auch hier wollen wir wieder die beiden Tabellen vertauschen:

SELECT mi.Personalnummer AS MitNr, 
       mi.Name, mi.Vorname, 
       dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
  FROM Dienstwagen dw
       FULL JOIN Mitarbeiter mi ON dw.Mitarbeiter_ID = mi.ID;

Ausgabe

MITNR     NAME          VORNAME     DIW  KENNZEICHEN  TYP
--------  ------------  ----------  ---  -----------  ---
80001     Schindler     Christina     8  DO-WB 428     14
80002     Aliman        Zafer        17  DO-WB 382      2
80003     Langer        Norbert      18  DO-WB 383      3
80004     Kolic         Ivana        19  DO-WB 384      4
90001     Janssen       Bernhard      9  DO-WB 429     14
90002     Hinkel        Martina
100001    Grosser       Horst        10  DO-WB 4210    14
100002    Friedrichsen  Angelina
110001    Eggert        Louis        11  DO-WB 4211    14
110002    Deiters       Gisela
120001    Carlsen       Zacharias    12  DO-WB 4212    14
120002    Baber         Yvonne
                                     13  DO-WB 111     16

Bei detailliertem Vergleich des vollständigen Ergebnisses ergibt sich: Es ist gleich, nur in anderer Reihenfolge. Das sollte nicht mehr verwundern.

Verknüpfung mehrerer Tabellen

Alle bisherigen Beispiele kranken daran, dass als Typ des Dienstwagens nur die ID angegeben ist. Selbstverständlich möchte man die Typbezeichnung und den Hersteller lesen. Dazu müssen die beiden Tabellen Fahrzeugtyp und Fahrzeughersteller eingebunden werden. Beim INNER JOIN war das kein Problem; probieren wir aus, wie es beim OUTER JOIN aussehen könnte.

Mehrere Tabellen parallel

Aufgabe

Erweitern wir dazu die Aufstellung „alle Dienstwagen zusammen mit den zugeordneten Mitarbeitern“ um die Angabe zu den Fahrzeugen.

SELECT mi.Personalnummer AS MitNr, 
       mi.Name, mi.Vorname, 
       dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS TypID,
       ft.Bezeichnung as Typ, ft.Hersteller_ID as FheID
  FROM Dienstwagen dw
       left JOIN Mitarbeiter mi ON dw.Mitarbeiter_ID = mi.ID
       join      Fahrzeugtyp ft on dw.Fahrzeugtyp_ID = ft.ID;

Ausgabe

MITNR   NAME        VORNAME     DIW  KENNZEICHEN  TYPID  TYP              FHEID
------  ----------  ----------  ---  -----------  -----  ---------------  -----
100001  Grosser     Horst        10  DO-WB 4210      14  A160                 6
110001  Eggert      Louis        11  DO-WB 4211      14  A160                 6
120001  Carlsen     Zacharias    12  DO-WB 4212      14  A160                 6
                                 13  DO-WB 111       16  W211 (E-Klasse)      6
50002   Braun       Christian    14  DO-WB 352        2  Golf                 1
50003   Polovic     Frantisek    15  DO-WB 353        3  Passat               1
50004   Kalman      Aydin        16  DO-WB 354        4  Kadett               2

Der zweite JOIN wurde nicht genauer bezeichnet, ist also ein INNER JOIN. Das gleiche Ergebnis erhalten wir, wenn wir die Tabelle Fahrzeugtyp ausdrücklich als LEFT JOIN verknüpfen (bitte selbst ausprobieren!). Anders sieht es beim Versuch mit RIGHT JOIN oder FULL JOIN aus:

SELECT mi.Personalnummer AS MitNr, 
       mi.Name, mi.Vorname, 
       dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS TypID,
       ft.Bezeichnung as Typ, ft.Hersteller_ID as FheID
  FROM Dienstwagen dw
       left JOIN         Mitarbeiter mi ON dw.Mitarbeiter_ID = mi.ID
       right | full join Fahrzeugtyp ft on dw.Fahrzeugtyp_ID = ft.ID;

Ausgabe

MITNR   NAME        VORNAME     DIW  KENNZEICHEN  TYPID  TYP              FHEID
------  ----------  ----------  ---  -----------  -----  ---------------  -----
80001   Schindler   Christina     8  DO-WB 428       14  A160                 6
90001   Janssen     Bernhard      9  DO-WB 429       14  A160                 6
100001  Grosser     Horst        10  DO-WB 4210      14  A160                 6
110001  Eggert      Louis        11  DO-WB 4211      14  A160                 6
120001  Carlsen     Zacharias    12  DO-WB 4212      14  A160                 6
                                                         W204 (C-Klasse)      6
                                 13  DO-WB 111       16  W211 (E-Klasse)      6
                                                         Saab 9-3             8
                                                         S40                  9
                                                         C30                  9

Versuchen wir eine Erklärung: Die beiden JOINs stehen sozusagen auf der gleichen Ebene; jede JOIN-Klausel wird für sich mit der Tabelle Dienstwagen verknüpft. An der Verknüpfung zwischen Dienstwagen und Mitarbeiter ändert sich nichts. Aber für die Fahrzeugtypen gilt:

• Das erste Beispiel benutzt einen INNER JOIN, nimmt also für jeden vorhandenen Dienstwagen genau „seinen“ Typ.
• Wenn man stattdessen einen LEFT JOIN verwendet, erhält man alle vorhandenen Dienstwagen, zusammen mit den passenden Typen. Das ist faktisch identisch mit dem Ergebnis des INNER JOIN.
• Das zweite Beispiel benutzt einen RIGHT JOIN, das liefert alle registrierten Fahrzeugtypen und (soweit vorhanden) die passenden Dienstwagen.
• Wenn man stattdessen einen FULL JOIN verwendet, erhält man alle Kombinationen von Dienstwagen und Mitarbeitern, zusammen mit allen registrierten Fahrzeugtypen. Das ist faktisch identisch mit dem Ergebnis des RIGHT JOIN.

Sie sehen: Es kommt genau auf die gewünschten und die tatsächlich vorhandenen Verknüpfungen an.

Gliederung durch Klammern

Für Verknüpfungen, die durch Klammern gegliedert werden, nehmen wir ein anderes Beispiel, nämlich „Mitarbeiter RIGHT JOIN Dienstwagen“, denn die Fahrzeugtypen sind eine Ergänzung zu den Dienstwagen, nicht zu den Mitarbeitern (auch wenn den Abteilungsleitern ein Mercedes zugestanden wird, aber das ist ein anderes Thema und hat nichts mit SQL zu tun).

SELECT mi.Personalnummer AS MitNr, 
       mi.Name, mi.Vorname, 
       dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS TypID,
       ft.Bezeichnung as Typ, ft.Hersteller_ID as FheID
  FROM Mitarbeiter mi
       right JOIN ( Dienstwagen dw
                    join Fahrzeugtyp ft on ft.ID = dw.Fahrzeugtyp_id )
               ON dw.Mitarbeiter_ID = mi.ID;

Ausgabe

MITNR    NAME        VORNAME     DIW  KENNZEICHEN  TYPID  TYP              FHEID
-------  ----------  ----------  ---  -----------  -----  ---------------  -----
80001    Schindler   Christina     8  DO-WB 428       14  A160                 6
90001    Janssen     Bernhard      9  DO-WB 429       14  A160                 6
100001   Grosser     Horst        10  DO-WB 4210      14  A160                 6
110001   Eggert      Louis        11  DO-WB 4211      14  A160                 6
120001   Carlsen     Zacharias    12  DO-WB 4212      14  A160                 6
                                  13  DO-WB 111       16  W211 (E-Klasse)      6
50002    Braun       Christian    14  DO-WB 352        2  Golf                 1
50003    Polovic     Frantisek    15  DO-WB 353        3  Passat               1
50004    Kalman      Aydin        16  DO-WB 354        4  Kadett               2

Auch hier erhalten wir ein vergleichbares Ergebnis. Prüfen wir zunächst die Abfrage in der Klammer:

• LEFT JOIN und INNER JOIN haben als Grundlage „alle Dienstwagen“, es wird also eine Datenmenge „alle Dienstwagen“ (mit Zusatzinformationen über die Fahrzeugtypen) erstellt.
• RIGHT JOIN und FULL JOIN gehen aus von „alle Fahrzeugtypen“, es wird also eine Datenmenge „alle Fahrzeugtypen“ (mit Zusatzinformationen über die Dienstwagen) erstellt.

Da der Ausdruck innerhalb der Klammern zuerst ausgewertet wird, wird diese Datenmenge anschließend mit den Mitarbeitern verknüpft, soweit es der Verknüpfungsbedingung auf der Basis von dw.Mitarbeiter_ID entspricht.

Mit diesen Erkenntnissen können wir nun auch den Hersteller mit seinem Namen anzeigen; dazu benutzen wir wegen der bisherigen Erkenntnisse das erste Beispiel:

SELECT mi.Personalnummer AS MitNr, 
       mi.Name, mi.Vorname, 
       dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS TypID,
       ft.Bezeichnung as Typ, fh.Name as Hersteller
  FROM Dienstwagen dw
       left  JOIN Mitarbeiter        mi ON mi.ID = dw.Mitarbeiter_ID
       inner join Fahrzeugtyp        ft on ft.ID = dw.Fahrzeugtyp_ID
       inner join Fahrzeughersteller fh on fh.ID = ft.Hersteller_ID;

Ausgabe

MITNR     NAME        VORNAME     DIW  KENNZEICHEN   TYPID  TYP              HERSTELLER
--------  ----------  ----------  ---  ------------  -----  ---------------  -------------
80001     Schindler   Christina     8  DO-WB 428        14  A160             Mercedes-Benz
90001     Janssen     Bernhard      9  DO-WB 429        14  A160             Mercedes-Benz
100001    Grosser     Horst        10  DO-WB 4210       14  A160             Mercedes-Benz
110001    Eggert      Louis        11  DO-WB 4211       14  A160             Mercedes-Benz
120001    Carlsen     Zacharias    12  DO-WB 4212       14  A160             Mercedes-Benz
                                   13  DO-WB 111        16  W211 (E-Klasse)  Mercedes-Benz
50002     Braun       Christian    14  DO-WB 352         2  Golf             Volkswagen
50003     Polovic     Frantisek    15  DO-WB 353         3  Passat           Volkswagen
50004     Kalman      Aydin        16  DO-WB 354         4  Kadett           Opel
80002     Aliman      Zafer        17  DO-WB 382         2  Golf             Volkswagen
80003     Langer      Norbert      18  DO-WB 383         3  Passat           Volkswagen
80004     Kolic       Ivana        19  DO-WB 384         4  Kadett           Opel

Zusammenfassung

In diesem Kapitel lernten Sie die Verwendung von OUTER JOIN kennen:

• Mit dieser Verknüpfung werden auch Datensätze abgefragt und angezeigt, bei denen es in einer der Tabellen keinen zugeordneten Datensatz gibt.
• Mit einem LEFT JOIN erhält man alle Datensätze der linken Tabelle, ergänzt durch passende Angaben aus der rechten Tabelle.
• Mit einem RIGHT JOIN erhält man alle Datensätze der rechten Tabelle, ergänzt durch passende Angaben aus der linken Tabelle.
• Mit einem FULL JOIN erhält man alle Datensätze beider Tabellen, wenn möglich ergänzt durch passende Angaben aus der jeweils anderen Tabelle.

Bei der Verknüpfung mehrerer Tabellen ist genau auf den JOIN-Typ und ggf. auf Klammerung zu achten.

Übungen

Übung 1

Allgemeines

Zur Lösung

Welche der folgenden Aussagen sind wahr, welche sind falsch?

1. Um alle Mitarbeiter mit Dienstwagen aufzulisten, benötigt man einen LEFT OUTER JOIN.
2. LEFT JOIN ist nur eine Kurzschreibweise für LEFT OUTER JOIN und hat keine zusätzliche inhaltliche Bedeutung.
3. Ein LEFT JOIN von zwei Tabellen enthält alle Zeilen, die nach Auswahlbedingung in der linken Tabelle enthalten sind.
4. Ein RIGHT JOIN von zwei Tabellen enthält nur noch diejenigen Zeilen, die nach der Verknüpfungsbedingung in der linken Tabelle enthalten sind.
5. Wenn wir bei einer LEFT JOIN-Abfrage mit zwei Tabellen die beiden Tabellen vertauschen und stattdessen einen RIGHT JOIN verwenden, erhalten wir dieselben Zeilen in der Ergebnismenge.
6. Wir erhalten dabei nicht nur dieselben Zeilen, sondern auch dieselbe Reihenfolge.

Übung 2

Allgemeines

Zur Lösung

Was ist am folgenden SELECT-Befehl falsch und warum? Die Aufgabe dazu lautet:

Aufgabe

Gesucht werden Kombinationen von Fahrzeug-Kennzeichen und Fahrzeugtypen, wobei alle Typen aufgeführt werden sollen; es werden nur die ersten 20 Fahrzeuge nach ID benötigt.

select Kennzeichen, Bezeichnung
  from Fahrzeug fz
       left join Fahrzeugtyp ft on fz.Fahrzeugtyp_ID = ft.ID
 where fz.ID <= 20 ;

Übung 3

Sinnvollen SELECT-Befehl erstellen

Zur Lösung

Gesucht werden alle registrierten Versicherungsgesellschaften und (soweit vorhanden) deren Kunden mit Name, Vorname.

Übung 4

Sinnvollen SELECT-Befehl erstellen

Zur Lösung

Gesucht werden die Dienstwagen, deren Fahrzeugtypen sowie die Hersteller. Die Liste der Typen soll vollständig sein.

Übung 5

Sinnvollen SELECT-Befehl erstellen

Zur Lösung

Gesucht werden Kombinationen von Mitarbeitern und ihren Dienstwagen (einschl. Typ). Es geht um die Abteilungen 1 bis 5; auch nicht-persönliche Dienstwagen sollen aufgeführt werden.

Übung 6

Sinnvollen SELECT-Befehl erstellen

Zur Lösung

Gesucht werden alle registrierten Versicherungsgesellschaften sowie alle Kunden mit Name, Vorname, soweit der Nachname mit 'S' beginnt.

Übung 7

RIGHT oder LEFT

Zur Lösung

Vertauschen Sie in der Lösung von Übung 5 die beiden Tabellen Mitarbeiter und Dienstwagen und erläutern Sie:

1. Warum werden jetzt mehr Mitarbeiter angezeigt, und zwar auch solche ohne Dienstwagen?
2. Warum fehlt jetzt der „nicht-persönliche“ Dienstwagen?

Übung 8

SELECT-Befehl mit mehreren Tabellen

Zur Lösung

Gesucht werden Angaben zu den Mitarbeitern und den Dienstwagen. Beim Mitarbeiter sollen Name und Vorname angegeben werden, bei den Dienstwagen Bezeichnung und Name des Herstellers. Die Liste aller Fahrzeugtypen soll vollständig sein.

Übung 9

SELECT-Befehl mit mehreren Tabellen

Zur Lösung

Ergänzen Sie die Lösung zu Übung 8 insofern, dass nur Mitarbeiter der Abteilungen 1 bis 5 angezeigt werden; die Zeilen ohne Mitarbeiter sollen unverändert ausgegeben werden.

Lösungen

Lösung zu Übung 1

Allgemeines

Zur Übung

Die Aussagen 2, 3, 5 sind richtig, die Aussagen 1, 4, 6 sind falsch.

Lösung zu Übung 2

Allgemeines

Zur Übung

Richtig ist folgender Befehl:

select Kennzeichen, Bezeichnung
  from Fahrzeug fz
       right join Fahrzeugtyp ft on fz.Fahrzeugtyp_ID = ft.ID
 where fz.ID <= 20 or fz.ID is null;

Weil alle Typen aufgeführt werden sollen, wird ein RIGHT JOIN benötigt. Damit auch der Vermerk „es gibt zu einem Typ keine Fahrzeuge“ erscheint, muss die WHERE-Klausel um die IS NULL-Prüfung erweitert werden.

Lösung zu Übung 3

Sinnvollen SELECT-Befehl erstellen

Zur Übung

select Bezeichnung, Name, Vorname
  from Versicherungsgesellschaft vg
       left join Versicherungsnehmer vn on vg.ID = vn.Versicherungsgesellschaft_ID
 order by Bezeichnung, Name, Vorname;

Lösung zu Übung 4

Sinnvollen SELECT-Befehl erstellen

Zur Übung

select Kennzeichen, Bezeichnung, Name
  from Dienstwagen dw
       right join Fahrzeugtyp        ft on ft.ID = dw.Fahrzeugtyp_ID
       inner join Fahrzeughersteller fh on fh.ID = ft.Hersteller_ID
 order by Name, Bezeichnung, Kennzeichen;

Lösung zu Übung 5

Sinnvollen SELECT-Befehl erstellen

Zur Übung

SELECT mi.Name, mi.Vorname, 
       dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
  FROM Mitarbeiter mi
       RIGHT JOIN Dienstwagen dw ON dw.Mitarbeiter_ID = mi.ID
 where mi.Abteilung_ID <= 5 or mi.ID is null;

Die IS NULL-Prüfung wird wegen der „nicht-persönlichen“ Dienstwagen benötigt.

Lösung zu Übung 6

Sinnvollen SELECT-Befehl erstellen

Zur Übung

SELECT Bezeichnung, Name, Vorname
  FROM Versicherungsgesellschaft vg
       full JOIN Versicherungsnehmer vn ON vg.id = vn.Versicherungsgesellschaft_id
 where vn.Name like 'S%' or vn.id is null
 ORDER BY Bezeichnung, Name, Vorname;

Lösung zu Übung 7

RIGHT oder LEFT

Zur Übung

1. Bei einem RIGHT JOIN werden alle Einträge der rechten Tabelle angezeigt. „Rechts“ stehen jetzt die Mitarbeiter, also werden alle Mitarbeiter der betreffenden Abteilungen angezeigt.
2. Bei einem RIGHT JOIN werden die Einträge der linken Tabelle nur dann angezeigt, wenn sie zu einem Eintrag der rechten Tabelle gehören. Der „nicht-persönliche“ Dienstwagen aus der linken Tabelle gehört aber zu keinem der Mitarbeiter.

Lösung zu Übung 8

SELECT-Befehl mit mehreren Tabellen

Zur Übung

SELECT mi.Name, mi.Vorname,
       dw.Kennzeichen, ft.Bezeichnung, fh.Name as HST
  FROM Dienstwagen dw
       left  join Mitarbeiter        mi on mi.id = dw.Mitarbeiter_id
       right JOIN Fahrzeugtyp        ft on ft.Id = dw.Fahrzeugtyp_id
       inner join Fahrzeughersteller fh on fh.Id = ft.Hersteller_id;

Hinweise: Die Reihenfolge der JOINs ist nicht eindeutig; der LEFT JOIN kann auch später kommen. Wichtig ist, dass die Verbindung Dienstwagen ↔ Fahrzeugtyp ein RIGHT JOIN ist (oder bei Vertauschung der Tabellen ein LEFT JOIN).

Lösung zu Übung 9

SELECT-Befehl mit mehreren Tabellen

Zur Übung

SELECT mi.Name, mi.Vorname,
       dw.Kennzeichen, ft.Bezeichnung, fh.Name as HST
  FROM Dienstwagen dw
       left  join Mitarbeiter        mi on mi.id = dw.Mitarbeiter_id
       right JOIN Fahrzeugtyp        ft on ft.Id = dw.Fahrzeugtyp_id
       inner join Fahrzeughersteller fh on fh.Id = ft.Hersteller_id
 where (mi.Abteilung_id <= 5) or (mi.id is null);

---

Mehr zu JOIN

Die folgenden Ergänzungen zu JOIN sind in besonderen Situationen hilfreich.

Welcher JOIN passt wann?

Diese Frage stellt sich vor allem Anfängern sehr oft. Neben den (theoretischen) Überlegungen der vorigen Kapitel helfen oft besondere Beispiele; vielleicht ist dieses nützlich.

Eltern und ihre Kinder

Wir haben zwei Tabellen: Paare (also Eltern) und Kinder. Es gibt kinderlose Paare, Paare mit Kindern und Waisenkinder. Wir wollen die Eltern und Kinder in Abfragen verknüpfen; bei den Symbolen steht der linke Kreis für die Tabelle Paare und der rechte Kreis für die Tabelle Kinder.

LEFT JOIN		Alle Paare und (falls es Kinder gibt) auch diese	SELECT * FROM Paare   LEFT  JOIN Kinder ON Paare.Key = Kinder.Key
INNER JOIN		Nur Paare, die Kinder haben	SELECT * FROM Paare   INNER JOIN Kinder ON Paare.Key = Kinder.Key
RIGHT JOIN		Alle Kinder und (falls es Eltern gibt) auch diese	SELECT * FROM Paare   RIGHT JOIN Kinder ON Paare.Key = Kinder.Key
LEFT JOIN IS NULL		Nur Paare, die keine Kinder haben	SELECT * FROM Paare   LEFT  JOIN Kinder ON Paare.Key = Kinder.Key   WHERE Kinder.Key IS NULL
RIGHT JOIN IS NULL		Nur Waisenkinder	SELECT * FROM Paare   RIGHT  JOIN Kinder ON Paare.Key = Kinder.Key   WHERE Paare.Key IS NULL
FULL JOIN		Alle Paare und alle Kinder	SELECT * FROM Paare   FULL  JOIN Kinder ON Paare.Key = Kinder.Key
FULL JOIN IS NULL		Alle kinderlosen Paare und alle Waisenkinder	SELECT * FROM Paare   FULL  JOIN Kinder ON Paare.Key = Kinder.Key   WHERE Kinder.Key IS NULL OR Paare.Key IS NULL
Die zweite Variante mit dem INNER JOIN kann man auch so ausdrücken:
LEFT JOIN IS NOT NULL		Alle Paare und (falls es Kinder gibt) auch diese, wobei es ein Kind geben muss	SELECT * FROM Paare   LEFT  JOIN Kinder ON Paare.Key = Kinder.Key   WHERE Kinder.Key IS NOT NULL

Tatsächlich sind oft mehrere Wege möglich, wie bereits im letzten Kapitel gesagt wurde: Das Ergebnis für "A LEFT JOIN B" gleicht dem von "B RIGHT JOIN A".

SELF JOIN – Verknüpfung mit sich selbst

Solche Verknüpfungen sind immer dann nötig, wenn Werte einer einzigen Spalte aus verschiedenen Datensätzen verbunden werden. Der JOIN dafür benutzt auf beiden Seiten dieselbe Tabelle <tabelle>; diese beiden „Instanzen“ müssen durch einen Alias unterschieden werden.

SELECT <spaltenliste>
  FROM <tabelle> t1
       JOIN <tabelle> t2 ON <verknüpfung>
 WHERE <auswahlbedingungen>

Hinweis: Es funktioniert nicht, wenn eine der beiden Instanzen mit Alias und die andere ohne Alias benutzt wird. Dann kommt das DBMS erst recht durcheinander. In einem Forum stand einmal ein solches Problem mit einer sehr vertrackten Ausgabe, bei dem diese Ursache erst nach längerer Diskussion klar wurde.

Dies soll zunächst an zwei Beispielen umgesetzt werden.

Beispiel 1

Aufgabe

Zeige zu jedem Fahrzeug andere Fahrzeuge aus dem gleichen Kreis.

Wir beschränken uns darauf, dass in einer Zeile jeweils zwei von allen möglichen Kombinationen angezeigt werden, auch wenn viele Angaben wiederholt werden.

select a.Kennzeichen, b.Kennzeichen
  from Fahrzeug a
       JOIN Fahrzeug b
         on SUBSTRING(a.Kennzeichen from 1 for 3) 
          = SUBSTRING(b.Kennzeichen from 1 for 3)
 where a.Kennzeichen < b.Kennzeichen
 order by a.Kennzeichen;

Ausgabe

KENNZEICHEN	KENNZEICHEN1
-----------     ------------
BO-GH 102	BO-KL 678
BOR-NO 234	BOR-PQ 567
BOR-NO 234	BOR-RS 890
BOR-PQ 567	BOR-RS 890
GE-AB 123	GE-AC 246
GE-AB 123	GE-EG 892
GE-AC 246	GE-EG 892
RE-CD 456	RE-LM 901
RE-CD 456	RE-LM 902
RE-CD 456	RE-LM 903

Gesucht werden Kombinationen eines Fahrzeugs mit jeweils einem anderen Fahrzeug, wobei die Bedingung „gleicher Kreis“ erfüllt sein soll. Wir brauchen also innerhalb eines SELECT-Befehls zwei Zugriffe auf die Tabelle Fahrzeug mit einer passenden Vergleichsbedingung. (Diese haben wir etwas ungenau formuliert, damit sie nicht unübersichtlich wird.)

Dies ist gleichzeitig ein Beispiel dafür, dass beliebige Bedingungen möglich sind. Überlegen Sie bitte auch, warum unter WHERE die „kleiner als“-Bedingung benutzt wird.

Beispiel 2

Aufgabe

Zeige zu jedem Fahrzeug mit mehreren Schadensfällen den zeitlichen Abstand von einem Vorfall zum nächsten an.

Lösungsweg 1

Wir benötigen für jedes der Fahrzeuge aus der Tabelle Schadensfall zwei Einträge mit dem Datum sowie den Abstand, der einfach als Differenz benutzt wird und somit die Anzahl der Tage anzeigt. Die Fahrzeuge sind freilich erst über die Tabelle Zuordnung_SF_FZ zu finden und müssen zusätzlich verbunden werden. Außerdem sind die Fahrzeuge und die Reihenfolge der Datumsangaben zu kontrollieren.

Datumsvergleich als Teil der Auswahlbedingung

SELECT fz.ID, fz.Kennzeichen, 
       sf1.Datum AS Datum1, sf2.Datum AS Datum2, sf2.Datum - sf1.Datum AS Abstand
  FROM Zuordnung_SF_FZ      zu1
       join Zuordnung_SF_FZ zu2 on zu1.Fahrzeug_ID     = zu2.Fahrzeug_ID
       join Schadensfall    sf1 ON zu1.Schadensfall_ID = sf1.ID
       JOIN Schadensfall    sf2 on zu2.Schadensfall_ID = sf2.Id
       JOIN Fahrzeug        fz  ON zu1.Fahrzeug_ID     = fz.ID
 where sf1.Datum < sf2.Datum
   and sf2.Datum = ( SELECT MIN(sf3.Datum)
                       FROM Schadensfall sf3 
                            join Zuordnung_SF_FZ zu3 on zu3.Schadensfall_ID = sf3.id
                      WHERE sf1.Datum < sf3.Datum
                        and zu3.Fahrzeug_ID = zu1.Fahrzeug_ID )
 order by fz.ID, Datum1;

Ausgabe

ID  KENNZEICHEN     DATUM1      DATUM2      ABSTAND
--  --------------  ----------  ----------  -------
 4  GE-AB 123       03.02.2007  05.10.2008      610
 6  HER-EF 789      19.12.2007  21.06.2009      550
 7  BO-GH 102       11.07.2007  13.03.2009      611
 7  BO-GH 102       13.03.2009  01.08.2009      141

Lösungswege 2 und 3

Alternativen bieten die folgenden Lösungen:

Datumsvergleich als Teil der Verknüpfungsbedingungen

SELECT fz.ID, fz.Kennzeichen, 
       sf1.Datum AS Datum1, sf2.Datum AS Datum2, sf2.Datum - sf1.Datum AS Abstand
  FROM Zuordnung_SF_FZ      zu1
       join Zuordnung_SF_FZ zu2 on zu1.Fahrzeug_ID     = zu2.Fahrzeug_ID
       join Schadensfall    sf1 ON zu1.Schadensfall_ID = sf1.ID
       JOIN Schadensfall    sf2 on zu2.Schadensfall_ID = sf2.Id
                               and sf1.Datum < sf2.Datum
                               and sf2.Datum = ( SELECT MIN(sf3.Datum)
                                                   FROM Schadensfall sf3
                                                        join Zuordnung_SF_FZ zu3 
                                                          on zu3.Schadensfall_ID = sf3.ID
                                                  WHERE sf1.Datum < sf3.Datum
                                                    and zu3.Fahrzeug_ID = zu1.Fahrzeug_ID )
       JOIN Fahrzeug        fz  ON zu1.Fahrzeug_ID     = fz.ID
 order by fz.ID, Datum1;

Datumsvergleich als Funktion bei den SELECT-Spalten

SELECT fz.ID, fz.Kennzeichen, 
       sf1.Datum AS Datum1, MIN(sf2.Datum) AS Datum2, MIN(sf2.Datum - sf1.Datum) AS Abstand
  FROM Zuordnung_SF_FZ      zu1
       join Zuordnung_SF_FZ zu2 on zu1.Fahrzeug_ID     = zu2.Fahrzeug_ID
       join Schadensfall    sf1 ON zu1.Schadensfall_ID = sf1.ID
       JOIN Schadensfall    sf2 on zu2.Schadensfall_ID = sf2.Id
                               and sf1.Datum < sf2.Datum
       JOIN Fahrzeug        fz  ON zu1.Fahrzeug_ID     = fz.ID
 group by fz.ID, fz.Kennzeichen, sf1.Datum
 order by fz.ID, Datum1;

Erläuterungen

In dieser Aufgabe stecken mehrere Probleme:

• Die Angaben aus der Spalte Datum der Tabelle Schadensfall müssen zweimal geholt werden.
• Zu jedem Schadensfall wird der Eintrag der Tabelle Zuordnung_SF_FZ benötigt, weil die Schadensfälle für jedes Fahrzeug gesucht werden.
• Das Datum, das zu sf1 gehört, muss immer „kleiner“ sein, also früher liegen als das Datum, das zu sf2 gehört.
• Außerdem benötigen wir „irgendwo“ die Einschränkung, dass zum Vergleich nur der jeweils folgende Schadensfall genommen werden darf, also das MINimum der späteren Einträge:
  • Die erste Lösung verwendet dafür eine Unterabfrage für eine Auswahlbedingung.
  • Die zweite Lösung arbeitet mit einer Unterabfrage bei der Verknüpfungsbedingung.
  • Die dritte Lösung benutzt das MINimum direkt als Aggregatfunktion und verlangt „zum Ausgleich“ eine GROUP BY-Klausel.

Die Lösung benötigt deshalb mehrfach verknüpfte Tabellen:

• Als Grundlage wird die Tabelle der Zuordnungen zwischen Schadensfällen und Fahrzeugen zu1 verwendet.
• Hauptverknüpfung ist der Self-Join zu2 auf dieselbe Tabelle, weil nur solche Einträge verknüpft werden sollen, die sich auf dasselbe Fahrzeug beziehen.
• Zu jedem Schadensfall aus zu1 werden die detaillierten Angaben aus sf1 geholt.
• Zu jedem Schadensfall aus zu2 werden die detaillierten Angaben aus sf2 geholt.
• Ergänzend benötigen wir das Kennzeichen des betreffenden Fahrzeugs, also einen JOIN auf Fahrzeug.
• Vor allem müssen die Vergleichsbedingungen für die Datumsangaben eingebaut werden.

Welche Lösung die Datenbank am wenigsten belastet, kann nicht generell gesagt werden, weil es von zu vielen Umständen abhängt.

Erweiterung durch einen OUTER JOIN

Bei diesen Lösungen stehen nicht alle Schadensfälle im Ergebnis, weil es nur um den zeitlichen Abstand ging. Wenn beispielsweise auch die Schadenshöhe gewünscht wird, müssen wir dafür sorgen, dass von sf1 oder sf2 alle Einträge angezeigt werden; wir brauchen also einen OUTER JOIN wie zum Beispiel (auf der Grundlage der letzten Version) so:

SELECT fz.ID, fz.Kennzeichen, 
       sf1.Datum AS Datum1, MIN(sf2.Datum) AS Datum2, MIN(sf2.Datum - sf1.Datum) AS Abstand,
       sf1.Schadenshoehe
  FROM Zuordnung_SF_FZ      zu1
       left join Zuordnung_SF_FZ zu2 on zu1.Fahrzeug_ID     = zu2.Fahrzeug_ID
       left join Schadensfall    sf1 ON zu1.Schadensfall_ID = sf1.ID
       left JOIN Schadensfall    sf2 on zu2.Schadensfall_ID = sf2.Id
                                    and sf1.Datum < sf2.Datum
       left JOIN Fahrzeug        fz  ON zu1.Fahrzeug_ID     = fz.ID
 group by fz.ID, fz.Kennzeichen, sf1.Datum, sf1.Schadenshoehe
 order by fz.ID, Datum1;

Ausgabe

ID  KENNZEICHEN      DATUM1      DATUM2      ABSTAND  SCHADENSHOEHE
--  ---------------  ----------  ----------  -------  -------------
 3  RE-LM 903        27.05.2008                            1.438,75
 4  GE-AB 123        03.02.2007  05.10.2008      610       1.234,50
 4  GE-AB 123        05.10.2008                            1.983,00
 5  RE-CD 456        11.07.2007                            2.066,00
 6  HER-EF 789       19.12.2007  21.06.2009      550       3.715,60
 6  HER-EF 789       21.06.2009                              865,00
 7  BO-GH 102        11.07.2007  13.03.2009      611       2.066,00
 7  BO-GH 102        13.03.2009  01.08.2009      141       4.092,15
 7  BO-GH 102        01.08.2009                            2.471,50

Wir nehmen es hin, dass dann alle Schadensfälle aufgeführt werden, auch für die Fahrzeuge, die nur einmal „aufgefallen“ sind. Dies ist eine Folge davon, dass Grundlage aller Verknüpfungen die Tabelle der Zuordnungen sein musste.

Bei allen solchen Situationen müssen Sie genau überlegen, wie die verschiedenen Instanzen miteinander verknüpft werden und wie die übrigen Bedingungen einzubinden sind. Oft führen erst mehrere Versuche zum Ziel. Hilfreich sind auch die Ausführungspläne, die ein DBMS anbieten kann.

Weitere Situationen

Zum Schluss sollen noch ein paar andere Beispiele erwähnt werden, bei denen ein Self-Join hilft.

• Wenn bei den Dienstwagen die privat gefahrenen Strecken abgerechnet werden sollen, können der km-Stand beim Fahrtantritt und beim Fahrtende in derselben Spalte, aber in getrennten Datensätzen gespeichert werden.
• Doppelte Adressen innerhalb einer Adressendatei können aufgespürt werden (siehe Übung 3).
• Wenn in der Tabelle Mitarbeiter zu einem Mitarbeiter der Leiter der Abteilung gesucht wird, benötigen wir wegen des doppelten Zugriffs auf dieselbe Tabelle ebenfalls einen Self-Join.

CROSS JOIN – das kartesische Produkt

Mit dieser speziellen Formulierung kann man deutlich machen, dass man wirklich ein kartesisches Produkt herstellen will und nicht etwa nur die JOIN-Bedingung vergessen hat:

SELECT mi.Personalnummer AS MitNr, 
       mi.Name, mi.Vorname, 
       dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
  FROM Mitarbeiter mi
       CROSS JOIN Dienstwagen dw;

Als Ergebnis wird tatsächlich jede Kombination eines Mitarbeiters mit einem Dienstwagen ausgegeben, also n mal m Sätze – wie beim allerersten Versuch im Kapitel Einfache Tabellenverknüpfung. Man kann die Ergebnismenge auch einschränken durch eine WHERE-Klausel:

SELECT mi.Personalnummer AS MitNr, 
       mi.Name, mi.Vorname, 
       dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
  FROM Mitarbeiter mi
       CROSS JOIN Dienstwagen dw
 WHERE mi.Name like 'S%' and CHAR_LENGTH(dw.Kennzeichen) = 10;

Hinweis: Die DBMS verhalten sich bei einem CROSS JOIN unterschiedlich; teilweise ist ein CROSS JOIN mit WHERE-Klausel nichts anderes als ein INNER JOIN.

Der Nutzen des CROSS JOIN wird bei unserer sparsamen Beispieldatenbank nicht klar. Unter Oracle wäre folgendes Verfahren möglich und hilfreich:

Aufgabe

Kontrollieren Sie mit der Tabelle Fahrzeugbuchung, welche Fahrzeuge am 2.12.2009 im Fuhrpark zur Verfügung stehen.

SELECT mi.Personalnummer AS MitNr, 
       mi.Name, mi.Vorname, 
       dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
  FROM Mitarbeiter mi
       CROSS JOIN Dienstwagen dw
       INNER JOIN Fahrzeugbuchung fb 
               ON dw.Kennzeichen = fb.Kennzeichen
 WHERE fb.Datum = to_date('02.12.2009','dd.mm.yyyy')
   AND fb.Status = 'noch nicht gebucht';

WITH – Inline-View

Oft kommt es vor, dass man die Daten aus einer Tabelle erst bearbeiten möchte, bevor man sie mit einer anderen Tabelle verknüpft. Beispiel:

SELECT Kuerzel, Bezeichnung, Anzahl_Mitarbeiter
  FROM Abteilung
       INNER JOIN ( select Abteilung_ID, count(*) AS Anzahl_Mitarbeiter
                      FROM Mitarbeiter
                     GROUP BY Abteilung_ID
                  ) MA_Anzahl
             ON Abteilung.ID = MA_Anzahl.Abteilung_ID
;

Dabei wird zunächst nach der Tabelle Mitarbeiter die Anzahl der Mitarbeiter für jede Abteilung bestimmt. Das Ergebnis wird wie eine Tabelle MA_Anzahl behandelt und über Abteilung_ID mit der Tabelle Abteilung verknüpft.

Diese Syntax ist ziemlich verschachtelt. Man kann sie auch so schreiben:

WITH MA_Anzahl as
   ( select Abteilung_ID, count(*) AS Anzahl_Mitarbeiter
       FROM Mitarbeiter
      GROUP BY Abteilung_ID
   )
SELECT Kuerzel, Bezeichnung, Anzahl_Mitarbeiter
  FROM Abteilung 
       INNER JOIN MA_Anzahl
             ON Abteilung.ID = MA_Anzahl.Abteilung_ID
;

MA_Anzahl wird benutzt wie eine VIEW, die allerdings nicht permanent angelegt wird, sondern die nur für die Ausführung dieses einen SQL-Befehls gültig ist. Der Unterschied liegt „nur“ darin, dass die Unterabfrage herausgelöst wird und durch WITH als separate temporäre Tabelle eingebunden wird.

Ob man die obere oder die untere Variante besser findet, ist sicher Geschmackssache und hat auch damit zu tun, welche Formulierung man gewöhnt ist. Wenn man sich die SQL-Beispiele im Kapitel Fremdschlüssel-Beziehungen daraufhin ansieht, dann würde eine Formulierung ohne WITH viel länger und komplizierter werden.

Zusammenfassung

In diesem Kapitel lernten Sie einige weitere Möglichkeiten im Zusammenhang mit JOINs kennen.

• Für bestimmte Anforderungen sind Verknüpfungen einer Tabelle mit sich selbst sinnvoll oder notwendig.
• In diesen Fällen sind die Auswahl- und Verknüpfungsbedingungen besonders sorgfältig zu bestimmen.
• Durch WITH können Verknüpfungen über JOINs übersichtlicher werden.

Übungen

Übung 1

Fragen zum Verständnis

Zur Lösung

Welche der folgenden Aussagen sind wahr, welche falsch?

1. Eine Tabelle kann mit sich selbst verknüpft werden.
2. SELF JOIN ist nur ein inhaltlicher Begriff, aber kein SQL-Schlüsselwort.
3. Bei einem SELF JOIN sind nur INNER JOINs erlaubt.
4. Eine bestimmte Tabelle darf in einem SELF JOIN nur zweimal verwendet werden.
5. Für einen SELF JOIN können Tabellen-Aliase benutzt werden, aber sie sind nicht überall erforderlich.
6. Ein CROSS JOIN ist eine Verknüpfung zweier Tabellen ohne Verknüpfungsbedingung.
7. Bei einem CROSS JOIN darf sich die WHERE-Klausel nicht auf die (rechte) Tabelle des JOINs beziehen.
8. Die Schreibweise mit WITH ist kein Sonderfall eines JOINs, sondern eine übersichtlichere Schreibweise, wenn mehrere Tabellen verknüpft werden.

Übung 2

Verknüpfung einer Tabelle mit sich selbst

Zur Lösung

Suchen Sie zu jedem Mitarbeiter den Namen und Vornamen des Leiters der Abteilung. Die Abteilungsleiter in unserer einfachen Firmenhierarchie haben keinen Vorgesetzten; sie sollen in der Liste deshalb nicht aufgeführt werden.

Übung 3

Doppelte Adressen suchen

Zur Lösung

Suchen Sie Einträge in der Tabelle Versicherungsnehmer, bei denen Name, Vorname, PLZ, Strasse übereinstimmen. Jeweils zwei dieser Adressen sollen mit ihrer ID und den übereinstimmenden Angaben aufgeführt werden.

Hinweis: Benutzen Sie einen JOIN, der sich nicht auf übereinstimmende IDs bezieht.

Lösungen

Lösung zu Übung 1

Fragen zum Verständnis

Zur Übung

Die Aussagen 1, 2, 6, 8 sind wahr, die Aussagen 3, 4, 5, 7 sind falsch.

Lösung zu Übung 2

Verknüpfung einer Tabelle mit sich selbst

Zur Übung

select mi1.Abteilung_ID as Abt, mi1.Name, mi1.Vorname, mi2.Name as LtrName, mi2.Vorname as LtrVorn
  from Mitarbeiter mi1
       join Abteilung   ab  on mi1.Abteilung_ID = ab.ID
       join Mitarbeiter mi2 on mi2.Abteilung_ID = ab.ID
 where mi2.Ist_Leiter = 'J'
   and mi1.Ist_Leiter = 'N'

Lösung zu Übung 3

Doppelte Adressen suchen

Zur Übung

select a.Name, a.Vorname, a.PLZ, a.Strasse, a.ID, b.ID
  from Versicherungsnehmer a
       JOIN Versicherungsnehmer b
         on a.Name = b.Name and a.Vorname = b.Vorname
            and a.PLZ = b.PLZ and a.Strasse = b.Strasse
 where a.ID < b.ID;

Siehe auch

Bei Wikipedia finden Sie weitere Erläuterungen:

• Auswertungsplan, auch „Ausführungsplan“ genannt

---

Nützliche Erweiterungen

In diesem Kapitel werden verschiedene Erweiterungen des SELECT-Befehls genauer behandelt.

Die Beispiele beziehen sich auch hier auf den Anfangsbestand der Beispieldatenbank; auf die Ausgabe der selektierten Datensätze wird wiederum weitgehend verzichtet. Bitte probieren Sie alle Beispiele aus und nehmen Sie verschiedene Änderungen vor, um die Auswirkungen zu erkennen.

DISTINCT – keine doppelten Einträge

Wenn Sie den DISTINCT-Parameter bei einem SELECT-Befehl angeben, erhalten Sie nur eindeutige Ergebnisse:

Liste der Fahrzeuge, für die Schadensfälle aufgetreten sind

SELECT distinct Fahrzeug_ID
  from Zuordnung_SF_FZ;

Ausgabe

Fahrzeug_ID
      3
      4
      5
      6
      7

Bitte beachten Sie: Als „eindeutig“ gilt immer die gesamte Zeile, also alle Spalten zusammen. Die folgende Abfrage liefert alle Datensätze; Fahrzeuge mit mehreren Schadensfällen stehen auch mehrfach in der Liste.

 Nur theoretisch DISTINCT, praktisch nicht

SELECT distinct Fahrzeug_ID, ID
  from Zuordnung_SF_FZ;

Als Gegenstück gibt es den ALL-Parameter, der ausdrücklich alle Datensätze abfragt. Da dies der Standardwert ist, wird er äußerst selten benutzt:

SELECT all Fahrzeug_ID
  from Zuordnung_SF_FZ

Beschränkung auf eine Anzahl Zeilen

Häufig will man nicht sofort das gesamte Ergebnis sehen, sondern nur einen Teil der Zeilen.

Vor allem im Netzwerk kostet es seine Zeit, eine größere Menge von Datensätzen zu übertragen. Es ist deshalb oft praktisch, zunächst einen Teil des Ergebnisses zu holen und anzuzeigen. Während der Anwender sich mit diesem Teilergebnis beschäftigt, wird „im Hintergrund“ der nächste Abschnitt geholt usw.

Im SQL-Standard gibt es dafür (noch) kein Verfahren. Abweichend vom üblichen Vorgehen in diesem Buch erhalten Sie Lösungen für verschiedene DBMS.

Anstelle konstanter Werte (ohne Klammern) kann in allen folgenden Fällen auch ein SQL-Ausdruck (in Klammern) angegeben werden.

Firebird: FIRST SKIP oder ROWS

Möglichkeiten bei Firebird

Firebird bietet gleich zwei Lösungen an, die erste mit FIRST / SKIP:

 SELECT [DISTINCT]
   [ FIRST <value1> ]
   [  SKIP <value2> ]
   <select list> FROM ... /* usw. */

Der FIRST-Parameter gibt an, wie viele Zeilen am Anfang anzuzeigen sind; der SKIP-Parameter legt fest, wie viele Zeilen davor übersprungen werden sollen. Beide Parameter werden einzeln oder zusammen benutzt; sie folgen direkt als erste Klausel nach DISTINCT, noch vor der Spaltenliste. Einige Beispiele:

➤ Nur FIRST zeigt die ersten Zeilen.

Firebird-Version

select FIRST 10
       ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name;

➤ Nur SKIP überspringt die ersten Zeilen.

Firebird-Version

select SKIP 10
       ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name;

➤ FIRST zeigt die ersten 10 Zeilen an, aber wegen SKIP werden vorher 5 Zeilen übersprungen.

Firebird-Version

select FIRST 10 SKIP 5
       ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name;

➤ Mit einem Ausdruck kann dafür gesorgt werden, dass etwa das erste Viertel der Datensätze abgerufen wird:

Firebird-Version

select FIRST ( (SELECT count(*) from Mitarbeiter) / 4 )
       ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name;

Der Wert für FIRST wird aus der Anzahl der Datensätze berechnet.

Die zweite Firebird-Variante benutzt mit ROWS direkt Zeilennummern:

 SELECT ... FROM ... WHERE ...
  ORDER BY ...
   ROWS <value1> [ TO <value2> ]

Die ROWS-Parameter legen fest, dass (nur) eine bestimmte Anzahl Zeilen angezeigt werden sollen, die durch die Zeilennummern gekennzeichnet sind.

• Wenn nur ROWS benutzt wird, bezeichnet <value1> die Gesamtzahl der angezeigten Zeilen.
• Wenn ROWS zusammen mit TO benutzt wird, ist <value1> die erste Zeilennummer und <value2> die letzte Zeilennummer.

Einige Beispiele:

➤ Ausgabe der Zeilen 10 bis 20 (also insgesamt 11 Zeilen)

Firebird-Version

select ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name
  ROWS 10 TO 20;

➤ Der erste Datensatz gemäß Sortierung

Firebird-Version

select ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name
  ROWS 1;

➤ Der letzte Datensatz gemäß Sortierung

Firebird-Version

select ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name desc
  ROWS 1;

Vor allem das letzte Beispiel, bei dem mit DESC die Sortierung umgekehrt wird, ist oft sehr nützlich.

Bei einer Interbase-Datenbank sind auch prozentuale Angaben möglich; das ist bei Firebird entfallen.

Microsoft SQL: TOP

Möglichkeiten bei Microsoft-SQL

Der MS-SQL Server benutzt diese Syntax:

 SELECT [DISTINCT]
   TOP ( <value> ) [PERCENT] [WITH TIES]
   <select list> FROM ... /* usw. */

Mit den TOP-Parametern wird festgelegt, dass (nur) eine bestimmte Anzahl von Zeilen angezeigt werden sollen, die durch die Zeilennummern gekennzeichnet sind. Diese Parameter folgen direkt als erste Klausel nach DISTINCT, noch vor der Spaltenliste.

• <value> bezeichnet die Gesamtzahl der angezeigten Zeilen. Es wird empfohlen, die Klammern immer zu setzen.
• Wenn TOP zusammen mit PERCENT benutzt wird, handelt es sich dabei um die prozentuale Angabe der Zeilenzahl.
• WITH TIES muss zusammen mit ORDER BY benutzt werden und liefert dann zusätzliche doppelte Zeilen, wenn der letzte Wert nach der Reihenfolge gleich ist den Werten in danach folgenden Zeilen.

Einige Beispiele:

➤ Nur TOP zeigt die ersten Zeilen.

Version für MS-SQL

select
   TOP 10
       ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name;

➤ TOP + PERCENT zeigt z. B. das erste Viertel an.

Version für MS-SQL

select
   TOP 25 PERCENT
       ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name;

➤ OFFSET NEXT für einen Teil der Daten (Paging), z.B. Zeile 11-20

Version für MS-SQL 2012

select
       ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name
 OFFSET 10 ROWS
 FETCH NEXT 10 ROWS ONLY;;

➤ Der letzte Datensatz gemäß Sortierung

Version für MS-SQL

select 
   TOP 1
       ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name desc;

Vor allem das letzte Beispiel, bei dem mit DESC die Sortierung umgekehrt wird, ist oft sehr nützlich.

MySQL und PostgreSQL: LIMIT

Möglichkeiten bei MySQL und PostgreSQL

Diese DBMS benutzen den LIMIT-Parameter. Dieser Parameter folgt nach ORDER BY, wobei die Sortierung nicht angegeben werden muss.

 SELECT ... FROM ... WHERE ...
  ORDER BY ...
  LIMIT <value1> OFFSET <value2>

Dabei wird mit <value1> angegeben, wie viele Zeilen am Anfang angezeigt werden sollen. Mit <value2> kann nach dem Begriff OFFSET außerdem angegeben werden, wie viele Zeilen davor übersprungen werden sollen.

➤ Es werden die ersten 10 Zeilen angezeigt.

MySQL-Version

select ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name
 LIMIT 10;

➤ Es werden die ersten 10 Zeilen angezeigt, aber vorher werden 5 Zeilen übersprungen.

MySQL-Version

select ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name
 LIMIT 10 OFFSET 5;

➤ Der letzte Datensatz gemäß Sortierung

MySQL-Version

select ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name desc
 LIMIT 1;

Vor allem das letzte Beispiel, bei dem mit DESC die Sortierung umgekehrt wird, ist oft sehr nützlich.

Eine andere Schreibweise für diesen Parameter verzichtet auf das Wort OFFSET:

 SELECT ... FROM ... WHERE
  ORDER BY ...
  LIMIT [ <value2>, ] <value1>

Bei dieser Variante wird genauso mit <value1> angegeben, wie viele Zeilen am Anfang angezeigt werden sollen. Mit <value2> kann außerdem angegeben werden, wie viele Zeilen davor übersprungen werden sollen; dieser Wert wird jedoch zuerst angegeben und durch ein Komma von der gewünschten Zeilenzahl getrennt.

Die Bedeutung beider Varianten ist identisch, sodass Beispiele nicht nötig sind. Es ist wohl Geschmackssache, welche Version „eingängiger“ ist.

Oracle: ROWNUM

Möglichkeiten bei Oracle

Bei dem DBMS Oracle gibt es bei jedem SELECT-Ergebnis eine implizite Spalte Rownum. Man kann diese Spalte mit ausgeben lassen. Solange man kein ORDER BY angibt, ist die Reihenfolge der ausgegebenen Sätze nicht festgelegt. Dieselbe Abfrage kann an einem anderen Tag durchaus eine andere Nummerierung der Sätze hervorbringen. Das liegt z. B. daran, dass jemand die Datensätze in der Zwischenzeit reorganisiert hat.

Oracle-Version

SELECT Name, rownum 
  FROM Mitarbeiter;

Ausgabe

NAME     ROWNUM
-------  ------
Müller      1
Schneider   2
Meyer       3
Schmitz     4   /* usw. */

Rownum als implizite Spalte der Ergebnismenge

Wenn man diese Spalte Rownum nicht angibt, dann wird sie auch nicht ausgegeben. Ihr Vorteil ist, dass man sie auch bei WHERE verwenden kann:

Oracle-Version

SELECT Name
  FROM Mitarbeiter 
 WHERE Rownum <= 2;

Ausgabe

NAME     ROWNUM
-------  ------
Müller      1
Schneider   2

Es werden nur die ersten 2 Zeilen angezeigt.

Folgende Formulierung funktioniert allerdings nicht, wenn man nur den 10. Satz ausgeben will:

 Oracle-Version

SELECT Name
  FROM Mitarbeiter; 
 WHERE Rownum = 10;

Nur den 10. Satz anzeigen – so geht es nicht.

Das liegt daran, dass der Zähler Rownum nur die Zeilen zählt, die auch wirklich ausgegeben werden. Wenn die Tabelle 500 Sätze hat, dann wird bei jedem Satz geprüft, ob Rownum bereits den Wert 10 erreicht hat. Das ist jedoch nie der Fall, da der Zähler immer den Wert 0 behält.

Da muss man schon das DBMS zwingen, die Ergebnismenge mit der Rownum zwischenzuspeichern. Dann geht es:

 Oracle-Version

SELECT Name
  FROM ( SELECT Name, rownum R
         FROM Mitarbeiter )
 WHERE R = 10;

Nur den 10. Satz anzeigen – so klappt es.

Welcher Satz dabei ausgegeben wird, ist jedoch dem Zufall überlassen.

Wenn man die Ergebnismenge sortiert ausgeben will und dabei nur die ersten 6 Sätze ausgegeben haben will, dann funktioniert die folgende Formulierung nicht:

 Oracle-Version

SELECT Name
  FROM Mitarbeiter; 
 WHERE Rownum <= 6
 ORDER BY Name;

Die alphabetisch ersten 6 Namen ausgeben – so geht es nicht.

Man wird feststellen, dass zwar nur 6 Sätze ausgegeben werden, dass das aber keinesfalls immer die alphabetisch ersten 6 Namen sind. Das liegt daran, dass zunächst WHERE ausgewertet wird und danach erst sortiert wird. Es werden also – wie bei den vorangegangenen Beispielen beschrieben – beliebige 6 Sätze gelesen, und nur diese 6 Sätze werden sortiert.

Für die richtige Lösung muss die Datenbank schon etwas mehr tun. Sie muss zuerst alle vorhandenen Sätze sortieren und dann die ersten 6 Sätze ausgeben:

 Oracle-Version

SELECT Name
  FROM ( SELECT Name
           FROM Mitarbeiter 
          ORDER BY Name )
 WHERE Rownum <= 6;

Die alphabetisch ersten 6 Namen ausgeben – so klappt es.

Sybase: ROWCOUNT

Möglichkeiten bei Sybase

Bei diesem DBMS wird zuerst die Anzahl der gewünschten Zeilen angegeben, danach folgt der SELECT-Befehl.

Sybase-Version

SET rowcount 10;
SELECT Name, rownum 
  FROM Mitarbeiter;

Rowcount als Vorgabe

Mehrere Abfragen zusammenfassen

UNION – Vereinigung

Mit der UNION-Klausel werden mehrere Abfragen verknüpft und als einheitliche Ergebnismenge geliefert. Dieses Verfahren ist Standard und steht in jedem DBMS zur Verfügung. Es ist vor allem in zwei Situationen sinnvoll:

• Die Daten stammen aus verschiedenen Tabellen mit ähnlicher Struktur und sollen gemeinsam angezeigt werden.
• Die Daten stammen aus derselben Tabelle; die Auswahlbedingungen sind so komplex, dass eine einzelne Abfrage nicht möglich, nicht sinnvoll oder zu unübersichtlich wäre.

Die Syntax einer solchen Verknüpfung sieht aus wie folgt, wobei auch mehr als zwei Abfragen verknüpft werden können:

 SELECT <Spaltenliste1> 
   FROM <Tabellenliste1> 
  WHERE <Bedingungen1>
        UNION [ DISTINCT | ALL ]
 SELECT <Spaltenliste2> 
   FROM <Tabellenliste2> 
  WHERE <Bedingungen2>

Bei den einzelnen Abfragen können grundsätzlich alle Klauseln benutzt werden. Bitte beachten Sie folgende Bedingungen:

• Alle Einzelabfragen müssen in der Anzahl und Reihenfolge der Ergebnis-Spalten übereinstimmen. Die Datentypen müssen je nach DBMS und Version genau gleich sein oder zumindest so ähnlich, dass sie automatisch konvertiert werden können.
• Die Spaltennamen werden aus der ersten Spaltenliste übernommen, ggf. unter Berücksichtigung von Alias-Namen.
• Grundsätzlich werden doppelte Zeilen aus den Einzelabfragen unterdrückt, d. h. DISTINCT ist Standard und kann weggelassen werden. Wenn doppelte Zeilen angezeigt werden sollen, ist ALL anzugeben.
• Die Benutzung von Klammern sowie ORDER BY für das Gesamtergebnis wird je nach DBMS unterschiedlich geregelt.

Aufgabe

Das folgende Beispiel ist eine einfache Zusammenfassung aller Fahrzeuge aus den Tabellen Fahrzeuge und Dienstwagen:

select ID, Kennzeichen, Farbe
  from Dienstwagen
       union
select ID, Kennzeichen, Farbe
  from Fahrzeug;

Im folgenden Beispiel werden als Spalte Var Konstanten eingetragen, die die Herkunft der Daten angeben, und es werden verschiedene Auswahlbedingungen benutzt.

select 'D' as Var, ID, Kennzeichen, Farbe
  from Dienstwagen
 where Fahrzeugtyp_ID <= 3
       union
select 'F', ID, Kennzeichen, Farbe
  from Fahrzeug
 where Kennzeichen LIKE 'B%';

Wenn die Ergebnismenge sortiert werden soll, werden je nach DBMS unterschiedliche Schreibweisen benötigt.

MySQL-Version mit Klammern um die Einzelabfragen herum, ORDER BY wird zusätzlich angegeben.

( select 'D' as Var, ID, Kennzeichen, Farbe
    from Dienstwagen
   where Fahrzeugtyp_ID <= 3 )
union
( select 'F', ID, Kennzeichen, Farbe
    from Fahrzeug
   where Fahrzeugtyp_ID <= 3 )
order by Kennzeichen;

Firebird-Version mit einer Hauptabfrage mit ORDER BY und der UNION-Verknüpfung als Unterabfrage.

select * from
( select 'D' as Var, ID, Kennzeichen, Farbe
    from Dienstwagen
   where Fahrzeugtyp_ID <= 3
         union
  select 'F', ID, Kennzeichen, Farbe
    from Fahrzeug
   where Fahrzeugtyp_ID <= 3 )
order by Kennzeichen;

Mit der folgenden Abfrage werden verschiedene Listen von Mitarbeitern zusammengefasst: zum einen diejenigen, die im September Geburtstag haben, zum anderen die Leiter mit einer Mobil-Rufnummer:

select Personalnummer, Name, Vorname, Geburtsdatum, Ist_Leiter as Leiter, Mobil
  from Mitarbeiter
 where EXTRACT(month from Geburtsdatum) = 9
       union
select Personalnummer, Name, Vorname, Geburtsdatum, Ist_Leiter, Mobil
  from Mitarbeiter
 where Ist_Leiter = 'J' and Mobil <> '';

Offensichtlich kann diese Abfrage auch ohne UNION erreicht werden, nämlich durch: WHERE <bedingung1> OR ( <bedingung2> AND <bedingung3> ). Sie können sich aber sicherlich noch viel kompliziertere Situationen vorstellen, vor allem wenn die WHERE-Bedingungen auf Informationen in unterschiedlichen weiteren Tabellen zugreifen müssen.

Andere Varianten

Unter den gleichen Voraussetzungen wie UNION gibt es weitere Wege, um Einzelabfragen zusammenzufassen. Diese stehen aber nur vereinzelt zur Verfügung; wir beschränken uns deshalb auf Hinweise.

Mit INTERSECT (MS-SQL) erhält man den Durchschnitt der Teilabfragen.

 SELECT <Spaltenliste1>   FROM <Tabellenliste1>   WHERE <Bedingungen1>
        INTERSECT
 SELECT <Spaltenliste2>   FROM <Tabellenliste2>   WHERE <Bedingungen2>

Die Ergebnismenge besteht aus allen Zeilen, die sowohl zum ersten SELECT-Befehl als auch zum zweiten SELECT-Befehl gehören.

Mit EXCEPT (MS-SQL) oder MINUS (Oracle) wird die Differenz der Teilabfragen zurückgegeben.

 SELECT <Spaltenliste1>   FROM <Tabellenliste1>   WHERE <Bedingungen1>
        EXCEPT | MINUS
 SELECT <Spaltenliste2>   FROM <Tabellenliste2>   WHERE <Bedingungen2>

Die Ergebnismenge besteht aus allen Zeilen, die zum ersten SELECT-Befehl gehören, aber beim zweiten SELECT-Befehl nicht enthalten sind.

Wie bei UNION können auch solche Verknüpfungen durch sehr komplizierte JOINs und WHERE-Klauseln nachgebildet werden.

CASE WHEN – Fallunterscheidungen

Immer wieder möchte man innerhalb eines SQL-Befehls je nach Situation unterschiedliche Werte erhalten. Das einfachste Beispiel ist die Tabelle Mitarbeiter: Anstelle des Feldinhalts Ist_Leiter mit 'J' oder 'N' kann 'Leiter' oder eine leere Zeichenkette angezeigt werden. Dafür ist der CASE-Ausdruck vorgesehen, den es in zwei Varianten gibt.

Als <expression> können wahlweise konstante Werte oder komplexe Ausdrücke verwendet werden. Der CASE-Ausdruck ist nicht nur für SELECT, sondern auch für Speichern-Befehle geeignet und nicht nur (wie in den meisten Beispielen) als Teil der Spaltenliste, sondern auch in der WHERE-Klausel oder an anderen Stellen, an denen entsprechende Werte benötigt werden.

Der Wert, den der CASE-Ausdruck zurückgibt, erhält einen passenden Datentyp, der sich aus den Result-Werten ergibt.

Simple Case – die einfache Fallunterscheidung

Die einfache Variante hat folgende Syntax:

 CASE <expression>
      WHEN <expression1> THEN <result1>
    [ WHEN <expression2> THEN <result2> ]  /* usw. */
    [ ELSE <default> ]
 END

Bei dieser Version wird der Wert, der sich durch den ersten Ausdruck beim CASE ergibt, nacheinander mit den Ausdrücken nach WHEN verglichen. Sobald eine Gleichheit festgestellt wird, wird der Wert, der sich durch den Result-Ausdruck ergibt, als Wert für den CASE-Ausdruck übernommen. Wenn es keine Übereinstimmung gibt, wird der Wert des Default-Ausdrucks übernommen; wenn ELSE nicht vorhanden ist, wird NULL als Wert genommen. Beispiele:

Aufgabe

Ein vorhandener Feldinhalt, nämlich 'J' oder 'N', wird für die Ausgabe durch andere Texte ersetzt:

select Personalnummer as Pers, Name, Vorname, Geburtsdatum as Geb,
       CASE Ist_Leiter
            WHEN 'J' THEN 'Leiter'
            ELSE ''
       END as Leiter, 
       Mobil
  from Mitarbeiter;

Ausgabe

PERS       NAME          VORNAME    GEB         LEITER  MOBIL
---------  ------------  ---------  ----------  ------  -----
40001      Langmann      Matthias   28.03.1976  Leiter
40002      Peters        Michael    15.11.1973
50001      Pohl          Helmut     27.10.1980  Leiter  (0171) 4123456
50002      Braun         Christian  05.09.1966          (0170) 8351647

Dieselbe Lösung ohne ELSE-Zweig ändert nur die Anzeige:

select Personalnummer as Pers, Name, Vorname, Geburtsdatum as Geb,
       CASE Ist_Leiter
            WHEN 'J' THEN 'Leiter'
       END as Leiter, 
       Mobil
  from Mitarbeiter;

Ausgabe

PERS       NAME          VORNAME    GEB         LEITER  MOBIL
---------  ------------  ---------  ----------  ------  -----
40001      Langmann      Matthias   28.03.1976  Leiter
40002      Peters        Michael    15.11.1973  <null>
50001      Pohl          Helmut     27.10.1980  Leiter  (0171) 4123456
50002      Braun         Christian  05.09.1966  <null>  (0170) 8351647

Aufgabe

Bei der Art der Versicherungsverträge möchten wir die Varianten im „Klartext“ lesen:

select Vertragsnummer as Vertrag, Abschlussdatum as Datum,
       CASE Art
            WHEN 'VK' THEN 'Vollkasko'
            WHEN 'TK' THEN 'Teilkasko'
            WHEN 'HP' THEN 'Haftpflicht'
       END
  from Versicherungsvertrag;

Ausgabe

VERTRAG  DATUM       CASE
-------  ----------  -----------
DB-04    25.01.2008  Haftpflicht
RH-01    11.12.1976  Vollkasko
RD-02    29.01.1988  Haftpflicht
RM-03    13.01.1996  Haftpflicht
RD-04    23.11.2006  Haftpflicht
RR-05    29.06.1990  Teilkasko

Searched Case – die komplexe Fallunterscheidung

Die komplexe Variante hat keinen Ausdruck hinter dem CASE; sie arbeitet nach folgender Syntax:

CASE 
     WHEN <bedingung1> THEN <result1>
   [ WHEN <bedingung2> THEN <result2> ]  /* usw. */
   [ ELSE <default> ]
END

Bei dieser Variante werden nacheinander Bedingungen geprüft. Sobald eine Bedingung als WAHR festgestellt wird, wird der Wert, der sich durch den Result-Ausdruck ergibt, als Wert für den CASE-Ausdruck übernommen. Wenn es keine Übereinstimmung gibt, wird der Wert des Default-Ausdrucks übernommen; wenn ELSE nicht vorhanden ist, wird NULL als Wert genommen. Beispiele:

Aufgabe

In einer einzigen Zeile wird angegeben, wieviele Versicherungsverträge innerhalb eines Jahrzehnts abgeschlossen wurden.

select
    SUM( CASE WHEN EXTRACT(YEAR from Abschlussdatum) between 1970 and 1979 
              THEN 1 
              ELSE 0 
         END ) as S_197_,
    SUM( CASE WHEN EXTRACT(YEAR from Abschlussdatum) between 1980 and 1989 
              THEN 1 
              ELSE 0 
         END ) as S_198_,
    SUM( CASE WHEN EXTRACT(YEAR from Abschlussdatum) between 1990 and 1999 
              THEN 1 
              ELSE 0 
         END ) as S_199_,
    SUM( CASE WHEN EXTRACT(YEAR from Abschlussdatum) >= 2000               
              THEN 1 
              ELSE 0 
         END ) as S_200_
  from Versicherungsvertrag;

Ausgabe

S_197_   S_198_   S_199_  S_200_
   6        6        6       5

Dazu wird für jedes Jahrzehnt eine Spalte vorgesehen. Jede Spalte enthält einen CASE-Ausdruck mit einer WHEN-Bedingung. Wenn für eine Zeile diese Bedingung TRUE ergibt, wird 1 zur Summe dieses Jahrzehnts addiert, andernfalls 0.

Aufgabe

Der CASE-Ausdruck soll innerhalb von ORDER BY eine unterschiedliche

Sortierung, abhängig von der Art der Versicherung, erreichen:

select ID, Art,
       Abschlussdatum as Datum, Vertragsnummer as Vertr,
       Mitarbeiter_ID as Mit, Fahrzeug_ID as FZG
  from Versicherungsvertrag
  order by Art,
           CASE WHEN Art = 'TK' THEN ID
                WHEN Art = 'VK' THEN Mitarbeiter_ID
                WHEN Art = 'HP' THEN Fahrzeug_ID
           END;

Ausgabe

ID  ART  DATUM       VERTR  MIT  FZG
------------------------------------
14  HP   15.03.1998  KG-03    9   16
18  HP   17.05.2000  HG-03    9   17
19  HP   21.09.2004  HB-04    9   19
10  HP   23.11.2006  RD-04    9   20
 6  HP   25.01.2008  DB-04    9   21
15  HP   27.03.1988  KV-04   10   22
11  TK   29.06.1990  RR-05    9   23
12  TK   14.02.1978  KB-01   10    6
21  VK   20.06.1982  XH-02    9    8
23  VK   19.07.2002  XO-04    9   18
 7  VK   11.12.1976  RH-01   10    5
22  VK   05.06.1992  XW-03   10   13

Hier wurden alle Abschnitte numerisch sortiert. Auch die Teilsortierung nach Datum funktioniert, muss aber unter Umständen überarbeitet werden:

 Firebird-Version

select ID, Art,
       Abschlussdatum as Datum, Vertragsnummer as Vertr,
       Mitarbeiter_ID as Mit, Fahrzeug_ID as FZG
  from Versicherungsvertrag
  order by Art,
           CASE WHEN Art = 'TK' THEN ID
                WHEN Art = 'VK' THEN Mitarbeiter_ID
                WHEN Art = 'HP' THEN Abschlussdatum
           END;

Ausgabe

Invalid token.
SQL error code = -104.
Datatypes are not comparable in expression CASE.

Auch wenn Firebird (wie im ersten Beispiel) Namen von Spalten akzeptiert, werden die Datentypen der Spalten vergleichen. Diese sind offensichtlich nicht kompatibel; also bricht Firebird diese Abfrage ab. Aber in der ORDER BY-Klausel können auch die Nummern der Spalten angegeben werden; dann klappt es:

 Firebird-Version

select ID, Art,
       Abschlussdatum as Datum, Vertragsnummer as Vertr,
       Mitarbeiter_ID as Mit, Fahrzeug_ID as FZG
  from Versicherungsvertrag
  order by Art,
           CASE WHEN Art = 'TK' THEN ID
                WHEN Art = 'VK' THEN Mitarbeiter_ID
                WHEN Art = 'HP' THEN 2
           END;

Mit Nummern der Spalten anstelle der Namen.

Bitte wundern Sie sich nicht über das identische Ergebnis wie oben bei der Sortierung nach Fahrzeug_ID: Die Fahrzeuge wurden in der gleichen Reihenfolge erfasst wie die Verträge; also stimmen in der ersten Zeit beide Sortierungen überein.

Die Sortierung kann nur einheitlich festgelegt werden, aber nicht mal so, mal so:

 Art der Sortierung kann nicht gemischt werden.

select ID, Art,
       Abschlussdatum as Datum, Vertragsnummer as Vertr,
       Mitarbeiter_ID as Mit, Fahrzeug_ID as FZG
  from Versicherungsvertrag
  order by Art,
           CASE
              WHEN Art = 'TK' THEN ID             ASC
              WHEN Art = 'VK' THEN Mitarbeiter_ID ASC
              WHEN Art = 'HP' THEN 2              DESC
           END;

Ausgabe

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 7, column 39. ASC.

 Einheitliche Sortierung DESC sollte möglich sein

select ID, Art,
       Abschlussdatum as Datum, Vertragsnummer as Vertr,
       Mitarbeiter_ID as Mit, Fahrzeug_ID as FZG
  from Versicherungsvertrag
  order by Art,
           CASE
              WHEN Art = 'TK' THEN ID             
              WHEN Art = 'VK' THEN Mitarbeiter_ID 
              WHEN Art = 'HP' THEN 2              
           END DESC;

Zumindest unter Firebird gelingt dennoch keine korrekte Sortierung: nur der Abschnitt 'TK' wird absteigend sortiert, die beiden anderen nicht. Sie müssen aber sowieso selbst ausprobieren, was in welchem DBMS möglich ist und was nicht.

CASE-Ausdruck beim Speichern

Wie beim SELECT-Befehl kann ein CASE-Ausdruck auch beim Speichern an verschiedenen Stellen benutzt werden, vor allem bei den Zuweisungen der Werte für INSERT und UPDATE sowie bei der WHERE-Klausel für UPDATE und DELETE. Da sich diese Kapitel vor allem mit Abfragen beschäftigen, beschränken wir uns auf ein Beispiel.

Aufgabe

Die Zuständigkeit der Mitarbeiter für die Versicherungsverträge wird neu geregelt:

• Vollkasko-Verträge gehören zum Aufgabenbereich des Abteilungsleiters (Mitarbeiter-ID 9).
• Teilkasko-Verträge gehören zum Aufgabenbereich des Mitarbeiters mit ID 12.
• Die Haftpflicht-Verträge werden in Abhängigkeit von der ID aufgeteilt auf die Mitarbeiter 10 und 11.

update Versicherungsvertrag
   set Mitarbeiter_id =
       CASE WHEN Art = 'VK' THEN  9
            WHEN Art = 'TK' THEN 12
            WHEN Art = 'HP' THEN 10 + MOD(ID, 2)
       END;

Es wird also eine normale SET-Anweisung geschrieben. Die CASE-Anweisung liefert die benötigten Werte, wobei für den Fall „Haftpflicht“ für gerade IDs der Wert (10+0) und für ungerade IDs der Wert (10+1) gesetzt wird. Eine WHERE-Klausel ist nicht erforderlich, weil alle Verträge neu zugeordnet werden sollen.

Zusammenfassung

In diesem Kapitel lernten Sie einige nützliche Erweiterungen vor allem für Abfragen kennen:

• Auf den DISTINCT-Parameter zur Auswahl wird genauer eingegangen.
• Je nach DBMS wird unterschiedlich geregelt, wenn nur eine gewisse Anzahl von Zeilen gewünscht wird.
• Mit UNION wird das Ergebnis von zwei oder mehr Abfragen in einer gemeinsamen Ergebnistabelle zusammengefasst.
• Mit CASE WHEN sind Fallunterscheidungen für gewisse Werte möglich; dies ist auch beim Speichern von Datensätzen sowohl für die Werte als auch für die WHERE-Klausel hilfreich.

Übungen

Übung 1

Richtig oder falsch?

Zur Lösung

Welche der folgenden Aussagen sind richtig, welche sind falsch?

1. Es ist Standard, den Parameter ALL bei einem SELECT-Befehl anzugeben.
2. Bei SELECT DISTINCT werden nur solche Zeilen angezeigt, die sich in mindestens einer Spalte unterscheiden.
3. Es ist äußerst selten erforderlich, das Ergebnis einer Abfrage nur in Teilen zu holen.
4. Für die Anzeige oder Verwendung einer ROW_NUMBER hat der SQL-Standard (2003) ein Verfahren vorgeschrieben.
5. Ein solches Verfahren wird von den meisten DBMS verwendet, ist aber in unterschiedlicher Weise verwirklicht.
6. Bei UNION, INTERSECT usw. müssen die ausgewählten Spalten von der Anzahl her übereinstimmen.
7. Bei UNION, INTERSECT usw. müssen die ausgewählten Spalten vom Datentyp her genau übereinstimmen.

Übung 2

DISTINCT und ALL

Zur Lösung

Bitte überprüfen Sie die folgenden Befehle:

--  Variante 1
select distinct COUNT(*)
  from Mitarbeiter
 group by Abteilung_ID

--  Variante 2
select all COUNT(*)
  from Mitarbeiter
 group by Abteilung_ID

Worin unterscheidet sich die Ausgabe? Welche wichtige Information fehlt vor allem bei Variante 2?

Übung 3

UNION, INTERSECT usw.

Zur Lösung

Was ist an den folgenden Befehlen falsch oder fragwürdig? Wie kann man das DBMS dazu bringen, den „fragwürdigen“ Befehl auf jeden Fall auszuführen?

--  Befehl 1
select Name, Vorname                   from Mitarbeiter
       union
select Name, Vorname, Geburtsdatum     from Versicherungsnehmer

--  Befehl 2
select Name, Vorname, Abteilung_ID     from Mitarbeiter
       union
select Name, Vorname, Geburtsdatum     from Versicherungsnehmer

Übung 4

UNION, INTERSECT usw.

Zur Lösung

Erstellen Sie eine Abfrage für die Tabellen Versicherungsvertrag, Fahrzeug, Zuordnung_SF-FZ, in der folgende Bedingungen berücksichtigt werden:

• Es sollen Vertragsnummer, Abschlussdatum, Kennzeichen sowie ggf. anteilige Schadenshöhe angezeigt werden.
• Fahrzeuge mit einem Schaden sollen vollständig angezeigt werden.
• Fahrzeuge ohne Schaden sollen nur angezeigt werden, wenn der Vertrag vor 1990 abgeschlossen wurde.
• Das Ergebnis soll nach Schadenshöhe und Kennzeichen sortiert werden.

Benutzen Sie UNION zur Verknüpfung der Verträge mit und ohne Schaden.

Übung 5

Fallunterscheidung für Nachschlagewerte

Zur Lösung

Schreiben Sie einen SELECT-Befehl, bei dem die korrekte Briefanrede für die Mitarbeiter erstellt wird.

Hinweis: Benutzen Sie CONCAT zum Verknüpfen mehrerer Zeichenketten.

Übung 6

Fallunterscheidung für Bereiche

Zur Lösung

Zur Tabelle Fahrzeug soll aus dem Kennzeichen die regionale Herkunft abgeleitet und angezeigt werden. Schreiben Sie eine Abfrage für diese Spalten Kennzeichen und Herkunft.

Hinweis: Benutzen Sie POSITION zur Feststellung des Bindestrichs sowie SUBSTRING.

Zusatzfrage: Wie müsste ein solches Problem sinnvollerweise gelöst werden, falls eine solche Zuordnung häufiger und allgemeiner benötigt wird?

Übung 7

Fallunterscheidung für mehrere Varianten

Zur Lösung

Aus der Tabelle Versicherungsnehmer sollen Name, Vorname und Anschrift angezeigt werden. Außerdem soll jede Adresse eine Markierung bekommen: F = (eigene) Firmenkunden, P = (eigene) Privatkunden, X = eXterne Verträge (d. h. Kunden fremder Versicherungsgesellschaften). Begründen Sie die Reihenfolge der WHEN-ELSE-Bedingungen.

Übung 8

Fallunterscheidung beim Speichern

Zur Lösung

Schreiben Sie einen UPDATE-Befehl, der nach Ablauf eines Versicherungsjahres den Prämiensatz für das nächste Jahr ändert. Berücksichtigen Sie dabei folgende Bedingungen:

• Neue Verträge, für die noch kein Prämiensatz gilt, werden auf 200 [Prozent] gesetzt.
• Verträge mit einem Prämiensatz von mindestens 100 werden um 20 reduziert.
• Verträge mit einem Prämiensatz von weniger als 100 werden um 10 reduziert.
• Der Mindestsatz von 30 darf nicht unterschritten werden.

Ignorieren Sie dabei zunächst, dass dies nur im Fall von Schadensfreiheit gelten darf und innerhalb eines Jahres nur einmal neu berechnet werden darf.

Übung 9

Fallunterscheidung beim Speichern

Zur Lösung

Bei genauerer Untersuchung von Übung 8 sind weitere Bedingungen im UPDATE-Befehl erforderlich.

1. In welcher Weise könnte die Schadensfreiheit berücksichtigt werden? Abhängig von der Höhe des Schadens soll sich der Prämiensatz gar nicht, wenig oder mehr erhöhen.
2. Wie kann unter Verwendung des Datums Prämienänderung gesichert werden, dass die Neuberechnung nur einmal jährlich stattfinden darf?

Versuchen Sie, dies im selben UPDATE-Befehl zu berücksichtigen. Sie sollen keinen Befehl schreiben, sondern die notwendigen Klauseln erwähnen und erläutern.

Lösungen

Lösung zu Übung 1

Richtig oder falsch?

Zur Übung

Die Aussagen 2, 5, 6 sind richtig. Die Aussagen 1, 3, 4, 7 sind falsch.

Lösung zu Übung 2

DISTINCT und ALL

Zur Übung

Variante 1 nennt jede Anzahl von Mitarbeitern pro Abteilung genau einmal. Bei Variante 2 gibt es diese Anzeige für jede Abteilung einzeln. Dabei fehlt vor allem die Angabe der Abteilung_ID, ohne die die Ausgabe ziemlich sinnlos ist.

Lösung zu Übung 3

UNION, INTERSECT usw.

Zur Übung

Bei Befehl 1 werden unterschiedlich viele Spalten ausgewählt, das ist unzulässig.

Bei Befehl 2 unterscheiden sich die Datentypen der dritten Spalte; es ist unsicher, ob das DBMS die unterschiedlichen Spalten einheitlich als Zeichenkette (zulässig) oder nicht-kompatibel (unzulässig) interpretieren will.

Ein CAST beider Spalten auf VARCHAR macht die Datentypen kompatibel.

Lösung zu Übung 4

UNION, INTERSECT usw.

Zur Übung

Eine mögliche Variante lautet so (Firebird-Version für ORDER BY):

select * from (
  select Vertragsnummer, Abschlussdatum, Kennzeichen, Schadenshoehe
    from Versicherungsvertrag vv
         inner join Fahrzeug        fz on fz.ID = vv.Fahrzeug_ID
         right join Zuordnung_SF_FZ zu on fz.ID = zu.Fahrzeug_ID
union
  select Vertragsnummer, Abschlussdatum, Kennzeichen, 0
    from Versicherungsvertrag vv
         inner join Fahrzeug        fz on fz.ID = vv.Fahrzeug_ID
   where Abschlussdatum < '01.01.1990' and not
      EXISTS ( select ID from Zuordnung_SF_FZ zu where zu.Fahrzeug_ID = fz.ID )
) order by Schadenshoehe, Kennzeichen;

Der zweite SELECT kann auch so geschrieben werden:

  select Vertragsnummer, Abschlussdatum, Kennzeichen, 0
    from Versicherungsvertrag vv
         inner join Fahrzeug        fz on fz.ID = vv.Fahrzeug_ID
         left  join Zuordnung_SF_FZ zu on fz.ID = zu.Fahrzeug_ID
   where Abschlussdatum < '01.01.1990' and zu.ID is null

Lösung zu Übung 5

Fallunterscheidung für Nachschlagewerte

Zur Übung

select CONCAT( 'Sehr geehrte',
                CASE Geschlecht
                     WHEN 'M' THEN 'r Herr '
                     WHEN 'W' THEN ' Frau '
                     ELSE '/r Frau/Herr '
                END,
                Name ) as Anrede
  from Mitarbeiter;

Hinweise: CONCAT oder die String-Verknüpfung erzeugen u. U. eine falsche Länge des ersten Teils. Das kann mit TRIM „repariert“ werden; dann ist aber das abschließende Leerzeichen hinzuzufügen. Der ELSE-Zweig ist überflüssig, weil Geschlecht nicht NULL sein kann; er wird nur der Vollständigkeit halber genannt.

Lösung zu Übung 6

Fallunterscheidung für Bereiche

Zur Übung

select Kennzeichen,
       CASE SUBSTRING(Kennzeichen from 1 for POSITION('-', Kennzeichen)-1 )
            WHEN 'RE'  THEN 'Kreis Recklinghausen'
            WHEN 'GE'  THEN 'Stadt Gelsenkirchen'
            WHEN 'E'   THEN 'Stadt Essen'
            WHEN 'BO'  THEN 'Stadt Bochum'
            WHEN 'HER' THEN 'Stadt Herne'
            ELSE 'unbekannt'
       END as Herkunft
  from Fahrzeug
 order by 2, 1;

Zur Zusatzfrage: Eine Tabelle Region o. ä. würde mit LEFT JOIN über den o. g. SUBSTRING verknüpft. Auch ein INNER JOIN wäre möglich, dann würden aber Fahrzeuge fehlen, deren Kennzeichen in der Tabelle Region fehlen.

Lösung zu Übung 7

Fallunterscheidung für mehrere Varianten

Zur Übung

select Name, Vorname, PLZ, Strasse, Hausnummer,
       CASE
            WHEN Eigener_Kunde = 'N'                       THEN 'X'
            WHEN Vorname is null or Fuehrerschein is null  THEN 'F'
            ELSE 'P'
       END as Markierung
  from Versicherungsnehmer;

Zur Reihenfolge: Bei den Fremdverträgen wird nicht zwischen Firmen- und Privatkunden unterschieden; dies muss also zuerst geprüft werden. Bei Firmenkunden sind weder Vorname noch Führerschein-Datum registriert, das wird als nächstes geprüft (eines der WHEN-Kriterien würde genügen). Alle übrigen Fälle sind Privatkunden.

Lösung zu Übung 8

Fallunterscheidung beim Speichern

Zur Übung

update Versicherungsvertrag
   set Praemiensatz = CASE
                          WHEN Praemiensatz is null THEN 200
                          WHEN Praemiensatz >=  100 THEN Praemiensatz - 20
                          WHEN Praemiensatz >=   40 THEN Praemiensatz - 10
                          ELSE 30
                      END
 where -- passende Bedingungen

Lösung zu Übung 9

Fallunterscheidung beim Speichern

Zur Übung

1. Es wird eine geschachtelte CASE-Anweisung verwendet. Zunächst wird unterschieden, ob überhaupt Schadensfälle aufgetreten sind. Im ersten Zweig „kein Schaden“ wird die Lösung von Übung 7 benutzt. Im zweiten Zweig „mit Schaden“ wird eine ähnliche CASE-Anweisung eingefügt, die abhängig von der Schadenshöhe den Prämiensatz erhöht.
2. In der WHERE-Klausel wird geprüft, ob die letzte Prämienänderung überschritten ist. Gleichzeitig mit der Neuberechnung wird dieses Datum um ein Jahr weitergesetzt.

Hinweis: Dieses Verfahren wird ausführlich im Kapitel Prozeduren: Automatisches UPDATE behandelt. Bei Interesse können Sie im dortigen Code den abschließenden UPDATE-Befehl vergleichen.

---

Berechnete Spalten

Mit Abfragen kann man nicht nur Spalten auswählen, sondern auch völlig neue Spalten aus anderen Spalten oder mit Funktionen erstellen.

Allgemeine Regel

Eine neue Spalte wird als Teil einer Abfrage wie folgt erstellt:

 <Ausdruck>  [AS]  <Name der neuen Spalte>

Ausdruck ist allgemein der Hinweis auf etwas, das an der betreffenden Stelle verwendet wird: ein konstanter Wert, der Inhalt einer oder mehrerer Spalten, eine Berechnung mit diesen Spalten, das Ergebnis einer Funktion oder das Ergebnis einer Unterabfrage. Der Spalte, die das Ergebnis aufnimmt, wird mit AS ein eigener Name, ein Alias zugewiesen; das AS kann auch entfallen.

In manchen DBMS ist bei berechneten Spalten die Angabe eines Alias Pflicht, und das Vergessen führt zu einem Fehler, manche erzeugen automatisch einen zufälligen Namen, wenn kein Alias eingegeben wird, und andere meckern erst, wenn man versucht, eine physische Tabelle mit einer namenlosen Spalte zu füllen.

Wenn eine Unterabfrage als berechnete Spalte benutzt wird, ist es unbedingt wichtig, dass sie – wie bei einer Funktion, Berechnung oder Verknüpfung – für jede Zeile der Hauptabfrage genau einen Wert ergibt. In dem untenstehenden Beispiel wird dies durch eine passende WHERE-Bedingung erreicht.

Hinweis: Fast alles, was in diesem Abschnitt über Ausdrücke, Funktionen und Konvertierungen gesagt wird, ist auch bei der Manipulation oder Verarbeitung von Daten wichtig. Die Funktionen und Konvertierungen wurden bereits in den Kapiteln Funktionen und Funktionen (2) behandelt.

Ergebnis von Berechnungen

Alle Werte einer Spalte werden für eine Berechnung verwendet, vorzugsweise eine der Grundrechenarten. Das folgende Beispiel errechnet für alle Zeilen der Tabelle Schadensfall aus dem Feld Schadenshoehe den Nettobetrag (ohne MWSt) und fügt alle weiteren Felder an:

SELECT Schadenshoehe / 1.19 AS Netto,
       *
  FROM Schadensfall;

Dieses Ergebnis kann auch für weitere Berechnungen verwendet werden, wenn auch nicht direkt mit dem Alias:

 Der Alias-Name der neuen Spalte Netto kann nicht nochmals verwendet werden.

SELECT Schadenshoehe         AS Brutto,
       Schadenshoehe / 1.19  AS Netto,
       Schadenshoehe - Netto AS MWSt
  FROM Schadensfall;

 Aber die Berechnung kann erneut zugewiesen werden.

SELECT Schadenshoehe                          AS Brutto,
       Schadenshoehe / 1.19                   AS Netto,
       Schadenshoehe - (Schadenshoehe / 1.19) AS MWSt
  FROM Schadensfall;

Man kann davon ausgehen, dass das DBMS die Abfrage soweit optimiert, dass die Berechnung tatsächlich nur einmal ausgeführt werden muss.

Zeichenketten verbinden und bearbeiten

Ebenso wie Zahlen für Berechnungen verwendet werden, gibt es die Bearbeitung von Zeichenketten. Das einfachste ist die Verknüpfung (Verkettung) von Strings durch den Operator || bzw. +:

SELECT Name || ', ' || Vorname AS Gesamtname
  FROM Mitarbeiter;

Ausgabe

Gesamtname
Müller, Kurt
Schneider, Daniela

In diesem Beispiel wird zwischen die Spalten noch das Komma und ein Leerzeichen als konstanter Text gesetzt.

Bei Textspalten mit fester Feldlänge, die wir in der Beispieldatenbank nicht haben, sollten überzählige Leerzeichen mit der Funktion TRIM abgeschnitten werden:

SELECT TRIM(Name) || ', ' || TRIM(Vorname) AS Gesamtname
  FROM Mitarbeiter;

Auf diese Weise kann man auch Teile von Texten übernehmen und den Rest abschneiden:

SELECT Name || ', ' || TRIM( SUBSTRING(Vorname FROM 1 FOR 1) ) || '.' AS Name_kurz
  FROM Mitarbeiter;

Ausgabe

Name_kurz
Müller, K.
Schneider, D.

Hinweis: Nach SQL-Standard liefert das Ergebnis von SUBSTRING einen Text von gleicher Länge wie der ursprüngliche Text; die jetzt zwangsläufig folgenden Leerzeichen müssen mit TRIM entfernt werden. Vielleicht ist Ihr DBMS „pfiffiger“ und macht das automatisch.

Ergebnis von Funktionen

In den bisherigen Beispielen werden Feldinhalte direkt modifiziert und ausgegeben. In den beiden Kapiteln über „Funktionen“ werden neue Informationen berechnet und unabhängig von vorhandenen Zeilen und Spalten ausgegeben:

SELECT COUNT(Farbe) AS Anzahl_Farbe 
  FROM Fahrzeug;

Bei diesem Beispiel mit einer Aggregatfunktion besteht das Ergebnis aus einer einzelnen Zahl in einer einzigen Zeile. Dies muss aber nicht so sein; vor allem mit Gruppierungen gibt es viele Varianten.

Viele andere Funktionen können besondere Abfragen ermöglichen oder stark erleichtern. EXTRACT nach Monat liefert die Liste aller Geburtstage, sortiert nach Monat:

SELECT Name, Vorname,
       EXTRACT(MONTH FROM Geburtsdatum) AS Monat,
       Geburtsdatum
  FROM Mitarbeiter
 ORDER BY Monat;

Ausgabe

NAME            VORNAME    MONAT  GEBURTSDATUM
--------------  ---------  -----  ------------
Aagenau         Karolin        1  02.01.1950
Langer          Norbert        1  13.01.1968
Wagner          Gaby           1  18.01.1970
Müller          Kurt           1  05.01.1977
Kolic           Ivana          2  14.02.1971
Schneider       Daniela        2  16.02.1980  // usw.

Unterabfragen

Diese werden ausführlich im Kapitel Unterabfragen besprochen. Hier soll deshalb ein Beispiel genügen, wie es für eine berechnete Spalte geeignet ist.

Aufgabe

Gesucht sind alle Abteilungen zusammen mit der Anzahl ihrer Mitarbeiter:

SELECT ab.ID,
       ab.Kuerzel,
       ab.Ort,
       ( SELECT COUNT(*) 
           FROM Mitarbeiter mi
          WHERE mi.Abteilung_ID = ab.ID
       ) AS Mitarbeiterzahl
  FROM Abteilung ab;

Von innen nach außen heißt das: In den Klammern gehört zu jeder ab.ID die Anzahl der Einträge in Mitarbeiter mit dieser Abteilung; dies wird mit den anderen Spalten von Abteilung zusammengefasst. Weil es ID in beiden Tabellen gibt, muss die gewünschte Tabelle ausdrücklich erwähnt werden.

Zusammenfassung

In diesem Kapitel bekamen wir verschiedene Erläuterungen dafür, wie aus Berechnungen, Verkettung von Zeichenketten oder als Ergebnis von Skalar- oder Spaltenfunktionen neue Spalten für die Ergebnistabelle entstehen.

Übungen

Übung 1

Zusatzspalten durch Berechnung

Zur Lösung

Zur Tabelle Versicherungsvertrag sollen Versicherungsnummer, Basisprämie und Prämiensatz angegeben sowie die aktuelle Prämie berechnet werden.

Übung 2

Zusatzspalten durch Berechnung

Zur Lösung

Geben Sie (unter Verwendung der Lösung von Übung 1) die Gesamtzahl der Versicherungsverträge sowie den Gesamtbetrag aller aktuellen Prämien an.

Übung 3

Zusatzspalten durch String-Verknüpfung

Zur Lösung

Erstellen Sie zur Tabelle Versicherungsnehmer per Abfrage die Druckanschrift:

• Zeile 1 mit Anrede (22 Zeichen, basierend auf der Spalte Geschlecht mit den Inhalten 'W' bzw. 'M') und der ID am rechten Rand (8 Zeichen rechtsbündig)
• Zeile 2 mit Vorname und Name
• Zeile 3 mit Straße und Hausnummer
• Zeile 4 mit PLZ und Ort

Benutzen Sie CASE, CAST, RPAD und CONCAT; auf TRIM können Sie verzichten.

Übung 4

Neue Spalten durch Spaltenfunktion

Zur Lösung

Bestimmen Sie, wie viele Fahrzeuge in Bochum ('BO') und wie viele in Gelsenkirchen ('GE') angemeldet sind.

Gruppierungen werden erst im nächsten Kapitel behandelt; verwenden Sie stattdessen UNION.

Lösungen

Lösung zu Übung 1

Zusatzspalten durch Berechnung

Zur Übung

select Vertragsnummer,
       Basispraemie,
       Praemiensatz,
       Basispraemie * Praemiensatz / 100 as Aktuell
  from Versicherungsvertrag;

Lösung zu Übung 2

Zusatzspalten durch Berechnung

Zur Übung

select COUNT(*) as Gesamtzahl,
       SUM(Basispraemie * Praemiensatz / 100) as Praemiensumme
  from Versicherungsvertrag;

Lösung zu Übung 3

Zusatzspalten durch String-Verknüpfung

Zur Übung

select CONCAT( CAST( ( CASE Geschlecht
                            WHEN 'M' THEN 'Herrn'
                            WHEN 'W' THEN 'Frau'
                            ELSE ''
                       END ) AS CHAR(22) ),
               RPAD( CAST( ID as VARCHAR(8) ), 8 )
             )                          as Zeile1,
       CASE
            WHEN Vorname is null THEN Name
            ELSE CONCAT(Vorname, ' ', Name)
       END                              as Zeile2,
       CONCAT(Strasse, ' ', Hausnummer) as Zeile3,
       CONCAT(PLZ,     ' ', Ort)        as Zeile4
  from Versicherungsnehmer vn;

Lösung zu Übung 4

Neue Spalten durch Spaltenfunktion

Zur Übung

select COUNT(*) as Anzahl, 'BO' as Kreis
  from Fahrzeug
 where Kennzeichen STARTING WITH 'BO-'
       union
select COUNT(*), 'GE'
  from Fahrzeug
 where Kennzeichen STARTING WITH 'GE-';

Für STARTING WITH gibt es Alternativen; wir haben verschiedentlich SUBSTRING mit POSITION o. a. verwendet.

---

Gruppierungen

Abfragen werden sehr häufig gruppiert, weil nicht nur einzelne Informationen, sondern auch Zusammenfassungen gewünscht werden. Durch die GROUP BY-Klausel im SELECT-Befehl werden alle Zeilen, die in einer oder mehreren Spalten den gleichen Wert enthalten, in jeweils einer Gruppe zusammengefasst.

Dies ist in der Regel nur dann sinnvoll, wenn in der Spaltenliste des SELECT-Befehls eine gruppenweise Auswertung, also eine der Spaltenfunktionen enthalten ist.

Syntax von GROUP BY

Die GROUP BY-Klausel hat folgenden allgemeinen Aufbau:

 GROUP BY <Spaltenliste>

Die Spaltenliste enthält, durch Komma getrennt, die Namen von einer oder mehreren Spalten. Für jede Spalte kann eine eigene Sortierung angegeben werden:

   <Spaltenname>
   -- oder
   <Spaltenname> COLLATE <Collation-Name>

Die Spalten in der Spaltenliste können meistens wahlweise mit dem Spaltennamen der Tabelle, mit dem Alias-Namen aus der Select-Liste oder mit Spaltennummer gemäß der Select-Liste (ab 1 gezählt) angegeben werden.

In der Regel enthält die Abfrage eine der Aggregatfunktionen und wird durch ORDER BY nach den gleichen Spalten sortiert.

Gruppierung bei einer Tabelle

Im einfachsten Fall werden Daten nach einer Spalte gruppiert und gezählt.

Aufgabe

Im folgenden Beispiel wird die Anzahl der Abteilungen für jeden Ort aufgeführt.

SELECT Ort,
       COUNT(*) AS Anzahl
  FROM Abteilung
 GROUP BY Ort
 ORDER BY Ort;

Ausgabe

ORT     ANZAHL
---     ------
Bochum     3
Dortmund   4
Essen      4
Herne      1

Aufgabe

Die folgende Abfrage listet auf, wie viele Mitarbeiter es in den Abteilungen und Raumnummern gibt:

SELECT Abteilung_ID AS Abt,
       Raum,
       COUNT(*) AS Anzahl
  FROM Mitarbeiter
 GROUP BY Abt, Raum
 ORDER BY Abt, Raum;

Ausgabe

ABT  RAUM  ANZAHL
---  ----  ------
 1   112      1
 1   113      1
 2   212      2
 3   312      1
 3   316      1
 4   412      2   // usw.

Am folgenden Beispiel wird die Gruppierung besonders deutlich.

Aufgabe

Berechne die mittlere Schadenshöhe für die Schadensfälle mit und ohne Personenschäden.

SELECT Verletzte, 
       AVG(Schadenshoehe) AS Mittlere_Schadenshoehe 
  FROM Schadensfall
 GROUP BY Verletzte;

Ausgabe

VERLETZTE  MITTLERE_SCHADENSHOEHE
    J          3.903,87
    N          1.517,45

Die Spalte Verletzte enthält entweder 'J' oder 'N' und ist verpflichtend, kann also keine NULL-Werte enthalten. Deshalb werden durch die GROUP BY-Anweisung eine oder zwei Gruppen gebildet. Für jede Gruppe wird der Mittelwert gesondert berechnet aus den Werten, die in der Gruppe vorkommen. In diesem Fall liefert die Funktion AVG also ein oder zwei Ergebnisse, abhängig davon, welche Werte in der Spalte Verletzte überhaupt vorkommen.

Zeilen, bei denen einer der Werte zur Gruppierung fehlt, oder Zeilen mit NULL-Werten werden als eigene Gruppe gezählt.

Gruppierung über mehrere Tabellen

Eine Gruppierung kann auch Felder aus verschiedenen Tabellen auswerten. Dafür sind zunächst die Voraussetzungen für die Verknüpfung mehrerer Tabellen zu beachten. Bitte beachten Sie das folgende Beispiel.

Aufgabe

Gesucht wird für jeden Fahrzeughersteller (mit Angabe von ID und Name) und Jahr die Summe der Schadenshöhe aus der Tabelle Schadensfall.

SELECT fh.ID AS Hersteller_ID,
       fh.Name AS Hersteller_Name,
       EXTRACT(YEAR FROM sf.Datum) AS Jahr,
       SUM(sf.Schadenshoehe) AS Schadenssumme
  FROM Schadensfall   sf
       JOIN Zuordnung_SF_FZ    zu ON sf.ID = zu.Schadensfall_ID
       JOIN Fahrzeug           fz ON fz.ID = zu.Fahrzeug_ID
       JOIN Fahrzeugtyp        ft ON ft.ID = fz.Fahrzeugtyp_ID
       JOIN Fahrzeughersteller fh ON fh.ID = ft.Hersteller_ID
 GROUP BY Hersteller_ID, Hersteller_Name, Jahr
 ORDER BY Jahr, Hersteller_ID;

Ausgabe

HERSTELLER_ID  HERSTELLER_NAME  JAHR  SCHADENSSUMME
-------------  ---------------  ----  -------------
       9       Volvo            2007       2.066,00
      10       Renault          2007       5.781,60
      11       Seat             2007       1.234,50
       2       Opel             2008       1.438,75
      11       Seat             2008       1.983,00
       9       Volvo            2009       4.092,15
      10       Renault          2009         865,00

Ausgangspunkt ist die Tabelle Schadensfall, weil aus deren Einträgen die Summe gebildet werden soll. Durch JOIN werden nacheinander die verknüpften Tabellen herangezogen, und zwar jeweils durch die ID auf die Verknüpfung: Schadensfall → Zuordnung → Fahrzeug → Fahrzeugtyp → Hersteller. Dann stehen ID und Name aus der Tabelle Fahrzeughersteller zur Verfügung, die für die Gruppierung gewünscht werden.

Zur Gruppierung genügt eigentlich die Verwendung von Hersteller_ID. Aber man möchte sich natürlich den Herstellernamen anzeigen lassen. Allerdings gibt es einen Fehler, wenn man den Namen nur in der SELECT-Liste benutzt und in der GROUP BY-Liste streicht:

 Fehler

SELECT fh.ID AS Hersteller_ID,
       fh.Name AS Hersteller_Name,
       EXTRACT(YEAR FROM sf.Datum) AS Jahr,
       SUM(sf.Schadenshoehe) AS Schadenssumme
  FROM Schadensfall   sf
       join ... (wie oben)
 group by Hersteller_ID, Jahr  /* oder Variante 2: */
 group by Hersteller_ID, EXTRACT(YEAR FROM sf.Datum)
 order by Jahr, Hersteller_ID

Ausgabe

Ungültiger Ausdruck in der Select-Liste (fehlt entweder in einer Aggregatfunktion oder in der GROUP BY-Klausel).

Siehe dazu die Erläuterungen unter Einschränkungen.

Einschränkungen

Wie das letzte Beispiel zeigt, muss die GROUP BY-Klausel gewisse Bedingungen erfüllen. Auch dafür gilt: Jedes DBMS weicht an manchen Punkten vom Standard ab.

• Jeder Spaltenname der SELECT-Auswahl, der nicht zu einer Aggregatfunktion gehört, muss auch in der GROUP BY-Klausel benutzt werden.

Diese Bedingung wird im letzten Beispiel verletzt: Hersteller_Name steht in der SELECT-Liste, aber nicht in der GROUP BY-Klausel. In diesem Fall ist eine Änderung einfach, weil ID und Name des Herstellers gleichwertig sind. Übrigens erlaubt MySQL auch die Auswahl von Feldern, die in der GROUP BY-Klausel nicht genannt sind.

Umgekehrt ist es in der Regel möglich, eine Spalte per GROUP BY zu gruppieren, ohne die Spalte selbst in der SELECT-Liste zu verwenden.

• GROUP BY kann Spalten der Tabellen, abgeleiteter Tabellen oder VIEWs in der FROM-Klausel oder der JOIN-Klausel enthalten.
• Sie kann keiner Spalte entsprechen, die das Ergebnis einer Funktion (genauer: einer numerischen Berechnung, einer Aggregatfunktion oder einer benutzerdefinierten Funktion) sind. Notfalls kann die Funktion in der GROUP-Klausel wiederholt werden (siehe im letzten Beispiel Variante 2).

Dies entspricht der gleichen Einschränkung, die bei den unter „Ergebnis von Berechnungen“ im Kapitel Berechnete Spalten genannt ist.

Mit der Beispieldatenbank sind keine passenden Beispiele möglich; wir müssen uns deshalb auf fiktive Tabellen und Spalten beschränken:

 Die folgenden Abfragen sind nicht zulässig.

SELECT Spalte1,  Spalte2            FROM T GROUP BY Spalte1 + Spalte2;
SELECT Spalte1 + constant + Spalte2 FROM T GROUP BY Spalte1 + Spalte2;

 Die folgenden Abfragen sind zulässig.

SELECT Spalte1,  Spalte2            FROM T GROUP BY Spalte1, Spalte2;
SELECT Spalte1 + Spalte2            FROM T GROUP BY Spalte1, Spalte2;
SELECT Spalte1 + Spalte2            FROM T GROUP BY Spalte1+ Spalte2;
SELECT Spalte1 + Spalte2 + constant FROM T GROUP BY Spalte1, Spalte2;

• GROUP BY kann nicht benutzt werden in einem SELECT-Befehl mit folgenden Bedingungen:
  • Der Befehl enthält eine INTO-Klausel (d. h. er wird benutzt, um einzelne Zeilen zu verarbeiten – dies wird in dieser Einführung nicht erläutert).
  • Der Befehl enthält eine Unterabfrage oder eine VIEW, die selbst mit einer GROUP BY- oder HAVING-Klausel arbeitet.

• Jeder SELECT-Befehl darf einschließlich aller Unterabfragen höchstens eine GROUP BY-Klausel enthalten.

Zu dieser Bedingung ist den Autoren kein vernünftiges Beispiel eingefallen. Eines, das dafür konstruiert wurde, hat Firebird trotz klarer Verletzung ausgeführt, wahrscheinlich weil es sich wegen der anderen Bedingungen sowieso nur um jeweils eine Zeile handelte und keine Gruppierung erforderlich war.

Erweiterungen

Zwischensummen mit CUBE

Diese Erweiterung steht nur in manchen DBMS zur Verfügung. Es soll deshalb ein kurzer Hinweis genügen.

GROUP BY CUBE (<spaltenliste>)

Mit diesem Befehl werden zusätzlich zu den normalerweise von GROUP BY erstellten Zeilen auch Zwischensummen in das Ergebnis aufgenommen. Für jede mögliche Kombination von Spalten in der <spaltenliste> wird eine eigene GROUP BY-Summenzeile zurückgegeben.

Erläuterungen und Beispiele sind zu finden z. B. unter GROUP BY (Microsoft) und weiteren Links.

Gesamtsummen mit ROLLUP

Auch diese Erweiterung steht nur in manchen DBMS zur Verfügung. Es soll wiederum ein kurzer Hinweis genügen.

GROUP BY <spaltenliste> WITH ROLLUP

Mit diesem Befehl werden zusätzlich zu den von GROUP BY erstellten Zeilen auch Gesamtsummen in das Ergebnis aufgenommen.

Erläuterungen und Beispiele sind zu finden z. B. unter GROUP BY (MySQL, englisch).

Ergebnis mit HAVING einschränken

Diese Erweiterung ist eine selbständige Klausel des SELECT-Befehls und hat eigentlich nichts mit der GROUP BY-Klausel zu tun. Sie ist eine Ergänzung zu den Aggregatfunktionen, wird deshalb überwiegend bei Gruppierungen verwendet und folgt ggf. direkt danach.

GROUP BY <spaltenliste>
HAVING <bedingungen>

Dieser Befehl dient dazu, nicht alle Ergebnisse der Auswahl in die Ausgabe zu übernehmen, sondern nur diejenigen, die den zusätzlichen Bedingungen entsprechen.

Im folgenden Beispiel (vergleiche oben unter „Gruppierung bei einer Tabelle“) wird festgestellt, an welchen Orten sich genau eine Abteilung befindet.

SELECT Ort,
       COUNT(*) AS Anzahl
  FROM Abteilung
 GROUP BY Ort
 HAVING COUNT(*) = 1
 ORDER BY Ort;

Ausgabe

Herne      1

Bitte beachten Sie, dass der Alias-Name nicht verwendet werden kann, sondern die Aggregatfunktion bei HAVING erneut aufgeführt werden muss.

Hinweis: Die HAVING-Klausel wird als letzter Teil des SELECT-Befehls ausgeführt. Es ist deshalb zu empfehlen, alle Einschränkungen vorher zu regeln, z. B. als Teil von WHERE-Bedingungen. Nur wenn – wie bei Aggregatfunktionen – diese Einschränkung erst am Schluss geprüft werden kann, ist HAVING zu benutzen.

 Möglich, aber nicht günstig.

SELECT Abteilung_ID, COUNT(*)
  FROM MITARBEITER 
  GROUP BY Abteilung_ID
  HAVING Abteilung_ID < 3;

 Besseres Verfahren, weil weniger Datensätze geprüft werden müssen.

SELECT Abteilung_ID, COUNT(*)
  FROM MITARBEITER 
  WHERE Abteilung_ID < 3
  GROUP BY Abteilung_ID;

Zusammenfassung

In diesem Kapitel lernten wir Einzelheiten über die Gruppierung bei Abfragen.

• Dies wird meistens gleichzeitig mit ORDER BY und in Verbindung mit Aggregatfunktionen verwendet.
• Die Gruppierung ist auch über mehrere Tabellen hinweg möglich.
• Einige wichtige Einschränkungen sind zu beachten; vor allem sind die Felder aus der Spaltenliste in der Regel auch unter GROUP BY aufzuführen.

Die HAVING-Klausel kann das Abfrageergebnis einschränken, sollte aber zurückhaltend benutzt werden.

Übungen

Übung 1

Definitionen

Zur Lösung

Welche der folgenden Feststellungen sind wahr, welche sind falsch?

1. GROUP BY kann nur zusammen mit (mindestens) einer Spaltenfunktion benutzt werden.
2. GROUP BY kann nur auf „echte“ Spalten angewendet werden, nicht auf berechnete Spalten.
3. In der GROUP BY-Klausel kann ein Spaltenname ebenso angegeben werden wie ein Spalten-Alias.
4. Die WHERE-Klausel kommt vor der GROUP BY-Klausel.
5. Folgende Gruppierung nach den ersten zwei Ziffern der PLZ ist zulässig.
   

   select PLZ, COUNT(*)
   

     from Versicherungsnehmer vn
   

    group by SUBSTRING(vn.PLZ from 1 for 2)
   

    order by 1
   

6. HAVING darf nur zusammen mit einer Gruppierung verwendet werden.

Übung 2

Gruppierung für 1 Tabelle

Zur Lösung

Bestimmen Sie die Anzahl der Kunden (Versicherungsnehmer) in jedem Briefzentrum (d. h. die Ziffern 1 und 2 der PLZ).

Übung 3

Gruppierung für 1 Tabelle

Zur Lösung

Bestimmen Sie, wie viele Fahrzeuge in jedem Kreis angemeldet sind.

Übung 4

Gruppierung für mehrere Tabellen

Zur Lösung

Bestimmen Sie, wie viele Fahrzeugtypen pro Hersteller registriert sind, und nennen Sie Namen und Land der Hersteller.

Hinweis: Erstellen Sie zunächst eine Abfrage für Anzahl plus Hersteller-ID und verknüpfen Sie das Ergebnis mit der Tabelle Hersteller.

Übung 5

Gruppierung für mehrere Tabellen

Zur Lösung

Bestimmen Sie – gruppiert nach Jahr des Schadensfalls und Kreis des Fahrzeugs – die Anzahl der Schadensfälle. Es sollen bei den Fahrzeugen nur Schadensfälle mit einem Schuldanteil von mehr als 50 [Prozent] berücksichtigt werden.

Lösungen

Lösung zu Übung 1

Definitionen

Zur Übung

Richtig sind die Aussagen 3, 4. Falsch sind die Aussagen 1, 2, 5, 6.

Lösung zu Übung 2

Gruppierung für 1 Tabelle

Zur Übung

select SUBSTRING(vn.PLZ from 1 for 2), COUNT(*)
  from Versicherungsnehmer vn
 group by 1
 order by 1

Hinweis: Hierbei handelt es sich um die korrekte Version zur Frage 5 aus Übung 1.

Lösung zu Übung 3

Gruppierung für 1 Tabelle

Zur Übung

select SUBSTRING(Kennzeichen from 1 for POSITION('-', Kennzeichen)-1 ) as Kreis, 
       COUNT(*) as Anzahl
  from Fahrzeug fz
 group by 1
 order by 1;

Hinweis: Hierbei handelt es sich um die vollständige Version der letzten Übung zu „berechneten Spalten“.

Lösung zu Übung 4

Gruppierung für mehrere Tabellen

Zur Übung

select Name, Land, Anzahl
  from (
  select ft.Hersteller_ID as ID, Count(ft.Hersteller_ID) as Anzahl
    from Fahrzeugtyp ft
   group by ft.Hersteller_ID
       ) temp
       join Fahrzeughersteller fh
            on fh.ID = temp.ID
 order by Name;

Lösung zu Übung 5

Gruppierung für mehrere Tabellen

Zur Übung

select EXTRACT(YEAR from sf.Datum) as Jahr,
       SUBSTRING(Kennzeichen from 1 for POSITION('-', Kennzeichen)-1 ) as Kreis, 
       COUNT(*)
  from Zuordnung_SF_FZ zu 
       right join Fahrzeug     fz on fz.ID = zu.Fahrzeug_ID
       inner join Schadensfall sf on sf.ID = zu.Schadensfall_ID
 where zu.Schuldanteil > 50
 group by 1, 2
 order by 1, 2;

Erläuterungen: Die Tabelle der Zuordnungen ist kleiner als die diejenige der Fahrzeuge, und darauf bezieht sich die WHERE-Bedingung; deshalb ist sie als Haupttabelle am sinnvollsten. Wegen der Kennzeichen benötigen wir einen JOIN auf die Tabelle Fahrzeug. Wegen des Datums des Schadensfalls für die Gruppierung nach Jahr benötigen wir einen JOIN auf die Tabelle Schadensfall.

Siehe auch

Ergänzende Informationen sind in diesen Kapiteln zu finden:

• Datentypen informiert auch über besondere Sortierungen einer einzelnen Spalte.
• Mehrere Tabellen verknüpfen

---

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

Ergebnisse einfacher Abfragen

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

---

Erstellen von Views

VIEWs sind Abfragen, die in der Datenbank als Objekt fest gespeichert sind. Sie können als virtuelle Tabellen verstanden werden, deren Inhalt und Struktur auf anderen Tabellen oder Views basieren, und können in (fast) jedem SELECT-Befehl anstelle einer „echten“ Tabelle verwendet werden.

Allgemeine Hinweise

Bei einer View wird die Abfrage in der Datenbank gespeichert, aber nicht das Ergebnis. Bei jedem neuen Aufruf der View wird die dahinterliegende Abfrage neu ausgeführt, denn sie soll ja das Ergebnis anhand der aktuellen Daten bestimmen.

Die Abfragen, auf denen Views basieren, können grundsätzlich alle Klauseln wie eine normale Abfrage enthalten. Somit ist es möglich, bestimmte Daten in einer View zu selektieren und zu gruppieren. Hierbei können die Daten aus mehreren Tabellen oder Views selektiert werden.

Je nach DBMS und Situation kann eine einzelne Klausel der View unwirksam sein oder zu unklaren Ergebnissen führen.

• Eine ORDER BY-Klausel der View wird ignoriert, wenn der SELECT-Befehl, der sie benutzt, selbst eine Sortierung verwendet.
• Bei einer Beschränkung durch LIMIT o. ä. weiß das DBMS oft nicht, nach welchen Regeln diese Beschränkung verwirklicht werden soll.
• WHERE-Bedingungen können nur fest eingebaut werden, aber nicht mit variablen Parametern.

Mit Views wird die stark differenzierte Struktur eines Auswahlbefehls vereinfacht. Die View wird mit ihrer komplexen Abfrage einmal angelegt, und die Nutzer können die Daten dieser View immer wieder abfragen.

Weiterhin können Views genutzt werden, um den Zugriff auf bestimmte Daten einzuschränken. Nutzer können Zugriff nur auf bestimmte Views bekommen. Somit lässt sich der Zugriff für einzelne Nutzer auf bestimmte Daten (Spalten und Datensätze) beschränken.

Eine View anlegen und benutzen

Views werden mit dem Befehl CREATE VIEW mit folgender Syntax angelegt.

 CREATE VIEW <View-Name> 
     [ ( <Spaltennamen> ) ]
     AS <Select-Ausdruck> ;

Zu dieser Definition gehören folgende Bestandteile:

• CREATE VIEW kennzeichnet den Befehl.
• Unter <View-Name> ist eine Bezeichnung anzugeben, unter der die View in einem SELECT-Befehl angesprochen wird. Dieser Name muss eindeutig sein und darf auch kein Name einer „echten“ Tabelle sein.
• Als <Select-Ausdruck> wird ein (beliebiger) SELECT-Befehl eingetragen.
• Es wird empfohlen, möglichst bei allen Spalten mit einem Alias zu arbeiten.
• Diese können wahlweise vor dem AS in Klammern angegeben werden oder (wie üblich) Teil des <Select-Ausdruck>s sein.

Die View wird dann wie jede Tabelle benutzt, z. B. einfach:

 SELECT * FROM <View-Name>

Oder auch als Teil einer komplexen Abfrage:

 SELECT <irgendwas>
   FROM <Tabelle>
        JOIN <View-Name> ON    /* usw. */

Eine einfache View

Im einfachsten Fall greifen wir auf eine einfache Verknüpfung zweier Tabellen zu und verbinden dies mit einer festen Suchbedingung.

Aufgabe

Erstelle eine View, die eine Liste aller Fahrzeugtypen deutscher Hersteller anzeigt.

CREATE VIEW Deutscher_Fahrzeugtyp
    AS SELECT DISTINCT ft.Bezeichnung AS Fahrzeugtyp, fh.Name AS Hersteller
         FROM Fahrzeugtyp ft 
              join Fahrzeughersteller fh on ft.Hersteller_ID = fh.ID
        WHERE fh.Land = 'Deutschland';

Die Abfrage basiert auf den beiden Tabellen Fahrzeugtyp und Fahrzeughersteller. Es werden nur die Spalten Bezeichnung und Name abgefragt; durch die WHERE-Klausel wird das Ergebnis auf Fahrzeuge deutscher Hersteller beschränkt. Für die Spalten werden Spalten-Aliase genutzt.

Diese View wird dann wie eine „normale“ Tabellen in Abfragen genutzt.

SELECT * FROM Deutscher_Fahrzeugtyp order by Hersteller;

Ausgabe

FAHRZEUGTYP  HERSTELLER
-----------  ----------
A3           Audi
A4           Audi
325          BMW
525          BMW
Z3           BMW
Fiesta       Ford
Focus        Ford    /* usw. */

In diesem Fall kann die ORDER BY-Klausel ebensogut Teil der View sein; das untersuchen wir später noch.

Eine View mit variabler Selektion

Es klappt leider nicht, in eine View eine WHERE-Klausel einen (variablen) Parameter einzubauen, der erst im SELECT mit einem konkreten Wert versehen wird.

Aufgabe

Gesucht wird eine Abfrage über die Mitarbeiter einer Abteilung; am Anfang soll der Abteilungsleiter stehen, danach alphabetisch die betreffenden Mitarbeiter. Die Nummer der Abteilung soll nicht fest vorgegeben werden, sondern variabel sein.

 Fehler

create view Mitarbeiter_in_Abteilung
as select Personalnummer, Name, Vorname, Geburtsdatum
     from Mitarbeiter
    WHERE Abteilung_ID = ?
    order by Ist_Leiter, Name, Vorname;

Ausgabe

Commit nicht möglich
Invalid token.

Auch Alternativen für das Fragezeichen führen nicht zum Ziel. Es bleibt nur ein kleiner Umweg, nämlich die Abteilung_ID in der View zu berücksichtigen und später für WHERE zu nutzen:

 So geht es.

create view Mitarbeiter_in_Abteilung
     ( Pers, Name, Vorname, Geburtsdatum, Abt )
as select Personalnummer, Name, Vorname, Geburtsdatum, Abteilung_ID
     from Mitarbeiter
    order by Ist_Leiter, Name, Vorname;

Damit können alle Angaben einer bestimmten Abteilung geholt werden; die Spalte Abt bleibt zur Verdeutlichung stehen:

select * from Mitarbeiter_in_Abteilung
 where Abt = 5;

Ausgabe

PERS    NAME           VORNAME    GEBURTSDATUM  ABT
------  -------------  ---------  ------------  ---
50001   Pohl           Helmut     27.10.1980      5
50002   Braun          Christian  05.09.1966      5
50004   Kalman         Aydin      17.12.1976      5
50003   Polovic        Frantisek  26.11.1961      5

Und siehe da: zuerst kommt der Abteilungsleiter, danach die anderen Mitarbeiter in alphabetischer Reihenfolge.

Hinweis: Eine Alternative zu einer VIEW mit variabler WHERE-Bedingung ist eine „StoredProcedure“, die diese Abfrage enthält und einen Wert als Parameter entgegennimmt; sie wird in einem späteren Kapitel behandelt.

Probleme mit der Sortierung

Ändern wir die obige View deutscher Fahrzeuge dahin, dass die Sortierung nach Hersteller fest eingebaut wird.

• Bitte beachten Sie: Wenn Sie oben die View Deutscher_Fahrzeugtyp fest gespeichert haben, müssen Sie in diesem Abschnitt einen anderen Namen verwenden oder stattdessen etwas wie CREATE OR ALTER (siehe die DBMS-Dokumentation) benutzen.

CREATE VIEW Deutscher_Fahrzeugtyp ( Typ, Firma )
    AS SELECT DISTINCT ft.Bezeichnung, fh.Name as Firma
         FROM Fahrzeugtyp ft 
              join Fahrzeughersteller fh on ft.Hersteller_ID = fh.ID
        WHERE fh.Land = 'Deutschland'
        order by Firma;

Bitte beachten Sie, dass in diesem Fall der Spalten-Alias Firma auch Teil des SELECT-Befehls sein muss, damit er in der ORDER BY-Klausel bekannt ist.

Jetzt wird die Liste wahlweise mit oder ohne Sortierung abgerufen:

SELECT * FROM Deutscher_Fahrzeugtyp;                -- automatisch sortiert nach Firma
SELECT * FROM Deutscher_Fahrzeugtyp ORDER BY Typ;   -- speziell sortiert nach Typ

Views in Verbindung mit JOIN

Die obige Verknüpfung „Fahrzeugtyp plus Hersteller“ benötigen wir in der Praxis ständig, nicht nur in der konkreten Abfrage nach deutschen Herstellern. Bisher – zum Beispiel mit OUTER JOIN – haben wir beide Tabellen separat per JOIN eingebunden, mussten aber immer auf die Art des JOINs aufpassen. Das kann man einmalig durch eine fiktive Tabelle Fahrzeugart, also eine VIEW mit den benötigten Informationen steuern.

Eine solche VIEW erfüllt mehrere Wünsche:

• Die eigentlichen Informationen werden getrennt gespeichert; es ist nicht nötig, bei jedem Fahrzeugtyp den Hersteller und sein Herkunftsland aufzuführen. Wie wir aus der Wirtschaftspolitik des Jahres 2009 wissen, kann sich ein Herkunftsland durchaus ändern; nach den Regeln der Normalisierung ist die separate Tabelle der Hersteller nicht nur sinnvoll, sondern notwendig.
• Bei jeder Abfrage des Fahrzeugtyps erhalten wir sofort auch den Hersteller.
• Jede solche Abfrage wird einfacher, weil eine Tabelle weniger benötigt wird.
• Das DBMS kennt seine VIEWs und hat sie „von Haus aus“ optimiert; also wird jede solche Abfrage auch schneller ausgeführt.

Diese Aussage gilt nicht unbedingt bei jeder Abfrage und jedem DBMS. Aber nach allen Erkenntnissen über interne Datenbankstrukturen kann man davon ausgehen.

Das obige „einfache Beispiel“ der VIEW müssen wir nur wenig umschreiben:

Aufgabe

Bereite eine (fiktive) Tabelle Fahrzeugart vor mit allen relevanten Informationen aus den Tabellen Fahrzeugtyp und Fahrzeughersteller.

CREATE VIEW Fahrzeugart
     ( ID, Bezeichnung, Hersteller, Land )
    AS SELECT ft.ID, ft.Bezeichnung, fh.Name, fh.Land
         FROM Fahrzeugtyp ft 
              join Fahrzeughersteller fh on ft.Hersteller_ID = fh.ID;

Für den Anwender sieht es tatsächlich so aus, als hätten wir eine einfache Tabelle mit allen Angaben:

select * from Fahrzeugart
 order by Land, Hersteller, Bezeichnung;

Ausgabe

ID  BEZEICHNUNG       HERSTELLER     LAND
--  ----------------  -------------  -----------
19  C30               Volvo
18  S40               Volvo
12  A3                Audi           Deutschland
13  A4                Audi           Deutschland
 9  325               BMW            Deutschland
10  525               BMW            Deutschland
11  Z3                BMW            Deutschland

Damit kann das letzte der Beispiele zu OUTER JOIN vereinfacht werden.

Aufgabe

Hole alle Dienstwagen (ggf. mit den zugehörigen Mitarbeitern) und nenne dazu alle Fahrzeugdaten.

SELECT 
    mi.Personalnummer AS MitNr, 
    mi.Name, mi.Vorname, 
    dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS TypID,
    fa.Bezeichnung AS Typ, fa.Hersteller
  FROM Dienstwagen dw
       LEFT  JOIN Mitarbeiter        mi ON mi.ID = dw.Mitarbeiter_ID
       INNER JOIN Fahrzeugart        fa ON fa.ID = dw.Fahrzeugtyp_ID;

Ausgabe

MITNR    NAME       VORNAME    DIW  KENNZEICHEN   TYPID  TYP              HERSTELLER
-------  ---------  ---------  ---  -----------   -----  ---------------  -------------
80001    Schindler  Christina    8  DO-WB 428        14  A160             Mercedes-Benz
90001    Janssen    Bernhard     9  DO-WB 429        14  A160             Mercedes-Benz
100001   Grosser    Horst       10  DO-WB 4210       14  A160             Mercedes-Benz
110001   Eggert     Louis       11  DO-WB 4211       14  A160             Mercedes-Benz
120001   Carlsen    Zacharias   12  DO-WB 4212       14  A160             Mercedes-Benz
                                13  DO-WB 111        16  W211 (E-Klasse)  Mercedes-Benz
50002    Braun      Christian   14  DO-WB 352         2  Golf             Volkswagen
50003    Polovic    Frantisek   15  DO-WB 353         3  Passat           Volkswagen
50004    Kalman     Aydin       16  DO-WB 354         4  Kadett           Opel
80002    Aliman     Zafer       17  DO-WB 382         2  Golf             Volkswagen
80003    Langer     Norbert     18  DO-WB 383         3  Passat           Volkswagen
80004    Kolic      Ivana       19  DO-WB 384         4  Kadett           Opel

Einige kleine Änderungen vereinfachen alles: Die Tabelle Fahrzeugtyp wird durch die View Fahrzeugart ersetzt; der JOIN auf Fahrzeughersteller entfällt ersatzlos. Lediglich zur Klarheit ändern wir Tabellen-Alias und Spaltennamen.

Eine View ändern oder löschen

Die Änderung einer VIEW wird unterschiedlich gehandhabt.

• Üblich ist das „normale“ ALTER VIEW.
• Firebird behandelt eine Änderung mit RECREATE als Löschung und anschließende Neuaufnahme.

Die Löschung einer View erfolgt mit dem üblichen Befehl DROP VIEW.

DROP VIEW Deutscher_Fahrzeugtyp;

Hierbei wird nur die View als Objekt in der Datenbank gelöscht. Die Tabellen und Daten in den Tabellen, auf denen die View basiert, werden davon nicht beeinflusst – sie werden nicht gelöscht.

Zusammenfassung

• Views sind Abfragen, die in der Datenbank als Objekt gespeichert werden.
• Views können die Komplexität für den Anwender reduzieren.
• Views können für eine detaillierte Zugriffskontrolle genutzt werden.
• Views werden in Abfragen wie jede Tabelle benutzt.
• Sie werden mit CREATE VIEW erstellt und mit DROP VIEW gelöscht.

Übungen

Die Formulierung „eine View kontrollieren“ meint: Mit einer geeigneten Abfrage soll überprüft werden, ob die View richtig erstellt worden ist.

Übung 1

Definitionen

Zur Lösung

Welche der folgenden Feststellungen sind richtig, welche sind falsch?

1. Eine View ist wie eine „normale“ Abfrage, deren Bestandteile in der Datenbank fest gespeichert werden.
2. Das Ergebnis dieser Abfrage wird gleichzeitig gespeichert und steht damit beim nächsten Aufruf der View sofort zur Verfügung.
3. Eine ORDER BY-Klausel kann in einer View immer benutzt werden.
4. Eine ORDER BY-Klausel ist in einer View nicht erforderlich.
5. Wenn diese Klausel in einer View benutzt wird, hat diese Sortierung Vorrang vor einer ORDER BY-Klausel in dem SELECT-Befehl, der die View benutzt.
6. Wenn ein SELECT-Befehl komplexe JOINs oder andere Klauseln benutzt und häufiger benutzt wird, ist es sinnvoll, ihn in einer View zu kapseln.
7. Wenn ein Anwender nicht alle Daten sehen darf, ist es notwendig, die Zugriffsrechte auf die Spalten zu beschränken; diese Beschränkung kann nicht über eine View gesteuert werden.
8. Eine View kann in einem SELECT-Befehl in der FROM-Klausel anstatt einer Tabelle aufgerufen werden.
9. Eine View kann nicht in einem JOIN benutzt werden.

Übung 2

Eine View benutzen

Zur Lösung

Skizzieren Sie eine Abfrage, durch die eine beliebige View benutzt werden kann.

Übung 3

Eine einfache View erstellen

Zur Lösung

Bei der Suche nach Dienstwagen sollen mit der View Dienstwagen_Anzeige immer auch angezeigt werden:

• Name und Vorname des Mitarbeiters
• ID und Bezeichnung seiner Abteilung
• der Fahrzeugtyp (nur als ID)

Stellen Sie sicher, dass auch nicht-persönliche Dienstwagen immer angezeigt werden, und kontrollieren Sie das Ergebnis durch eine Abfrage ähnlich diesem Muster:

SELECT * FROM Dienstwagen_Anzeige 
 WHERE ( Abt_ID BETWEEN 5 AND 8 ) or ( Mi_Name is null );

Übung 4

Mehrere Tabellen und Views verbinden

Zur Lösung

Erweitern Sie die vorstehende View so, dass mit Hilfe der View Fahrzeugart auch Bezeichnung, Hersteller und Land angezeigt werden. Kontrollieren Sie das Ergebnis durch die o. g. Abfrage.

Dies ist ein Beispiel dafür, dass eine View bei Abfragen genauso wie eine „echte“ Tabelle benutzt werden kann.

Übung 5

Eine VIEW auf mehrere Tabellen

Zur Lösung

Erstellen Sie eine Sicht Vertrag_Anzeige, bei der zu jedem Vertrag angezeigt werden:

• ID, Vertragsnummer, Abschlussdatum, Art (als Text)
• Name, Vorname des Mitarbeiters
• Name, Vorname des Versicherungsnehmers
• Kennzeichen des Fahrzeugs

Übung 6

Eine VIEW auf mehrere Tabellen

Zur Lösung

Erweitern Sie die vorstehende View so, dass mit Hilfe der View Fahrzeugart auch Bezeichnung, Hersteller und Land angezeigt werden.

Übung 7

Eine View abschnittsweise kontrollieren

Zur Lösung

Erstellen Sie eine Abfrage, sodass für einen Teil der Verträge die vorstehende View kontrolliert wird.

Übung 8

Eine weitere VIEW auf mehrere Tabellen

Zur Lösung

Erstellen Sie eine Sicht Schaden_Anzeige, bei der zu jedem an einem Schadensfall beteiligten Fahrzeug angezeigt werden:

• ID, Datum, Gesamthöhe eines Schadensfalls
• Kennzeichen und Typ des beteiligten Fahrzeugs
• Anteiliger Schaden
• ID des Versicherungsvertrags

Übung 9

Eine weitere VIEW auf mehrere Tabellen

Zur Lösung

Erweitern Sie die vorstehende View so, dass mit Hilfe der View Fahrzeugart auch Bezeichnung, Hersteller und Land sowie Vertragsnummer und ID des Versicherungsnehmers angezeigt werden.

Übung 10

Eine View zur Auswertung einer View

Zur Lösung

Erstellen Sie eine weitere View so, dass die vorstehende View für alle Schadensfälle des aktuellen Jahres benutzt wird.

Lösungen

Lösung zu Übung 1

Definitionen

Zur Übung

Richtig sind die Aussagen 1, 3, 4, 6, 8. Falsch sind die Aussagen 2, 5, 7, 9.

Lösung zu Übung 2

Eine View benutzen

Zur Übung

SELECT * FROM <View-Name>;

Lösung zu Übung 3

Eine View erstellen

Zur Übung

create view Dienstwagen_Anzeige
     ( Kennzeichen, TypId,
       Mi_Name, Mi_Vorname,
       Ab_ID, Ab_Name )
    as select dw.Kennzeichen, dw.Fahrzeugtyp_ID,
              mi.Name, mi.Vorname,
              mi.Abteilung_ID,
              ab.Bezeichnung
         from Dienstwagen dw
              left join Mitarbeiter mi
                        on mi.ID = dw.Mitarbeiter_ID
              left join Abteilung ab
                        on ab.ID = mi.Abteilung_ID;

Erläuterung: LEFT JOIN in beiden Fällen wird benötigt, damit auch NULL-Werte, nämlich die nicht-persönlichen Dienstwagen angezeigt werden.

Lösung zu Übung 4

Mehrere Tabellen und Views verbinden

Zur Übung

alter view Dienstwagen_Anzeige
    ( Kennzeichen, TypId,
      Typ, Fz_Hersteller, Fz_Land,
      Mi_Name, Mi_Vorname,
      Ab_ID, Ab_Name )
   as select dw.Kennzeichen, dw.Fahrzeugtyp_ID,
             fa.Bezeichnung, fa.Hersteller, fa.Land,
             mi.Name, mi.Vorname,
             mi.Abteilung_ID,
             ab.Bezeichnung
        from Dienstwagen dw
             left  join Mitarbeiter mi
                        on mi.ID = dw.Mitarbeiter_ID
             left  join Abteilung ab
                        on ab.ID = mi.Abteilung_ID
             inner join Fahrzeugart fa
                        on fa.ID = dw.Fahrzeugtyp_ID;

Lösung zu Übung 5

Eine VIEW auf mehrere Tabellen

Zur Übung

create view Vertrag_Anzeige
   ( ID, Vertragsnummer, Abschlussdatum, Art,
     Mi_Name, Mi_Vorname,
     Vn_Name, Vn_Vorname,
     Kennzeichen )
  as select vv.ID, vv.Vertragsnummer, vv.Abschlussdatum,
            CASE vv.Art
                 WHEN 'TK' THEN 'Teilkasko'
                 WHEN 'VK' THEN 'Vollkasko'
                 ELSE 'Haftpflicht'
            END,
            mi.Name, mi.Vorname,
            vn.Name, vn.Vorname,
            fz.Kennzeichen
       from Versicherungsvertrag vv
            join Mitarbeiter mi
                 on mi.ID = vv.Mitarbeiter_ID
            join Versicherungsnehmer vn
                 on vn.ID = vv.Versicherungsnehmer_ID
            join Fahrzeug fz
                 on fz.ID = vv.Fahrzeug_ID;

Hinweis: Weil die Zusatzangaben Pflicht sind, können wir einheitlich mit INNER JOIN arbeiten.

Lösung zu Übung 6

Eine VIEW auf mehrere Tabellen

Zur Übung

alter view Vertrag_Anzeige
   ( ID, Vertragsnummer, Abschlussdatum, Art,
     Mi_Name, Mi_Vorname,
     Vn_Name, Vn_Vorname,
     Kennzeichen, Typ, Hersteller, Land )
  as select vv.ID, vv.Vertragsnummer, vv.Abschlussdatum,
            CASE vv.Art
                 WHEN 'TK' THEN 'Teilkasko'
                 WHEN 'VK' THEN 'Vollkasko'
                 ELSE 'Haftpflicht'
            END,
            mi.Name, mi.Vorname,
            vn.Name, vn.Vorname,
            fz.Kennzeichen, fa.Bezeichnung, fa.Hersteller, fa.Land
       from Versicherungsvertrag vv
            join Mitarbeiter mi
                 on mi.ID = vv.Mitarbeiter_ID
            join Versicherungsnehmer vn
                 on vn.ID = vv.Versicherungsnehmer_ID
            join Fahrzeug fz
                 on fz.ID = vv.Fahrzeug_ID
            join Fahrzeugart fa
                 on fa.ID = fz.Fahrzeugtyp_ID;

Lösung zu Übung 7

Eine View abschnittsweise kontrollieren

Zur Übung

SELECT * FROM Vertrag_Anzeige 
 WHERE EXTRACT(YEAR from Abschlussdatum) <= 1990;

Lösung zu Übung 8

Eine weitere VIEW auf mehrere Tabellen

Zur Übung

create view Schaden_Anzeige
   ( ID, Datum, Gesamtschaden,
     Kennzeichen, Typ,
     Schadensanteil,
     VV_ID )
  as select sf.ID, sf.Datum, sf.Schadenshoehe,
            fz.Kennzeichen, fz.Fahrzeugtyp_ID,
            zu.Schadenshoehe,
            vv.ID
       from Zuordnung_SF_FZ zu
            join Schadensfall sf
                 on sf.ID = zu.Schadensfall_ID
            join Fahrzeug fz
                 on fz.ID = zu.Fahrzeug_ID
            join Versicherungsvertrag vv
                 on fz.ID = vv.Fahrzeug_ID;

Lösung zu Übung 9

Eine weitere VIEW auf mehrere Tabellen

Zur Übung

alter view Schaden_Anzeige
   ( ID, Datum, Gesamtschaden,
     Kennzeichen, Typ, Hersteller, Land,
     Schadensanteil,
     VV_ID, Vertragsnummer, VN_ID )
  as select sf.ID, sf.Datum, sf.Schadenshoehe,
            fz.Kennzeichen, fa.Bezeichnung, fa.Hersteller, fa.Land,
            zu.Schadenshoehe,
            vv.ID, vv.Vertragsnummer, vv.Versicherungsnehmer_ID
       from Zuordnung_SF_FZ zu
            join Schadensfall sf
                 on sf.ID = zu.Schadensfall_ID
            join Fahrzeug fz
                 on fz.ID = zu.Fahrzeug_ID
            join Versicherungsvertrag vv
                 on fz.ID = vv.Fahrzeug_ID
            join Fahrzeugart fa
                 on fa.ID = fz.Fahrzeugtyp_ID;

Lösung zu Übung 10

Eine View zur Auswertung einer View

Zur Übung

create view Schaden_Anzeige_Jahr
  as select *
       from Schaden_Anzeige
      where EXTRACT(YEAR from Datum) = EXTRACT(YEAR from CURRENT_DATE);

Siehe auch

Ergänzende Informationen gibt es in den folgenden Kapiteln:

• StoredProcedure
• Verknüpfung mehrerer Tabellen bei OUTER JOIN

---

Lizenz

---