Geschrieben von

Korrelierte Unterabfragen einfach erklärt

Data Analytics

Neben Scalar Subqueries (skalare Unterabfragen) und Multiple Row Subqueries (mehrzeilige Unterabfragen), gehören die Correlated Subqueries – also “korrelierte Unterabfragen” – zu der dritten Sorte der SQL-Unterabfragen. In diesem Beitrag möchte ich dir korrelierte Unterabfragen näher bringen.

Kurze Einführung in Unterabfragen

Bei einer Unterabfrage handelt es sich um eine Abfrage in einer Abfrage – daher der Begriff “Unterabfrage”. Wir haben also zwei Abfragen in Einer. Diese zwei Abfragen haben auch Namen:

  • Outer Query: Dies ist in der Regel die Hauptabfrage.
  • Inner Query: Dabei handelt es sich um die eigentliche Unterabfrage.

Schauen wir uns dazu das folgende Beispiel an:

select
  first_name,
  last_name
from
  Customers
where
  age > (select avg(age)
         from Customers)

In der WHERE-Klausel kann man sehen, dass in runden Klammern eine weitere Abfrage ist. Diese würde die Inner Query (innere Abfrage) darstellen, während die Haupt-Abfrage die Outer Query (äußere Abfrage) wäre:

Auf Basis der Beziehung zwischen Inner und Outer Query lassen sich Unterabfragen dann wie folgt einteilen:

  • Nicht-korrelierte Unterabfrage
  • Korrelierte Unterabfrage

Was bedeutet das nun konkret?

Nicht-korrelierte Unterabfrage

“Nicht-korreliert” heißt im Zusammenhang mit Unterabfragen, dass die Inner Query unabhängig von der Outer Query ist. Sprich: Die Inner Query kann auch selbstständig ausgeführt werden. Bei einer nicht-korrelierten Unterabfrage geht SQL wie folgt vor:

  1. Zuerst wird die Inner Query ausgeführt.
  2. Die Inner Query liefert dann einen Wert (oder mehrere) zurück.
  3. Der Wert wird dann in die Outer Query gesetzt.
  4. Die Outer Query wird ausgeführt.
  5. Das Ergebnis der gesamten Abfrage wird zurückgegeben.

Wenn wir also bei unserem Beispiel von oben bleiben:

select
  first_name,
  last_name
from
  Customers
where
  age > (select avg(age)
         from Customers)

Dann würde SQL zunächst die Inner Query ausführen:

Diese liefert dann einen Wert zurück:

Dieser Wert wird nun in die äußere Abfrage gesetzt:

Jetzt führt SQL die äußere Abfrage durch:

Und dann wird das Ergebnis zurückgeliefert:

Sprich, wenn die Inner Query komplett alleine ausgeführt werden kann, dann korreliert die Inner Query nicht mit der Outer Query. Dadurch haben wir es mit einer nicht-korrelierten Unterabfrage zu tun.

Korrelierte Unterabfrage

Korrelierte Unterabfragen arbeiten komplexer als einfache (nicht-korrelierte) Unterabfragen. Schauen wir uns dazu das folgende Beispiel an:

select
  first_name,
  country,
  age
from
  Customers c
where
  age >= (select avg(age)
         from Customers
         where country = c.country)

Auch hier können wir wieder eine Inner Query und Outer Query erkennen:

Bei näherer Betrachtung fällt dir vielleicht auf, dass die Inner Query nicht unabhängig von der Outer Query ist. Der Grund ist folgender:

Dieser Ausdruck – also “c.country” – ist in der Inner Query zunächst unbekannt. Wenn wir wie bei der nicht-korrelierten Unterabfrage versuchen, die Inner Query selbstständig auszuführen, dann würde das nun nicht mehr klappen wie bei der nicht-korrelierten Unterabfrage. Wir würden einen Fehler zurückbekommen, dass die Spalte “c.country” unbekannt ist, da sie in der inneren Abfrage nicht existiert. Dieser Ausdruck in der Inner Query bezieht sich auf eine Spalte der äußeren Abfrage:

Aufgrund dieser Beziehung zueinander, spricht man hier von einer korrelierten Unterabfrage. Bei einer korrelierten Unterabfrage geht SQL wie folgt vor:

  1. Die Outer Query wird für jede Zeile in der Tabelle ausgeführt.
  2. Jedoch wird für jede dieser Zeilen ein Wert in der Inner Query eingesetzt.
  3. Mit diesem Wert wird die Inner Query einmal ausgeführt.
  4. Das Ergebnis der Inner Query wird dann zur Outer Query zur Ausführung weitergegeben.

