6. Vertiefung der Sprache SQL

6.1. Introduction

In diesem Kapitel stellen wir

weitere Syntaxvarianten des Befehls SELECT vor, die ihn zu einem sehr leistungsstarken Abfragebefehl machen, insbesondere für die gleichzeitige Abfrage mehrerer Tabellen.
erweiterte Syntaxvarianten bereits behandelter Befehle

Zur Veranschaulichung der verschiedenen Befehle arbeiten wir mit den folgenden Tabellen, die für die Verwaltung von Bestellungen in einem Buchvertriebssystem (PME) verwendet werden:

6.1.1. die Tabelle CLIENTS

Sie speichert Informationen über die Kunden des PME:

`ID`	Nummer, die den Kunden eindeutig identifiziert – Primärschlüssel
`NOM`	Name des Kunden
`STATUT`	I = Privatperson, E = Unternehmen, A = Behörde
`PRENOM`	Vorname bei Privatpersonen
`CONTACT`	Name des Ansprechpartners beim Kunden (im Falle eines Unternehmens oder einer Behörde)
`RUE`	Adresse des Kunden – Straße
`VILLE`	Stadt
`CPOSTAL`	Postleitzahl
`TELEPH`	Telefon
`DEPUIS`	Seit wann sind Sie Kunde?
`DEBITEUR`	O (Ja), wenn der Kunde dem Unternehmen Geld schuldet, und N (Nein) andernfalls.

6.1.2. Die Tabelle ARTICLES

Sie speichert Informationen zu den verkauften Produkten, in diesem Fall Bücher. Ihre Struktur ist wie folgt:

`ISBN`	Nummer, die ein Buch eindeutig identifiziert (ISBN = International Standard Book Number) – Primärschlüssel
`TITRE`	Titel des Buches
`CODEDITEUR`	Code zur eindeutigen Identifizierung eines Verlags
`AUTEUR`	Name des Autors
`RESUME`	Zusammenfassung des Buches
`QTEANCOUR`	Verkaufte Stückzahl im Jahr
`QTEANPREC`	Verkaufte Menge im Vorjahr
`DERNVENTE`	Datum des letzten Verkaufs
`QTERECUE`	Menge der letzten Lieferung
`DERNLIV`	Datum der letzten Lieferung
`PRIXVENTE`	Verkaufspreis
`COUT`	Einkaufskosten
`MINCDE`	Mindestbestellmenge
`MINSTOCK`	Mindestbestandsmenge
`QTESTOCK`	Lagerbestand

Der Inhalt könnte wie folgt lauten:

6.1.3. die Tabelle COMMANDES

Sie speichert Informationen zu den von Kunden aufgegebenen Bestellungen. Ihre Struktur ist wie folgt:

`NOCMD`	Nummer, die eine Bestellung eindeutig identifiziert – Primärschlüssel
`IDCLI`	Kundennummer des Auftraggebers – Ausländerschlüssel – Artikelnummer CLIENTS(ID)
`DATE_CMD`	Datum der Erfassung dieser Bestellung
`ANNULE`	O (Ja), wenn die Bestellung storniert wurde, und N (Nein) andernfalls.

6.1.4. Die Tabelle DETAILS

Sie enthält die Details einer Bestellung, d. h. die Artikelnummern und Mengen der bestellten Bücher. Ihre Struktur ist wie folgt:

`NOCMD`	Bestellnummer – Fremdschlüssel, der auf die Spalte NOCMD der Tabelle COMMANDES verweist
`ISBN`	Nummer des bestellten Buches – Fremdschlüssel, der auf die Spalte ISBN der Tabelle LIVRES verweist
`QTE`	Bestellte Menge

Der Inhalt könnte wie folgt aussehen:

Oben ist zu sehen, dass die Bestellung Nr. 3 (NOCMD) drei Bücher betrifft. Das bedeutet, dass der Kunde drei Bücher gleichzeitig bestellt hat. Die Referenzdaten dieses Kunden finden sich in der Tabelle [COMMANDES], aus der hervorgeht, dass die Bestellung Nr. 3 vom Kunden Nr. 5 aufgegeben wurde. Aus der Tabelle [CLIENTS] geht hervor, dass es sich bei dem Kunden Nr. 5 um das Unternehmen NetLogos aus Segré handelt.

6.2. Die Bestellung SELECT

Wir möchten hier unser Wissen über die Bestellung SELECT vertiefen, indem wir neue Syntaxvarianten dieser Bestellung vorstellen.

