Index   Kapitel 1   Kapitel 2   Kapitel 3   Kapitel 4   Kapitel 5   Kapitel 6   Kapitel 7  
Beispiel - Datenbank  



5 Unterabfragen (Subqueries)

Eine Unterabfrage ist eine Abfrage, die in einer Abfrage eingebettet ist. Spezielle Operatoren für die Einbettung von Unterabfragen sind ANY und EXISTS.

Unteranfragen mit dem IN-operator

Syntax : SELECT <Spalte1>
FROM <Tabelle>
WHERE <Spalte2>     IN     (SELECT <Spalte3>)
                     FROM <Tabelle>
                     [WHERE <Bedingung>])
Hinweis : Spalte 1, Spalte 2 und Spalte 3 müssen nicht unterschiedlich sein.
Beispiel : Gesucht werden die Nachnamen aller Mitarbeiter, die am Projekt '3' arbeiten.
SELECT nach_name
FROM Mitarbeiter
WHERE pers_nr     IN (SELECT pers_nr
                FROM     Arbeitet_an
                WHERE    pro_nr = 3)
Hinweis : Jede Anfrage, die eine Unteranfrage mit dem IN-Operator enthält, ist als Equijoin formulierbar (gilt nicht für NOT IN). So ist die folgende Abfrage äquivalent zur letzten.
Beispiel : SELECT M.nach_name
FROM Mitarbeiter M, Arbeitet_an A
WHERE M.mnr = A.mnr        AND
A.pnr = 3
Hinweis : In einer Unterabfrage kann wieder eine Unterabfrage eingebettet werden.
Beispiel : Gesucht werden die Personalnummern aller Mitarbeiter, die an einem Projekt arbeiten, dessen Standort Köln ist.
SELECT pers_nr
FROM Mitarbeiter
WHERE pers_nr IN (SELECT pers_nr
        FROM      Arbeitet_an
        WHERE    pnr IN (SELECT pnr
                         FROM      Projekt
                         WHERE    ort = 'Koeln' )
Hinweis : Um das Ergebnis einer Unterbfrage zu negieren, kann der NOT-Operator benutzt werden.
Beispiel : Gesucht werden die Nachnamen aller Mitarbeiter, die nicht am Projekt '3' arbeiten.
SELECT nach_name
FROM Mitarbeiter
WHERE pers_nr   NOT IN (SELECT pers_nr
         FROM      Arbeitet_an
         WHERE    pro_nr = 3)

  

Unterabfragen mit Vergleich (und ANY oder ALL)

Beispiel : Gesucht werden die Personalnummern aller Mitarbeiter, die am Projekt mit Standort Köln arbeiten.
SELECT pers_nr
FROM Arbeitet_an
WHERE pro_nr = (SELECT pro_nr
         FROM      Projekt
         WHERE    ort = 'Koeln')
Hinweis : Ein einfacher Vergleich mit dem Ergebnis einer Unteranfrage ist nur dann erlaubt, wenn die Unteranfrage genau einen Wert liefert. Ist dies nicht der Fall (oder nicht sicher), so muß zusätzlich angegeben werden, ob der Vergleich bezüglich einem (ANY) oder allen (ALL) Werten der Unteranfrage gelten muß. Das letzte Beispiel führt also nur dann zu einem (korrekten) Ergebnis, wenn nur ein Projekt den Standort Köln aufweist.
Beispiel : Gesucht wird die Personalnummer der Mitarbeiter, die mindestens soviel verdienen wie die Kollegen.
SELECT pers_nr
FROM Mitarbeiter
WHERE gehalt >= ALL    (SELECT gehalt
             FROM    Mitarbeiter)
Die folgende Abfrage liefert das gleiche Ergebnis:
SELECT pers_nr
FROM Mitarbeiter
WHERE gehalt >= (SELECT MAX (gehalt))
             FROM    Mitarbeiter)
Beispiel : Gesucht wird die Personalnummer der Mitarbeiter, die nicht das kleinste Gehalt aufweisen.
SELECT pers_nr
FROM Mitarbeiter
WHERE gehalt > ANY (SELECT gehalt
             FROM    Mitarbeiter)
Die folgende Abfrage liefert das gleiche Ergebnis:
SELECT pers_nr
FROM Mitarbeiter
WHERE gehalt > (SELECT MIN (gehalt))
             FROM    Mitarbeiter)

  

Einfache Unterabfrage mit EXISTS

Syntax : SELECT <Spalte1>
FROM <Tabelle>
WHERE EXISTS (SELECT <Spalte3>
         FROM       <Tabelle>
         [WHERE <Bedingung>])

Die Bedeutung von EXISTS-Unterabfragen wird erst im Kontext korrelierter Unterabfragen deutlich. Eine korrelierte Unterabfrage ist eine Unterabfrage mit einer Spalte, die zu einer Tabelle der Oberabfrage gehört. Daraus resultiert, daß das Ergebnis der Unterabfrage vom gerade in der Oberabfrage betrachteten Tupel abhängt. Bei korrelierten Unterabfragen ist es also im Gegensatz zu einfachen Unterabfragen notwendig, daß die Unterabfrage für jedes Tupel aus der Oberabfrage neu durchlaufen wird.

Beispiel : Gesucht werden die Nachnamen aller Mitarbeiter, die an mindestens einem Projekt arbeiten.
SELECT nach_name
FROM Mitarbeiter M
WHERE EXISTS (SELECT *
         FROM      Arbeitet_an
            WHERE      M.pers_nr = pers_nr )
