SQL: Unterabfragen, CTEs und Mengenoperationen

Kurz: Eine Unterabfrage (subquery) ist ein SELECT in einem anderen:

Teil des Kurses SQL

Kapitel 9 von 15 im Kurs SQL. Mit Fortschritt, Quiz und Zertifikat auf der Lernseite.

Unterabfragen

Eine Unterabfrage (subquery) ist ein SELECT in einem anderen:

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
-- Kunden mit überdurchschnittlichen Punkten
SELECT name, punkte FROM kunden
WHERE punkte > (SELECT avg(punkte) FROM kunden);
 
-- Kunden, die mindestens eine Bestellung haben
SELECT name FROM kunden WHERE id IN (SELECT kunde_id FROM bestellungen);
 
-- Kunden ohne Bestellung
SELECT name FROM kunden k
WHERE NOT EXISTS (SELECT 1 FROM bestellungen b WHERE b.kunde_id = k.id);

Ausgabe:

name  punkte
----  ------
Zoe   200
Eva   150
name
----
Mia
Tom
Zoe
name
----
Ben
Eva

Achtung

NOT IN mit einer Liste, die ein NULL enthält, liefert nie Treffer. Nutze NOT EXISTS (hier hat Bestellung 7 kunde_id = NULL).

Unterabfrage als Spalte oder Tabelle

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
SELECT name,
       (SELECT count(*) FROM bestellungen b WHERE b.kunde_id = k.id) AS anzahl
FROM kunden k
ORDER BY anzahl DESC, name;
 
SELECT ort, durchschnitt
FROM (SELECT ort, avg(punkte) AS durchschnitt FROM kunden GROUP BY ort)
WHERE durchschnitt > 100
ORDER BY ort;

Ausgabe:

name  anzahl
----  ------
Zoe   3
Mia   2
Tom   1
Ben   0
Eva   0
ort     durchschnitt
------  ------------
Berlin  160.0
Köln    150.0

CTE: WITH

Eine Common Table Expression gibt einer Zwischenabfrage einen Namen und macht lange Abfragen lesbar:

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
WITH umsatz AS (
    SELECT kunde_id, sum(preis * menge) AS summe
    FROM bestellungen
    WHERE kunde_id IS NOT NULL
    GROUP BY kunde_id
),
rang AS (
    SELECT kunde_id, summe FROM umsatz WHERE summe > 100
)
SELECT k.name, r.summe
FROM rang r
JOIN kunden k ON k.id = r.kunde_id
ORDER BY r.summe DESC;

Ausgabe:

name  summe
----  ------
Zoe   1073.5
Mia   1050.0
Tom   189.9

Rekursive CTEs

Damit gehst du Hierarchien oder Zahlenfolgen durch:

WITH RECURSIVE zahlen(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM zahlen WHERE n < 5
)
SELECT n, n * n AS quadrat FROM zahlen;

Ausgabe:

n  quadrat
-  -------
1  1
2  4
3  9
4  16
5  25

CREATE TABLE mitarbeiter (id INTEGER PRIMARY KEY, name TEXT, chef_id INTEGER);
INSERT INTO mitarbeiter VALUES (1,'Anna',NULL),(2,'Ben',1),(3,'Cem',1),(4,'Dina',2),(5,'Eli',4);
 
WITH RECURSIVE baum(id, name, ebene, pfad) AS (
    SELECT id, name, 0, name FROM mitarbeiter WHERE chef_id IS NULL
    UNION ALL
    SELECT m.id, m.name, b.ebene + 1, b.pfad || ' > ' || m.name
    FROM mitarbeiter m JOIN baum b ON m.chef_id = b.id
)
SELECT ebene, pfad FROM baum ORDER BY pfad;

Ausgabe:

ebene  pfad
-----  -----------------------
0      Anna
1      Anna > Ben
2      Anna > Ben > Dina
3      Anna > Ben > Dina > Eli
1      Anna > Cem

Mengenoperationen

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
SELECT ort FROM kunden WHERE punkte >= 120
UNION
SELECT ort FROM kunden WHERE ort = 'Hamburg'
ORDER BY ort;
 
SELECT produkt FROM bestellungen WHERE preis > 100
INTERSECT
SELECT produkt FROM bestellungen WHERE menge = 1
ORDER BY produkt;
 
SELECT name FROM kunden
EXCEPT
SELECT k.name FROM kunden k JOIN bestellungen b ON b.kunde_id = k.id
ORDER BY name;

Ausgabe:

ort
-------
Berlin
Hamburg
Köln
produkt
-------
Laptop
Monitor
name
----
Ben
Eva

Operator	Ergebnis
`UNION`	Vereinigung ohne Duplikate
`UNION ALL`	Vereinigung mit Duplikaten (schneller)
`INTERSECT`	nur was in beiden vorkommt
`EXCEPT`	in der ersten, aber nicht in der zweiten (Oracle: `MINUS`)

Beide Abfragen brauchen gleich viele Spalten mit passenden Typen.

Merke

Unterabfragen stehen in WHERE, FROM oder SELECT
EXISTS/NOT EXISTS ist robuster als IN/NOT IN (NULL!)
WITH (CTE) macht komplexe Abfragen lesbar, WITH RECURSIVE für Hierarchien
UNION, INTERSECT, EXCEPT kombinieren Ergebnismengen

Übungsaufgabe

Finde mit einer CTE die Produkte, deren Gesamtumsatz über dem Durchschnitt aller Produktumsätze liegt.

Quiz zur Selbstkontrolle

Warum ist NOT IN mit NULL in der Liste gefährlich?

Es liefert nie Treffer (richtig)

Es liefert alle Zeilen

Es löscht die Daten

Es ist langsamer, aber korrekt

Wozu dient WITH?

Zwischenergebnisse zu benennen (CTE) (richtig)

Zum Verbinden von Datenbanken

Für Transaktionen

Für Indizes

Was unterscheidet UNION von UNION ALL?

UNION entfernt Duplikate (richtig)

UNION ALL entfernt Duplikate

Nichts

UNION sortiert absteigend

Weiter im Kurs

Zurück: JOINs

Weiter: Fensterfunktionen

Alle Kapitel: SQL im Überblick

Emzett Wiki

Explorer

SQL: Unterabfragen, CTEs und Mengenoperationen

Unterabfragen

Unterabfrage als Spalte oder Tabelle

CTE: WITH

Rekursive CTEs

Mengenoperationen

Merke

Übungsaufgabe

Quiz zur Selbstkontrolle

Weiter im Kurs

Graphansicht

Inhaltsverzeichnis

Backlinks