6.2.1. Syntax einer Abfrage über mehrere Tabellen

`syntaxe`	SELECT Spalte1, Spalte2, ... FROM Tabelle1, Tabelle2, ..., Tabellep WHERE condition ORDER BY ...
`action`	Das Neue daran ist, dass die Spalten colonne1, colonne2, ... aus mehreren Tabellen table1, table2, ... stammen. Wenn zwei Tabellen Spalten mit demselben Namen haben, wird die Mehrdeutigkeit durch die Notation tablei.colonnej beseitigt. „condition“ kann sich auf Spalten aus verschiedenen Tabellen beziehen.

Funktionsweise

`1`	Die kartesische Produkt-Tabelle aus table1, table2, …, tablep wird erstellt. Wenn ni die Anzahl der Zeilen von tablei ist, hat die erstellte Tabelle somit n1n2...np* Zeilen, die alle Spalten der verschiedenen Tabellen enthalten.
`2`	Die condition der WHERE wird auf diese Tabelle angewendet. Dadurch wird eine neue Tabelle erzeugt
`3`	Diese ist gemäß der in ORDER angegebenen Sortierreihenfolge sortiert.
`4`	Die in SELECT angeforderten Spalten werden angezeigt.

Beispiele

Es werden die zuvor vorgestellten Tabellen verwendet. Man möchte die Details der Bestellungen einsehen, die nach dem 25. September aufgegeben wurden:

SQL>select details.nocmd,isbn,qte from commandes,details
  where commandes.datecmd>'25-sep-91'
  and details.nocmd=commandes.nocmd

Es ist zu beachten, dass hinter „FROM“ die Namen aller Tabellen angegeben werden, auf deren Spalten Bezug genommen wird. Im vorangegangenen Beispiel gehören die ausgewählten Spalten alle zur Tabelle „DETAILS“. Die Bedingung bezieht sich jedoch auf die Tabelle „COMMANDES“. Daher muss letztere hinter „FROM“ angegeben werden. Der Vorgang, bei dem die Übereinstimmung von Spalten zweier verschiedener Tabellen geprüft wird, wird oft als Gleichheitsverknüpfung bezeichnet.

Die Abfrage SELECT hätte auch wie folgt geschrieben werden können:

SQL> select details.nocmd,isbn,qte from commandes
    inner join details on details.nocmd=commandes.nocmd
    where commandes.datecmd>'25-sep-91'

Fahren wir mit unseren Beispielen fort. Wir möchten das gleiche Ergebnis wie zuvor, jedoch mit dem Titel des bestellten Buches anstelle seiner Nummer ISBN:

SQL>select commandes.nocmd, articles.titre, details.qte
  from commandes,articles,details
  where commandes.datecmd>'25-sep-91'
  and details.nocmd=commandes.nocmd
  and details.isbn=articles.isbn

Das gleiche Ergebnis wird mit der folgenden, weniger gut lesbaren Abfrage SQL erzielt:

SQL> select details.nocmd,articles.titre,details.qte from details
    inner join commandes on details.nocmd=commandes.nocmd
    inner join articles on  details.isbn=articles.isbn
    where commandes.datecmd>'25-sep-91'

Oben werden zwei interne Verknüpfungen mit der Tabelle [DETAILS] durchgeführt:

eine mit der Tabelle [COMMANDES], um auf das Bestelldatum eines Buches zuzugreifen
die eine mit der Tabelle [ARTICLES], um Zugriff auf den Titel des bestellten Buches zu erhalten

Außerdem benötigen wir den Namen des Kunden, der die Bestellung aufgibt:

SQL>select commandes.nocmd, articles.titre, qte ,clients.nom
  from commandes,details,articles,clients
  where commandes.datecmd>'25-sep-91'
  and details.nocmd=commandes.nocmd
  and details.isbn=articles.isbn
  and commandes.idcli=clients.id

Außerdem sollen die Bestelldaten und eine Sortierung dieser Daten in absteigender Reihenfolge angezeigt werden:

SQL>select commandes.nocmd, commandes.datecmd, articles.titre, qte ,clients.nom
    from commandes,details,articles,clients
    where commandes.datecmd>'25-sep-91'
    and details.nocmd=commandes.nocmd
    and details.isbn=articles.isbn
    and commandes.idcli=clients.id
    order by commandes.datecmd descending

Hier sind einige Regeln, die bei Verknüpfungen zu beachten sind:

Hinter SELECT werden die Spalten angegeben, die in der Anzeige erscheinen sollen. Wenn die Spalte in mehreren Tabellen vorhanden ist, wird ihr der Name der Tabelle vorangestellt.
Hinter FROM werden alle Tabellen angegeben, die von SELECT durchsucht werden sollen, d. h. die Tabellen, zu denen die Spalten gehören, die hinter SELECT und WHERE stehen.

6.2.2. Die Selbstverknüpfung

Wir möchten wissen, welche Bücher einen Verkaufspreis haben, der höher ist als der des Buches „Using SQL“:

SQL>select a.titre from articles a, articles b
  where b.titre='Using SQL'
  and a.prixvente>b.prixvente

Die beiden Tabellen der Verknüpfung sind hier identisch: die Tabelle articles. Um sie voneinander zu unterscheiden, werden ihnen Aliase zugewiesen: from articles a, articles b. Der Alias der ersten Tabelle lautet a und der der zweiten b. Diese Syntax kann auch verwendet werden, wenn die Tabellen unterschiedlich sind. Bei Verwendung eines Alias muss dieser im gesamten Befehl anstelle der Tabelle, auf die er verweist, verwendet werden: SELECT.

6.2.3. Äußere Verknüpfung

Wir möchten die Kunden ermitteln, die im September etwas gekauft haben, einschließlich des Bestelldatums. Die übrigen Kunden werden ohne dieses Datum angezeigt:

SQL>select clients.nom,commandes.datecmd from clients
    left outer join commandes  on clients.id=commandes.idcli
    where datecmd between '01-sep-91' and '30-sep-91'

Wir sind überrascht, dass wir hier nicht das richtige Ergebnis erhalten. Eigentlich sollten alle Kunden in der Tabelle [CLIENTS] enthalten sein, was jedoch nicht der Fall ist. Wenn man sich die Funktionsweise der äußeren Verknüpfung vor Augen führt, wird klar, dass die Kunden, die nichts gekauft haben, einer leeren Zeile der Tabelle COMMANDES zugeordnet wurden und somit einem leeren Datum (Wert NULL in der Terminologie SQL) zugeordnet. Dieses Datum erfüllt dann die für das Datum festgelegte Bedingung nicht, und der entsprechende Kunde wird nicht angezeigt. Versuchen wir es anders:

SQL>select clients.nom,commandes.datecmd from clients
    left outer join commandes  on clients.id=commandes.idcli
    where (commandes.datecmd between '01-sep-91' and '30-sep-91')
        or (commandes.datecmd is null)

Diesmal erhalten wir die richtige Antwort auf unsere Frage.

6.2.4. Verschachtelte Abfragen

syntaxe

SELECT Spalte[s] FROM Tabelle[s]

WHERE Ausdruck Operator Abfrage

ORDER BY ...

fonctionnement

requête ist ein Befehl SELECT, der eine Gruppe von 0, 1 oder mehreren Werten liefert. Man erhält dann eine Bedingung WHERE vom Typ

Ausdruck Operator (val1, val2, ..., vali)

expression und vali müssen vom gleichen Typ sein. Wenn die Abfrage einen einzigen Wert liefert, gelangt man zu einer Bedingung vom Typ

Ausdruck Operator Wert

, die wir gut kennen. Liefert die Abfrage eine Liste von Werten, können die folgenden Operatoren verwendet werden:

IN

expression IN (val1, val2, ..., vali): wahr, wenn expression einen der Werte aus der Liste vali annimmt.

NOT IN

Umkehrung von IN

ANY

muss mit =, !=, >, >=, <, <= eingeleitet werden

expression >= ANY (val1, val2, .., valn): wahr, wenn expression größer oder gleich einem der Werte vali aus der Liste ist

ALL

muss von =, !=, >, >=, <, <= eingeleitet werden

expression >= ALL (val1, val2, .., valn): wahr, wenn der Ausdruck >= allen Werten vali aus der Liste ist

EXISTS

Abfrage: wahr, wenn die Funktion requête mindestens eine Zeile zurückgibt.

Beispiele

Wir greifen die bereits durch eine Äquijoin-Abfrage gelöste Frage wieder auf: Titel anzeigen, deren Verkaufspreis höher ist als der des Buches „Using SQL“.

SQL>select titre from ARTICLES
    where prixvente > (select prixvente from ARTICLES where titre='Using SQL')

Diese Lösung erscheint intuitiver als die mit der Gleichheitsverknüpfung. Man führt zunächst eine Filterung nach „SELECT“ durch und anschließend eine zweite Filterung auf das erhaltene Ergebnis. Auf diese Weise lassen sich mehrere Filterungen nacheinander durchführen.

