Unterabfragen

Unterabfragen sind geklammerte Abfrageausdrücke, die Teil einer größeren, äußeren Abfrage sind. Unterabfragen basieren in der Regel auf SELECT ... FROM, in DuckDB können aber auch andere Abfragekonstrukte wie PIVOT als Unterabfrage auftreten.

Skalare Unterabfrage

Skalare Unterabfragen sind Unterabfragen, die einen einzelnen Wert zurückgeben. Sie können überall dort verwendet werden, wo ein Ausdruck verwendet werden kann. Gibt eine skalare Unterabfrage mehr als einen einzelnen Wert zurück, wird ein Fehler ausgelöst (es sei denn, scalar_subquery_error_on_multiple_rows ist auf false gesetzt; dann wird eine Zeile zufällig ausgewählt).

Betrachten Sie die folgende Tabelle:

Noten

grade	course
7	Math
9	Math
8	CS

CREATE TABLE grades (grade INTEGER, course VARCHAR);
INSERT INTO grades VALUES (7, 'Math'), (9, 'Math'), (8, 'CS');

Wir können die folgende Abfrage ausführen, um die minimale Note zu erhalten:

SELECT min(grade) FROM grades;

min(grade)
7

Indem wir eine skalare Unterabfrage in der Klausel WHERE verwenden, können wir herausfinden, für welchen Kurs diese Note erreicht wurde:

SELECT course FROM grades WHERE grade = (SELECT min(grade) FROM grades);

course
Math

ARRAY-Unterabfragen

Unterabfragen, die mehrere Werte zurückgeben, können mit ARRAY umschlossen werden, um alle Ergebnisse in einer Liste zu sammeln.

SELECT ARRAY(SELECT grade FROM grades) AS all_grades;

all_grades
[7, 9, 8]

Unterabfragevergleiche: ALL, ANY und SOME

Im Abschnitt zu skalaren Unterabfragen wurde ein skalarer Ausdruck direkt mit einer Unterabfrage über den Gleichheits-Vergleichsoperator (=) verglichen. Solche direkten Vergleiche sind nur bei skalaren Unterabfragen sinnvoll.

Skalare Ausdrücke können dennoch mit einspaltigen Unterabfragen verglichen werden, die mehrere Zeilen zurückgeben, indem ein Quantor angegeben wird. Verfügbare Quantoren sind ALL, ANY und SOME. Die Quantoren ANY und SOME sind gleichwertig.

ALL

Der Quantor ALL legt fest, dass der Vergleich insgesamt zu true auswertet, wenn die einzelnen Vergleichsergebnisse des Ausdrucks auf der linken Seite des Vergleichsoperators mit jedem der Werte aus der Unterabfrage auf der rechten Seite des Vergleichsoperators alle zu true auswerten:

SELECT 6 <= ALL (SELECT grade FROM grades) AS adequate;

liefert:

adequate
true

weil 6 kleiner oder gleich jedem der Unterabfrageergebnisse 7, 8 und 9 ist.

Die folgende Abfrage

SELECT 8 >= ALL (SELECT grade FROM grades) AS excellent;

liefert dagegen

excellent
false

weil 8 nicht größer oder gleich dem Unterabfrageergebnis 9 ist. Und weil nicht alle Vergleiche zu true auswerten, wertet >= ALL insgesamt zu false aus.

ANY

Der Quantor ANY legt fest, dass der Vergleich insgesamt zu true auswertet, wenn mindestens eines der einzelnen Vergleichsergebnisse zu true auswertet. Zum Beispiel:

SELECT 5 >= ANY (SELECT grade FROM grades) AS fail;

liefert

fail
false

weil kein Ergebnis der Unterabfrage kleiner oder gleich 5 ist.

Der Quantor SOME kann statt ANY verwendet werden: ANY und SOME sind austauschbar.

EXISTS

Der Operator EXISTS prüft, ob in der Unterabfrage irgendeine Zeile existiert. Er liefert wahr, wenn die Unterabfrage einen oder mehrere Datensätze zurückgibt, und sonst falsch. Der Operator EXISTS ist in der Regel am nützlichsten als korrelierte Unterabfrage, um Semijoin-Operationen auszudrücken. Er kann jedoch auch als unkorrelierte Unterabfrage verwendet werden.

Wir können ihn beispielsweise verwenden, um festzustellen, ob für einen bestimmten Kurs Noten vorhanden sind:

SELECT EXISTS (FROM grades WHERE course = 'Math') AS math_grades_present;

math_grades_present
true

