Mengenoperationen

Mengenoperationen erlauben das Kombinieren von Abfragen nach Mengenoperationssemantik. Mengenoperationen bezeichnen die Klauseln UNION [ALL], INTERSECT [ALL] und EXCEPT [ALL]. Die einfachen Varianten verwenden Mengensemantik, d. h. sie entfernen Duplikate, die Varianten mit ALL verwenden Multimengen-Semantik (Bag-Semantik).

Klassische Mengenoperationen vereinigen Abfragen nach Spaltenposition und verlangen, dass die zu kombinierenden Abfragen dieselbe Anzahl Eingabespalten haben. Haben die Spalten nicht denselben Typ, können Casts ergänzt werden. Das Ergebnis verwendet die Spaltennamen der ersten Abfrage.

DuckDB unterstützt außerdem UNION [ALL] BY NAME, das Spalten nach Namen statt nach Position verbindet. UNION BY NAME verlangt nicht, dass die Eingaben dieselbe Spaltenzahl haben. Bei fehlenden Spalten werden NULL-Werte ergänzt.

UNION

Die UNION-Klausel kann Zeilen aus mehreren Abfragen kombinieren. Die Abfragen müssen dieselbe Spaltenzahl zurückgeben. Wo nötig, wird ein implizites Casting auf einen der zurückgegebenen Typen durchgeführt, um Spalten unterschiedlicher Typen zu kombinieren. Ist das nicht möglich, löst die UNION-Klausel einen Fehler aus.

Einfaches UNION (Mengensemantik)

Die einfache UNION-Klausel folgt der Mengensemantik und führt daher eine Duplikateliminierung durch, d. h. nur eindeutige Zeilen werden ins Ergebnis aufgenommen.

SELECT * FROM range(2) t1(x)
UNION
SELECT * FROM range(3) t2(x);

x
2
1
0

UNION ALL (Bag-Semantik)

UNION ALL gibt alle Zeilen beider Abfragen nach Bag-Semantik zurück, d. h. ohne Duplikateliminierung.

SELECT * FROM range(2) t1(x)
UNION ALL
SELECT * FROM range(3) t2(x);

x
0
1
0
1
2

UNION [ALL] BY NAME

Die Klausel UNION [ALL] BY NAME kann Zeilen aus unterschiedlichen Tabellen nach Namen statt nach Position kombinieren. UNION BY NAME verlangt nicht, dass beide Abfragen dieselbe Spaltenzahl haben. Spalten, die nur in einer der Abfragen vorkommen, werden für die andere Abfrage mit NULL-Werten gefüllt.

Nehmen Sie beispielsweise die folgenden Tabellen:

CREATE TABLE capitals (city VARCHAR, country VARCHAR);
INSERT INTO capitals VALUES
    ('Amsterdam', 'NL'),
    ('Berlin', 'Germany');
CREATE TABLE weather (city VARCHAR, degrees INTEGER, date DATE);
INSERT INTO weather VALUES
    ('Amsterdam', 10, '2022-10-14'),
    ('Seattle', 8, '2022-10-12');

SELECT * FROM capitals
UNION BY NAME
SELECT * FROM weather;

city	country	degrees	date
Seattle	NULL	8	2022-10-12
Amsterdam	NL	NULL	NULL
Berlin	Germany	NULL	NULL
Amsterdam	NULL	10	2022-10-14

UNION BY NAME folgt der Mengensemantik (führt daher eine Duplikateliminierung durch), UNION ALL BY NAME folgt der Bag-Semantik.

INTERSECT

Die INTERSECT-Klausel wählt alle Zeilen aus, die im Ergebnis beider Abfragen vorkommen.

Einfaches INTERSECT (Mengensemantik)

Einfaches INTERSECT führt eine Duplikateliminierung durch, sodass nur eindeutige Zeilen zurückgegeben werden.

SELECT * FROM range(2) t1(x)
INTERSECT
SELECT * FROM range(6) t2(x);

x
0
1

INTERSECT ALL (Bag-Semantik)

INTERSECT ALL folgt der Bag-Semantik, sodass Duplikate zurückgegeben werden.

SELECT unnest([5, 5, 6, 6, 6, 6, 7, 8]) AS x
INTERSECT ALL
SELECT unnest([5, 6, 6, 7, 7, 9]);

x
5
6
6
7

EXCEPT

Die EXCEPT-Klausel wählt alle Zeilen aus, die nur in der linken Abfrage vorkommen.

Einfaches EXCEPT (Mengensemantik)

Einfaches EXCEPT folgt der Mengensemantik und führt daher eine Duplikateliminierung durch, sodass nur eindeutige Zeilen zurückgegeben werden.

SELECT * FROM range(5) t1(x)
EXCEPT
SELECT * FROM range(2) t2(x);

x
2
3
4

EXCEPT ALL (Bag-Semantik)

EXCEPT ALL verwendet Bag-Semantik:

SELECT unnest([5, 5, 6, 6, 6, 6, 7, 8]) AS x
EXCEPT ALL
SELECT unnest([5, 6, 6, 7, 7, 9]);

x
5
8
6
6

Syntax