Mit der beispielhaften Abfrage von oben lässt sich das wie folgt noch etwas besser erklären. Dazu starten wir nur mit der Inner Query:

select
  avg(age)
from
  Customers
where
  country = c.country

Hier ist zunächst der Ausdruck “c.country” ein Wert, der von der Outer Query geliefert wird. Um zu verstehen, welche Werte hier die Outer Query liefern könnte, reicht ein Blick in die Tabelle “c”, welches ein Alias der Tabelle “Customers” ist. Gehen wir davon aus, dass die Spalte “c.country” folgende mögliche Werte besitzt: USA, UK, UAE. Wenn wir nun das “c.country” in der Inner Query wegdenken und stattdessen nacheinander die möglichen Werte USA, UK und UAE setzen, dann hätten als erste Query Folgende:

select
  avg(age)
from
  Customers
where
  country = 'USA'

Als Ergebnis würden wir nun beispielhaft folgendes bekommen: 26.5. Also das Durchschnittsalter aller Kunden aus den USA. Das Ergebnis können wir uns merken:

LandDurchschn. Alter
USA26.5

Das selbe folgt für UK:

select
  avg(age)
from
  Customers
where
  country = 'UK'

Ergebnis wäre z.B. “23.5”, was wir uns ebenfalls merken:

LandDurchschn. Alter
USA26.5
UK23.5

Und dann noch für UAE:

select
  avg(age)
from
  Customers
where
  country = 'UAE'

Auch hier merken wir uns das Ergebnis:

LandDurchschn. Alter
USA26.5
UK23.5
UAE28

Wenn wir mit dieser Tabelle nun zurück zu unserer Inner Query kommen…:

select
  avg(age)
from
  Customers
where
  country = c.country

…dann sagt uns diese, dass wenn wir “c.country” mit USA ersetzen, bekommen wir auf Basis der oberen Tabelle 26.5 als Ergebnis, etc. Damit kommen wir nun wieder zu unserer Quter Query:

select
  first_name,
  country,
  age
from
  Customers c

Gehen wir davon aus, diese Abfrage liefert uns folgendes Ergebnis:

first_namecountryage
JohnUSA31
RobertUSA22
DavidUK22
JohnUK25
BettyUAE28

Was geschieht nun? Als erstes wird für jede Zeile dieser äußeren Abfrage “country” ermittelt (da wir in der Inner Query “where country = c.country” nutzen). Für die erste Zeile wäre dies “USA”. Im zweiten Schritt wird dieser Wert nun statt “c.country” in der inneren Abfrage gesetzt, also:

select
  first_name,
  country,
  age
from
  Customers c
where
  age >= (select avg(age)
         from Customers
         where country = 'USA')

Im dritten Schritt wird die Inner Query mit diesem Wert einmal ausgeführt. Was ist das Ergebnis der Inner Query? Das Ergebnis haben wir vorher in unserer “gedanklichen” Tabelle zwischengespeichert:

LandDurchschn. Alter
USA26.5
UK23.5
UAE28

Damit hätten wir also folgende Query (die komplette Inner Query kann nun mit diesem Wert ersetzt werden):

select
  first_name,
  country,
  age
from
  Customers c
where
  age >= 26.5

Als vierten Schritt kann nun diese Abfrage auf die äußere Tabelle angewendet werden. Für die zweite Zeile – also “Robert, USA, 22” – der äußeren Abfrage würde das Spiel von vorne gehen. Zunächst hätten wir also:

select
  first_name,
  country,
  age
from
  Customers c
where
  age >= (select avg(age)
         from Customers
         where country = 'USA')

Und dann:

select
  first_name,
  country,
  age
from
  Customers c
where
  age >= 26.5

Da in unserem Fall nur die erste Zeile der äußeren Abfrage dieser Kondition entspricht – da John älter ist als der Duchschnitt und Robert nicht – wird entsprechend nur John zurückgeliefert. Diese Query wird nun für jede einzelne Zeile durchgeführt und am Ende bekommen wir folgende Ergebnistabelle:

first_namecountryage
JohnUSA31
JohnUK25
BettyUAE28

Mit der korrelierten Unterabfragen haben wir im Grunde alle Namen ermittelt, deren Alter größer oder gleich des Durchschnittsalters in deren Land ist.