SELECT EXISTS (FROM grades WHERE course = 'History') AS history_grades_present;

history_grades_present
false

Die Unterabfragen in den Beispielen oben nutzen, dass Sie in DuckDB dank der FROM-zuerst-Syntax SELECT * weglassen können. Die Klausel SELECT ist in Unterabfragen anderer SQL-Systeme erforderlich, kann in EXISTS- und NOT EXISTS-Unterabfragen aber keinen Zweck erfüllen.

NOT EXISTS

Der Operator NOT EXISTS prüft, ob in der Unterabfrage keine Zeile vorhanden ist. Er liefert wahr, wenn die Unterabfrage ein leeres Ergebnis zurückgibt, und sonst falsch. Der Operator NOT EXISTS ist in der Regel am nützlichsten als korrelierte Unterabfrage, um Antijoin-Operationen auszudrücken. Um beispielsweise Person-Knoten ohne Interesse zu finden:

CREATE TABLE Person (id BIGINT, name VARCHAR);
CREATE TABLE interest (PersonId BIGINT, topic VARCHAR);

INSERT INTO Person VALUES (1, 'Jane'), (2, 'Joe');
INSERT INTO interest VALUES (2, 'Music');

SELECT *
FROM Person
WHERE NOT EXISTS (FROM interest WHERE interest.PersonId = Person.id);

id	name
1	Jane

DuckDB erkennt automatisch, wenn eine NOT EXISTS-Abfrage eine Antijoin-Operation ausdrückt. Es ist nicht nötig, solche Abfragen manuell in LEFT OUTER JOIN ... WHERE ... IS NULL umzuschreiben.

IN-Operator

Der Operator IN prüft, ob der linke Ausdruck im durch die Unterabfrage oder die Menge von Ausdrücken auf der rechten Seite (RHS) definierten Ergebnis enthalten ist. Der Operator IN liefert wahr, wenn der Ausdruck in der RHS vorkommt, falsch, wenn der Ausdruck nicht in der RHS vorkommt und die RHS keine NULL-Werte hat, oder NULL, wenn der Ausdruck nicht in der RHS vorkommt und die RHS NULL-Werte hat.

Wir können den Operator IN ähnlich verwenden wie den Operator EXISTS:

SELECT 'Math' IN (SELECT course FROM grades) AS math_grades_present;

math_grades_present
true

Korrelierte Unterabfragen

Alle bisher vorgestellten Unterabfragen waren unkorrelierte Unterabfragen, die in sich abgeschlossen sind und ohne die übergeordnete Abfrage ausgeführt werden können. Es gibt eine zweite Art von Unterabfragen, die korrelierten Unterabfragen. Korrelierte Unterabfragen verwenden Werte aus der übergeordneten Abfrage.

Konzeptuell werden die Unterabfragen einmal für jede einzelne Zeile der übergeordneten Abfrage ausgeführt. Eine einfache Vorstellung davon ist, dass die korrelierte Unterabfrage eine Funktion ist, die auf jede Zeile im Quelldatensatz angewendet wird.

Angenommen, wir möchten die minimale Note für jeden Kurs finden. Das könnten wir wie folgt tun:

SELECT *
FROM grades grades_parent
WHERE grade =
    (SELECT min(grade)
     FROM grades
     WHERE grades.course = grades_parent.course);

grade	course
7	Math
8	CS

Die Unterabfrage verwendet eine Spalte aus der übergeordneten Abfrage (grades_parent.course). Konzeptuell können wir die Unterabfrage als Funktion sehen, bei der die korrelierte Spalte ein Parameter dieser Funktion ist:

SELECT min(grade)
FROM grades
WHERE course = ?;

Wenn wir diese Funktion für jede der Zeilen ausführen, sehen wir, dass sie für Math 7 und für CS 8 zurückgibt. Das vergleichen wir dann mit der Note der jeweiligen Zeile. Dadurch wird die Zeile (Math, 9) herausgefiltert, weil 9 <> 7.

Jede Zeile der Unterabfrage als Struct zurückgeben

Die Verwendung des Namens einer Unterabfrage in der Klausel SELECT (ohne Bezug auf eine bestimmte Spalte) wandelt jede Zeile der Unterabfrage in einen Struct um, dessen Felder den Spalten der Unterabfrage entsprechen. Zum Beispiel:

SELECT t
FROM (SELECT unnest(generate_series(41, 43)) AS x, 'hello' AS y) t;

t
{‘x’: 41, ‘y’: hello}
{‘x’: 42, ‘y’: hello}
{‘x’: 43, ‘y’: hello}