Wir möchten die Titel ermitteln, deren Verkaufspreis über dem durchschnittlichen Verkaufspreis liegt:

SQL> select titre from ARTICLES
    where prixvente > (select avg(prixvente) from ARTICLES)

Welche Kunden haben die Titel bestellt, die das Ergebnis der vorherigen Abfrage sind?

SQL>select distinct idcli from COMMANDES,DETAILS
    where DETAILS.isbn in
    (select isbn from ARTICLES where prixvente
        > (select avg(prixvente) from ARTICLES))
    and COMMANDES.nocmd=DETAILS.nocmd

Erläuterungen

In der Tabelle DETAILS werden die Codes ISBN ausgewählt, die zu den Büchern gehören, deren Preis über dem Durchschnittspreis der Bücher liegt.
In den im vorherigen Schritt ausgewählten Zeilen ist der Kundencode IDCLI nicht enthalten. Er befindet sich in der Tabelle COMMANDES. Die Verknüpfung zwischen den beiden Tabellen erfolgt über die Bestellnummer NOCMD, woraus sich die Gleichverknüpfung COMMANDES.nocmd=DETAILS.nocmd ergibt.
Ein und derselbe Kunde kann eines der betreffenden Bücher mehrmals gekauft haben; in diesem Fall kommt sein Code IDCLI mehrmals vor. Um dies zu vermeiden, wird das Schlüsselwort DISTINCT hinter SELECT gesetzt. DISTINCT beseitigt generell Duplikate in den Ergebniszeilen eines SELECT.
Um den Namen des Kunden zu erhalten, müssten wir eine zusätzliche Äquijoin zwischen den Tabellen COMMANDES und CLIENTS durchführen, wie die folgende Abfrage zeigt.

SQL> select distinct CLIENTS.nom from COMMANDES,DETAILS,CLIENTS
    where DETAILS.isbn in
    (select isbn from ARTICLES where prixvente
        > (select avg(prixvente) from ARTICLES))
    and COMMANDES.nocmd=DETAILS.nocmd
    and COMMANDES.IDCLI=CLIENTS.ID

Kunden finden, die seit dem 24. September keine Bestellung aufgegeben haben:

SQL>select nom from CLIENTS
    where clients.id not in
    (select distinct commandes.idcli from commandes where datecmd>='24-sep-91')

Wir haben gesehen, dass man Zeilen auch anders als mit der Klausel WHERE filtern kann: indem man die Klausel HAVING in Verbindung mit den Klauseln GROUP und BY verwendet. Die Klausel HAVING filtert Zeilengruppen.

Ähnlich wie bei der Klausel WHERE lautet die Syntax


     HAVING expression opérateur requête

möglich, mit der bereits genannten Einschränkung, dass expression einer der Ausdrücke expri der Klausel


     GROUP BY expr1, expr2, ...

Beispiele

Wie hoch sind die Verkaufsmengen für Bücher mit mehr als 200F?

Zeigen wir zunächst die verkauften Mengen nach Titel an:

SQL>select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
    where DETAILS.isbn=ARTICLES.isbn
    group by titre

Nun filtern wir die Titel:

SQL> select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
    where DETAILS.isbn=ARTICLES.isbn
    group by titre
    having titre in (select titre from ARTICLES where prixvente>200)

Vielleicht hätte man es noch deutlicher formulieren können:

SQL>select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
    where DETAILS.isbn=ARTICLES.isbn
    and ARTICLES.prixvente>200
    group by titre

6.2.5. Verknüpfte Abfragen

Bei verschachtelten Abfragen gibt es eine übergeordnete Abfrage (die äußerste Abfrage) und eine untergeordnete Abfrage (die innerste Abfrage). Die übergeordnete Abfrage wird erst ausgewertet, wenn die untergeordnete Abfrage vollständig ausgewertet wurde.

Verknüpfte Abfragen weisen bis auf folgende Kleinigkeit dieselbe Syntax auf: Die untergeordnete Abfrage führt eine Verknüpfung mit der Tabelle der übergeordneten Abfrage durch. In diesem Fall wird die Kombination aus übergeordneter und untergeordneter Abfrage für jede Zeile der übergeordneten Tabelle wiederholt ausgewertet.

Beispiel

Wir greifen das Beispiel wieder auf, in dem wir die Namen der Kunden abrufen möchten, die seit dem 24. September keine Bestellung aufgegeben haben:

SQL> 
select nom from clients
    where not exists
        (select idcli from commandes
            where datecmd>='24-sep-91'
                and commandes.idcli=clients.id)

Die übergeordnete Abfrage wird auf die Tabelle clients angewendet. Die untergeordnete Abfrage führt eine Verknüpfung zwischen den Tabellen clients und commandes durch. Es handelt sich also um eine korrelierte Abfrage. Für jede Zeile der Tabelle clients wird die Tochterabfrage ausgeführt: Sie sucht nach dem Kundencode id in den Bestellungen, die nach dem 24. September getätigt wurden. Wenn sie keinen findet (not exists), wird der Name des Kunden angezeigt. Anschließend wird zur nächsten Zeile der Tabelle clients übergegangen.

6.2.6. Auswahlkriterien für die Abfrage SELECT

Wir haben bereits mehrfach gesehen, dass es möglich ist, durch unterschiedliche Schreibweisen der Tabelle SELECT dasselbe Ergebnis zu erzielen. Nehmen wir ein Beispiel: Anzeige der Kunden, die etwas bestellt haben:

Verbindung

SQL>
select distinct nom from clients,commandes
    where clients.id=commandes.idcli

Verschachtelte Abfragen

SQL> 
select nom from clients
    where id in (select idcli from commandes)

liefert das gleiche Ergebnis.

Korrelierte Abfragen

SQL>
select nom from clients
    where exists (select * from commandes where commandes.idcli=clients.id)

liefert das gleiche Ergebnis.

Die Autoren Christian MAREE und Guy LEDANT schlagen in ihrem Buch „SQL, Einführung, Programmierung und Beherrschung“ einige Auswahlkriterien vor:

Leistung

Der Benutzer weiß nicht, wie das SGBD es „schafft“, die von ihm angeforderten Ergebnisse zu finden. Er wird daher erst durch Erfahrung herausfinden, dass eine bestimmte Schreibweise leistungsfähiger ist als eine andere. MAREE und LEDANT bestätigen aus Erfahrung, dass korrelierte Abfragen im Allgemeinen langsamer zu sein scheinen als verschachtelte Abfragen oder Verknüpfungen.

Formulierung

Die Formulierung mittels verschachtelter Abfragen ist oft lesbarer und intuitiver als eine Verknüpfung. Sie ist jedoch nicht immer anwendbar. Dabei sind insbesondere zwei Punkte zu beachten:

Die Tabellen, zu denen die Argumentspalten von SELECT (SELECT col1, col2, ...) gehören, müssen hinter dem Schlüsselwort FROM genannt werden. Es wird dann das kartesische Produkt dieser Tabellen gebildet, was als Join bezeichnet wird.

Wenn die Abfrage Ergebnisse aus einer einzigen Tabelle anzeigt und die Filterung der Zeilen dieser Tabelle die Abfrage einer weiteren Tabelle erfordert, können verschachtelte Abfragen verwendet werden.

6.3. Syntaxerweiterungen

Aus Gründen der Übersichtlichkeit haben wir meist vereinfachte Syntaxen der verschiedenen Befehle vorgestellt. In diesem Abschnitt stellen wir die erweiterten Syntaxen vor. Sie sind selbsterklärend, da sie denen des bereits ausführlich behandelten Befehls SELECT ähneln.

INSERT

`syntaxe1`	INSERT INTO table (col1, col2, ..) VALUES (val1, val2, ...)
`syntaxe2`	INSERT INTO table (Spalte1, Spalte2, ...) (requête)
`explication`	Diese beiden Syntaxen wurden vorgestellt

DELETE

`syntaxe1`	DELETE FROM table WHERE condition
`explication`	Diese Syntax ist bekannt. Hinzu kommt, dass die Bedingung eine Abfrage mit der folgenden Syntax enthalten kann: WHERE Ausdruck Operator (Abfrage)

UPDATE

`syntaxe1`	UPDATE table SET Spalte1=Ausdruck1, Spalte2=Ausdruck2, ... WHERE condition
`explication`	Diese Syntax wurde bereits vorgestellt. Hinzu kommt, dass die Bedingung eine Abfrage mit der Syntax WHERE Ausdruck Operator (Abfrage) enthalten kann.

`syntaxe2`	UPDATE table SET (col1, col2, ..) = Abfrage1, (cola, colb, ..) = Abfrage2, ... WHERE condition
`explication`	Die den verschiedenen Spalten zugewiesenen Werte können aus einer Abfrage stammen.