Level 15: EXISTS und korrelierte Unterabfragen

Lektion

Die Unterabfragen aus Level 14 sind unabhängig: Man kann sie allein ausführen. Eine korrelierte Unterabfrage verwendet dagegen eine Spalte der Hauptabfrage. Sie kann nicht allein laufen: Sie wird für jede Zeile der Hauptabfrage neu berechnet.

Beispiel: die Angestellten anzeigen, die mehr verdienen als der Durchschnitt ihrer Abteilung. Der Vergleichsdurchschnitt ändert sich je nach Angestelltem. Man schreibt FROM staff s WHERE salary > (SELECT AVG(salary) FROM staff WHERE department = s.department). Für Bob (IT) berechnet die Unterabfrage den Durchschnitt der IT-Abteilung, für Claire den von Finance. Die Verbindung entsteht über s.department, das aus der Hauptabfrage kommt.

Wenn dieselbe Tabelle in beiden Abfragen vorkommt, ist der Alias unverzichtbar: s bezeichnet die aktuelle Zeile der Hauptabfrage. Ohne ihn würde department = department die Spalte mit sich selbst vergleichen, und die Unterabfrage würde den Durchschnitt der ganzen Firma berechnen.

EXISTS (Unterabfrage) ist wahr, wenn die Unterabfrage mindestens eine Zeile liefert, sonst falsch. Es beantwortet die Frage „gibt es mindestens ein…?“. WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) behält die Kunden mit mindestens einer Bestellung. Man schreibt SELECT 1, denn es zählt nur, dass eine Zeile existiert, nicht ihr Inhalt.

NOT EXISTS ist wahr, wenn die Unterabfrage nichts liefert: die Kunden ohne Bestellung. Das ist die sicherste Methode, um zu finden, was keinen Partner hat, denn EXISTS antwortet nur mit wahr oder falsch und tappt nicht in die NULL-Falle, anders als NOT IN.

Die Verbindung zwischen den beiden Abfragen entsteht im WHERE der Unterabfrage. Vergisst man sie, ist EXISTS für alle Zeilen wahr, sobald die abgefragte Tabelle nicht leer ist.

Syntax

SELECT …
FROM a
WHERE EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id);

Kommentiertes Beispiel

SELECT c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Für jeden Kunden sucht die Unterabfrage mindestens eine Bestellung auf seinen Namen. Fabio, der nichts bestellt hat, fällt weg.

Ergebnis des Beispiels

Das Wichtigste

• Korreliert = die Unterabfrage hängt von der aktuellen Zeile ab.
• EXISTS: mindestens eine Zeile; NOT EXISTS: keine.
• SELECT 1 genügt in einem EXISTS.

Häufige Fallen

• Die Verknüpfungsbedingung vergessen macht EXISTS für alle Zeilen wahr.
• Gib den Tabellen der Abfrage und der Unterabfrage verschiedene Aliase, wenn es dieselbe Tabelle ist.

Die 5 Übungen des Levels

1. Geführt · Zeige den Namen der Regisseure, die mindestens einen Film in der Tabelle movies haben. (Tabellen: directors, movies)
2. Training · Zeige den Namen der Hotelgäste, die nie gebucht haben. (Tabellen: guests, bookings)
3. Training · Zeige den Namen, die Abteilung und das Gehalt der Mitarbeitenden, die mehr als der Durchschnitt ihrer eigenen Abteilung verdienen. (Tabelle: staff)
4. Training · Zeige den Namen der Kunden mit mindestens einer stornierten Bestellung (status = 'cancelled'). (Tabellen: customers, orders)
5. Herausforderung · Zeige für jedes Genre das Genre, den Titel und die Bewertung des bestbewerteten Films. (Tabelle: movies)

In SpeedQL wird jede Abfrage sofort geprüft: Ihr Ergebnis wird mit dem erwarteten verglichen, dann läuft die Abfrage noch einmal auf einer versteckten Kontrolldatenbank. Zu jeder Übung gibt es schriftliche Hinweise, die du nur bei Bedarf aufrufst.