Hinweis : Abfragen mit EXISTS-Unterabfragen lassen sich durch Verbund-Abfragen ersetzen (dies gilt nicht für Abfragen mit einer NOT EXISTS-Unterabfrage). Das folgende Beispiel ist daher äquivalent zu dem vorhergehenden Beispiel.
Beispiel : Gesucht werden die Projektnummern aller Projekte, für die es mindestens einen Mitarbeiter gibt, der mehr als 25 Stunden am Projekt arbeitet.
SELECT pro_nr
FROM Projekt P
WHERE EXISTS (SELECT *
             FROM       Arbeitet_an
             WHERE    stunden > 25 and
                               P.pro_nr = pro_nr)
                      alternativ :

SELECT pro_nr
FROM Projekt P, Arbeitet_an Aa
WHERE P.pro_nr = Aa.pro_nr     AND
stunden > 25

  

Korrelierte Unterabfrage mit NOT EXISTS (relationale Division)

Die NOT EXISTS - Unterabfrage spielt eine wichtige Rolle bei der Formulierung einer relationalen Division. Beispiel: Gesucht werden die Mitarbeiter, die an allen Projekten mitarbeiten. In relationaler Algebra sieht diese Anfrage folgendermaßen aus:
Ergebnis := (PJ<pers_nr, pro_nr> arbeitet_an) DV (PJ<pro_nr> projekt).
Die Formulierung einer relationalen Division in SQL erfordert eine Umformulierung. Dabei wird die Aussage " Für alle x gilt P(x) " in die äquivalente Aussage: "Es gibt kein x, für das nicht P(x) gilt" umformuliert.

Beispiel : Gesucht werden die Mitarbeiter, für die es kein Projekt gibt, an dem sie nicht mitarbeiten.
SELECT pers_nr
FROM Mitarbeiter M
WHERE NOT EXISTS (SELECT *
       FROM      Projekt P
       WHERE NOT EXISTS (SELECT *
                               FROM        Arbeitet_an
                               WHERE      pro_nr = P.pro_nr      AND
                                                  pers_nr = M.pers_nr ))
Hinweis : Eine andere Möglichkeit diese Anfrage zu formulieren, nutzt die Aggregatfunktion count(). Findet man einen Mitarbeiter, der an n verschiedenen Projekten mitarbeitet und insgesamt existieren nur n Projekte, dann muß er folglich an allen Projekten dieser Abteilung mitarbeiten.
SELECT pers_nr
FROM Mitarbeiter M
WHERE pers_nr IN (SELECT pers_nr
            FROM        Arbeitet_an A, Projekt P
            WHERE        A.pro_nr = P.pro_nr
            GROUP BY     pers_nr
            HAVING       COUNT (DISTINCT pro_nr) = (SELECT COUNT (*)
                                                                                       FROM Projekt))
Beispiel : Finde die Abteilungsleiter, deren Untergebenen alle in ihrer Abteilung arbeiten. Erster Schritt ist wieder die Umformulierung der "für alle"-Bedingung: "Finde jeden Abteilungsleiter, der keinen Untergebenen hat, der nicht in seiner Abteilung arbeitet."
SELECT leiter_nr
FROM Abteilung A
WHERE NOT EXISTS    (SELECT *
                   FROM      Mitarbeiter M
                   WHERE    M.vorg_nr = A.leiter_nr     AND
                                    A.anr <> M.anr)

  

Equijoin und NULL-Werte (OUTER JOIN)

Ein Tupel, das in der Verbundspalte einen NULL-Wert aufweist oder kein "Pendant" in der anderen Tabelle hat, fällt beim Equijoin heraus. Wenn aber auch die Tupel einer Tabelle, die nicht im Join verbunden werden konnten, in der Ergebnistabelle enthalten sein sollen (Outer Join), dann muß die Abfrage wie im folgenden Beispiel erweitert werden.

Beispiel : Gewünscht wird eine Tabelle mit den Nachnamen der Mitarbeiter und den Vornamen ihrer Angehörigen. Mitarbeiter, die keine Angehörige haben, sollen in der zweiten Spalte ein "-" bekommen.
SELECT nach_name, angeh_vorname
FROM Mitarbeiter, Angehoeriger_von
WHERE pers_nr = mitarb_pers_nr
UNION
SELECT nach_name, '-'
FROM Mitarbeiter
WHERE NOT EXISTS    (SELECT *
                   FROM        Angehoeriger_von
                   WHERE      pers_nr = mitarb_pers_nr)

                    oder

SELECT nach_name, angeh_vorname
FROM Mitarbeiter, Angehoeriger_von
WHERE pers_nr = mitarb_pers_nr
UNION
SELECT nach_name, '-'
FROM Mitarbeiter
WHERE pers_nr NOT IN (SELECT mitarb_pers_nr
                       FROM     Angehoeriger_von)
Hinweis : In vielen SQL-Versionen (Oracle,Sybase) wurde für diesen Fall ein neuer Verbund-Operator , der Outer-Join , definiert, der oft durch ein "*=" dargestellt wird. Die fehlenden Spalten erhalten dann den Wert NULL.
SELECT nach_name, angeh_vorname
FROM Mitarbeiter, Angehoeriger_von
WHERE pers_nr *= mitarb_pers_nr

  


Index   Kapitel 1   Kapitel 2   Kapitel 3   Kapitel 4   Kapitel 5   Kapitel 6   Kapitel 7  
Beispiel - Datenbank