SQL: Unterabfragen und Mengenoperationen

SQL: Unterabfragen und Mengenoperationen

https://bildung.social/@oerinformatik/

https://oer-informatik.de/sql-unterabfragen

SQL, Unterabfragen, Subquery, korrelierte Unterabfrage, EXISTS, NOT IN, UNION, INTERSECT, EXCEPT, MariaDB

tl/dr; (ca. 30 min Lesezeit): Eine Unterabfrage ist eine SELECT-Abfrage in Klammern, deren Ergebnis eine andere Abfrage weiterverwendet – als einzelner Wert, als Liste, als Tabelle oder als Wahrheitswert mit EXISTS. Der Artikel zeigt Unterabfragen in WHERE, SELECT und FROM, korrelierte Unterabfragen, Werte aus Merkmalstabellen, die NULL-Falle bei NOT IN und typische Fehlerbilder, Prozentanteile, ANY und ALL, Archivieren mit INSERT … SELECT und DELETE sowie die Mengenoperationen UNION, UNION ALL, INTERSECT und EXCEPT in MariaDB [AI-Assisted]. (Zuletzt geändert am 28.10.2026)

Dieser Artikel ist Teil einer Artikelreihe über SQL-Abfragen: Selektion und Projektion / Single-Row-Funktionen / Aggregierende Funktionen / Abfragen über mehrere Tabellen (JOIN) / Unterabfragen und Mengenoperationen / Übungsaufgaben zu Unterabfragen

Schon fit in diesem Thema? Nutze die Kann-Liste und die Leitfragen unter dem Artikel als Checkliste, ob Du die relevanten Kompetenzen bereits hast. Hier klicken, um Kann-Liste ein- oder auszublenden

„Wer arbeitet mehr Stunden als der Durchschnitt?“ – So einfach die Frage klingt, mit den bisherigen Mitteln lässt sie sich nicht in einer Abfrage beantworten: Für den Durchschnitt brauchst Du AVG() über alle Zeilen, für den Vergleich aber jede einzelne Zeile. Die Lösung ist eine Abfrage in der Abfrage, die Unterabfrage (subquery). Dazu kommen die Mengenoperationen (set operations), mit denen Du die Ergebnisse mehrerer Abfragen untereinanderstellst, ihre Schnittmenge oder ihre Differenz bildest. In SQL-Aufgaben tauchen beide regelmäßig auf: beim Vergleich mit Durchschnitts- oder Höchstwerten, bei Anteilen in Prozent, beim Suchen und Löschen von Datensätzen ohne Partner und beim Archivieren alter Daten.

Unterabfragen können an vielen Stellen einer Anweisung stehen. Was dort erlaubt ist, hängt davon ab, was die Unterabfrage liefert: einen einzelnen Wert, eine Liste oder eine ganze Tabelle.

Das Beispiel: Abteilungen, Mitarbeitende, Schulungen und Praktika

Die Beispiele verwenden die Personaldatenbank des Fahrradhändlers aus dem Artikel Abfragen über mehrere Tabellen mit JOIN, ergänzt um die Wochenstunden der Mitarbeitenden, die Platzzahl der Schulungen, eine vierte Schulung und eine Tabelle für Praktika:

• abteilung (abteilung_id, bezeichnung)

• mitarbeiter (mitarbeiter_id, vorname, nachname, #abteilung_id, #vorgesetzt_id, wochenstunden)

• schulung (schulung_id, titel, plaetze)

• teilnahme (#mitarbeiter_id, #schulung_id)

• praktikum (praktikum_id, vorname, nachname, #abteilung_id)

Alex Kaiser ist noch keiner Abteilung zugeordnet, in der IT arbeitet bisher nur ein Praktikant, und für die Schulung „Kundengespräche führen“ hat sich noch niemand angemeldet.

Abfragen in Abfragen

Eine Unterabfrage ist eine vollständige SELECT-Anweisung in runden Klammern, die an der Stelle eines Werts, einer Liste oder einer Tabelle steht. Die umgebende Abfrage heißt äußere Abfrage oder Hauptabfrage. Je nachdem, was die Unterabfrage liefert, unterscheidet man:

Gedanklich wird eine einfache Unterabfrage zuerst ausgeführt, ihr Ergebnis setzt die Datenbank dann in die äußere Abfrage ein. Unterabfragen lassen sich auch ineinander schachteln. Eine Ausnahme bilden korrelierte Unterabfragen, die sich auf die gerade bearbeitete Zeile der äußeren Abfrage beziehen. Zu ihnen kommen wir weiter unten.

Vergleich mit einem berechneten Wert: die skalare Unterabfrage

Zurück zur Eingangsfrage. Der Durchschnitt aller Wochenstunden beträgt 34,75 Stunden. Statt diesen Wert erst auszurechnen und dann von Hand in die Abfrage zu schreiben, lässt Du ihn von einer Unterabfrage bestimmen:

SELECT vorname, nachname, wochenstunden
FROM mitarbeiter
WHERE wochenstunden > (SELECT AVG(wochenstunden)
                       FROM mitarbeiter);

Die Grafik zeigt die beiden Schritte: Erst berechnet die Unterabfrage den Durchschnitt, dann vergleicht die äußere Abfrage jede Zeile mit diesem einen Wert.

Der Vorteil: Die Abfrage stimmt auch noch, wenn sich die Daten ändern. Nach demselben Muster findest Du den Datensatz mit dem kleinsten oder größten Wert (WHERE wochenstunden = (SELECT MIN(wochenstunden) FROM mitarbeiter) liefert Natalia Wróbel) oder vergleichst mit einem anderen Datensatz, dessen Wert Du nicht kennst, wohl aber seinen Schlüssel:

-- Wer arbeitet mehr Stunden als Selin Arslan (mitarbeiter_id 3)?
SELECT vorname, nachname, wochenstunden
FROM mitarbeiter
WHERE wochenstunden > (SELECT wochenstunden
                       FROM mitarbeiter
                       WHERE mitarbeiter_id = 3);

Das liefert Sophie Bertram, Bastian Ludwig und Alex Kaiser. Für die Frage nach dem jüngsten oder ältesten Datensatz gibt es neben der Unterabfrage mit MIN/MAX auch die Variante mit ORDER BY … LIMIT 1. Sie liefert bei Gleichstand aber nur einen von mehreren gleichwertigen Datensätzen, die Unterabfrage dagegen alle.

Werte aus einer Merkmalstabelle

Viele Datenbanken speichern technische Merkmale nicht als eigene Spalten, sondern als Merkmal-Wert-Paare: Eine Tabelle merkmal enthält die Merkmale (Gewicht, Akkulaufzeit, Bildschirmgröße …), eine Zuordnungstabelle produkt_merkmal (produkt_id, merkmal_id, wert) die Werte je Produkt. So lassen sich neue Merkmale ergänzen, ohne die Tabellenstruktur zu ändern. Beim Abfragen musst Du dann zuerst das richtige Merkmal auswählen – entweder per JOIN auf merkmal oder mit einer skalaren Unterabfrage, die die merkmal_id zum Namen ermittelt:

-- Alle Produkte unter 1,5 kg: leichteste zuerst, bei gleichem Gewicht das teuerste zuerst
SELECT p.bezeichnung, pm.wert AS gewicht_kg, p.preis
FROM produkt AS p
JOIN produkt_merkmal AS pm ON pm.produkt_id = p.produkt_id
WHERE pm.merkmal_id = (SELECT merkmal_id FROM merkmal WHERE bezeichnung = 'Gewicht')
  AND pm.wert < 1.5
ORDER BY pm.wert ASC, p.preis DESC;

Die Sortierung nach zwei Spalten mit unterschiedlicher Richtung (ASC, DESC) gilt je Spalte: Erst wird nach dem Gewicht sortiert, nur bei gleichem Gewicht entscheidet der Preis.

Wird der höchste oder niedrigste Wert eines Merkmals gesucht, muss auch die Unterabfrage auf dieses Merkmal eingeschränkt werden. SELECT MAX(wert) FROM produkt_merkmal ohne Bedingung liefert den größten Wert über alle Merkmale – Gramm, Stunden und Zoll durcheinander – und die äußere Abfrage findet dann meist gar nichts:

-- Produkte mit der längsten Akkulaufzeit (merkmal_id 2), bei Gleichstand alle
SELECT p.bezeichnung, pm.wert AS akku_h
FROM produkt AS p
JOIN produkt_merkmal AS pm ON pm.produkt_id = p.produkt_id
WHERE pm.merkmal_id = 2
  AND pm.wert = (SELECT MAX(wert) FROM produkt_merkmal WHERE merkmal_id = 2);

Ein weiterer häufiger Fehler ist eine vertauschte Verbundbedingung wie JOIN merkmal m ON m.merkmal_id = pm.produkt_id. Die Abfrage läuft ohne Fehlermeldung, verknüpft aber Merkmal-Nummern mit Produkt-Nummern und liefert falsche Zeilen. Prüfe bei jeder Verbundbedingung, dass Primär- und Fremdschlüssel derselben Beziehung verglichen werden.

Fehler: Die Unterabfrage liefert mehr als eine Zeile

Hinter =, <, > und den anderen Vergleichsoperatoren darf die Unterabfrage höchstens einen Wert liefern. Liefert sie mehrere Zeilen, bricht MariaDB mit der Meldung Subquery returns more than 1 row ab:

-- Fehler: In der Werkstatt arbeiten drei Personen
SELECT vorname, nachname FROM mitarbeiter
WHERE wochenstunden = (SELECT wochenstunden FROM mitarbeiter WHERE abteilung_id = 2);

Ist eine Liste gemeint, gehört IN statt = in die Abfrage. Liefert eine skalare Unterabfrage gar keine Zeile, ist ihr Wert NULL, und jeder Vergleich mit NULL ist nicht wahr – die äußere Abfrage liefert dann keine Zeile, aber auch keinen Fehler.

Listen vergleichen: IN und NOT IN

Mit IN prüfst Du, ob ein Wert in der Ergebnisliste einer Unterabfrage vorkommt. Die Unterabfrage muss dafür genau eine Spalte liefern, darf aber beliebig viele Zeilen haben:

-- Wer hat an der Schulung „Erste Hilfe“ (2) teilgenommen?
SELECT vorname, nachname
FROM mitarbeiter
WHERE mitarbeiter_id IN (SELECT mitarbeiter_id
                         FROM teilnahme
                         WHERE schulung_id = 2);

Das Ergebnis sind Sophie Bertram, Selin Arslan und Natalia Wróbel. Unterabfragen lassen sich schachteln, zum Beispiel, wenn nur der Titel der Schulungen bekannt ist:

-- Wer war bei einer Schulung, deren Titel mit „E-“ beginnt?
SELECT vorname, nachname
FROM mitarbeiter
WHERE mitarbeiter_id IN (SELECT mitarbeiter_id
                         FROM teilnahme
                         WHERE schulung_id IN (SELECT schulung_id
                                               FROM schulung
                                               WHERE titel LIKE 'E-%'));

Selin Arslan, Paul Neubauer und Natalia Wróbel erscheinen jeweils einmal, obwohl Selin und Natalia in der Tabelle teilnahme zweimal vorkommen. IN fragt nur „kommt vor oder nicht?“. Ein JOIN über dieselben Tabellen würde dagegen je Teilnahme eine Zeile liefern und bräuchte DISTINCT.

NOT IN kehrt die Bedingung um: WHERE mitarbeiter_id NOT IN (SELECT mitarbeiter_id FROM teilnahme) liefert alle Mitarbeitenden ohne jede Schulung, also Bastian Ludwig und Alex Kaiser.

Die NULL-Falle bei NOT IN

Gesucht sind alle Mitarbeitenden, die niemandem vorgesetzt sind. Naheliegend wäre:

SELECT vorname, nachname
FROM mitarbeiter
WHERE mitarbeiter_id NOT IN (SELECT vorgesetzt_id FROM mitarbeiter);

Das Ergebnis ist leer, obwohl Selin, Paul, Natalia und Alex niemanden führen. Der Grund: Die Unterabfrage liefert die Liste (NULL, NULL, 2, 2, 1, 2). x NOT IN (a, b, NULL) bedeutet x <> a AND x <> b AND x <> NULL. Der Vergleich mit NULL ist weder wahr noch falsch, sondern unbekannt – und damit ist die ganze AND-Verknüpfung nie wahr. Abhilfe schafft, NULL in der Unterabfrage auszuschließen oder gleich NOT EXISTS zu verwenden (nächster Abschnitt), das dieses Problem nicht kennt:

SELECT vorname, nachname
FROM mitarbeiter
WHERE mitarbeiter_id NOT IN (SELECT vorgesetzt_id
                             FROM mitarbeiter
                             WHERE vorgesetzt_id IS NOT NULL);

Bei IN tritt die Falle nicht auf: Ein Treffer reicht, die NULL-Werte stören dort nicht. Prüfe bei NOT IN also immer, ob die Spalte der Unterabfrage NULL enthalten darf – nullbare Fremdschlüssel sind der häufigste Fall.

Fehlerbild: Datensätze ohne Partner mit INNER JOIN suchen

Ebenfalls leer bleibt diese gut gemeinte Abfrage nach Mitarbeitenden ohne Schulung:

-- Fehler: liefert nie eine Zeile
SELECT m.vorname, m.nachname
FROM mitarbeiter AS m
INNER JOIN teilnahme AS t ON t.mitarbeiter_id = m.mitarbeiter_id
WHERE t.schulung_id IS NULL;

Ein INNER JOIN liefert nur Paare, zu denen es einen Partner gibt – Mitarbeitende ohne Teilnahme fallen schon beim Verbund heraus, und t.schulung_id ist in den übrigen Zeilen nie NULL. Richtig ist NOT IN bzw. NOT EXISTS mit Unterabfrage oder ein LEFT JOIN, bei dem die fehlenden Partner als NULL erscheinen (LEFT JOIN teilnahme AS t ON … WHERE t.mitarbeiter_id IS NULL).

ANY, SOME und ALL

Für Vergleiche mit einer ganzen Liste gibt es neben IN die Quantoren ANY (gleichbedeutend: SOME) und ALL:

• wert > ANY (Unterabfrage) ist wahr, wenn wert größer als mindestens ein Listenelement ist. = ANY entspricht IN.

• wert > ALL (Unterabfrage) ist wahr, wenn wert größer als jedes Listenelement ist. <> ALL entspricht NOT IN – mit derselben NULL-Falle.

-- Wer arbeitet mindestens so viel wie jede Person der Werkstatt?
SELECT vorname, nachname
FROM mitarbeiter
WHERE wochenstunden >= ALL (SELECT wochenstunden
                            FROM mitarbeiter
                            WHERE abteilung_id = 2);

Das liefert alle mit 40 Stunden (Sophie, Bastian, Alex). Meist geht es auch mit MIN oder MAX: >= ALL (…) entspricht >= (SELECT MAX(…) …). Einen Unterschied gibt es bei einer leeren Unterabfrage. ALL über eine leere Menge ist immer wahr (es gibt kein Element, das widerspricht). Fragst Du nach der IT-Abteilung (abteilung_id = 4, dort arbeiten keine Mitarbeitenden), liefert die ALL-Variante alle sechs Mitarbeitenden. MAX über eine leere Menge ist dagegen NULL, der Vergleich nie wahr, das Ergebnis leer. Welche Variante richtig ist, hängt von der Frage ab – Du solltest den Unterschied kennen.

Korrelierte Unterabfragen: Bezug auf die äußere Zeile

Nun soll nicht mehr mit dem Durchschnitt aller Mitarbeitenden verglichen werden, sondern mit dem Durchschnitt der eigenen Abteilung. Die Unterabfrage braucht dafür die Abteilung der Zeile, die die äußere Abfrage gerade prüft:

SELECT m.vorname, m.nachname, m.abteilung_id, m.wochenstunden
FROM mitarbeiter AS m
WHERE m.wochenstunden > (SELECT AVG(x.wochenstunden)
                         FROM mitarbeiter AS x
                         WHERE x.abteilung_id = m.abteilung_id);

Die Bedingung x.abteilung_id = m.abteilung_id verbindet die Unterabfrage mit der äußeren Zeile. Eine solche Unterabfrage heißt korreliert (correlated subquery). Man kann sie sich so vorstellen, dass sie für jede Zeile der äußeren Abfrage neu ausgeführt wird (die Datenbank optimiert das intern). Weil hier dieselbe Tabelle zweimal vorkommt, sind die Aliase m und x Pflicht. Auch bei verschiedenen Tabellen lohnen sie sich, damit klar ist, welche Spalte von außen kommt. Sophie Bertram fehlt im Ergebnis, weil sie allein im Vertrieb und damit genau im Durchschnitt liegt. Alex Kaiser fehlt, weil seine Abteilung NULL ist und x.abteilung_id = NULL nie wahr wird.

Fehler: Die Korrelation fehlt

Ein typischer Fehler ist, die äußere Tabelle in der Unterabfrage noch einmal aufzuführen. Damit entsteht eine neue, unabhängige Tabelle mit demselben Namen, und die Unterabfrage verliert ihren Bezug nach außen:

-- Fehler: zählt in jeder Zeile alle Teilnahmen (Ergebnis überall 6)
SELECT s.titel,
       (SELECT COUNT(*)
        FROM teilnahme AS t, schulung AS s
        WHERE t.schulung_id = s.schulung_id) AS belegt
FROM schulung AS s;

Das innere schulung AS s überdeckt das äußere s. Richtig ist, in der Unterabfrage nur teilnahme zu nennen und auf das äußere s zu verweisen: WHERE t.schulung_id = s.schulung_id. Ein schneller Test: Liefert eine korrelierte Unterabfrage in jeder Zeile denselben Wert, fehlt meist die Korrelation.

EXISTS und NOT EXISTS

EXISTS (Unterabfrage) ist wahr, wenn die Unterabfrage mindestens eine Zeile liefert, NOT EXISTS, wenn sie keine liefert. Welche Spalten die Unterabfrage auswählt, spielt keine Rolle; üblich ist SELECT * (oder SELECT 1). EXISTS wird fast immer korreliert eingesetzt:

-- Für welche Schulung gibt es noch keine Teilnahme?
SELECT s.titel
FROM schulung AS s
WHERE NOT EXISTS (SELECT *
                  FROM teilnahme AS t
                  WHERE t.schulung_id = s.schulung_id);

Das Ergebnis ist „Kundengespräche führen“. Mit EXISTS statt NOT EXISTS bekämst Du die drei Schulungen mit Teilnahmen.

Datensätze ohne Partner zu finden ist eine der häufigsten Aufgaben: Kund*innen ohne Bestellung, Haushalte ohne Zähler, freie Plätze ohne Buchung. Es gibt mehrere gleichwertige Schreibweisen:

Freie Ressourcen zu einem Zeitpunkt

Eine Abwandlung davon sind „freie“ Plätze, Betten, Räume oder Geräte: Gesucht ist alles, wofür es keine Belegung gibt, die den gefragten Zeitpunkt oder Zeitraum betrifft. Die Bedingung für „belegt“ steht dann komplett in der Unterabfrage. Für einen Zeitraum von beginn bis ende (bei offenem Ende NULL) und den Stichtag '2026-11-02' sähe das so aus:

SELECT r.bezeichnung
FROM ressource AS r
WHERE NOT EXISTS (SELECT *
                  FROM belegung AS b
                  WHERE b.ressource_id = r.ressource_id
                    AND b.beginn <= '2026-11-02'
                    AND (b.ende IS NULL OR b.ende >= '2026-11-02'));

Zwei Zeiträume überschneiden sich genau dann, wenn jeder vor dem Ende des anderen beginnt. Diese eine Bedingung ersetzt lange Fallunterscheidungen mit DAY(), MONTH() und YEAR().

Unterabfragen in der SELECT-Liste

Eine skalare Unterabfrage darf auch als berechnete Spalte in der SELECT-Liste stehen. Sie ist dann fast immer korreliert und bekommt einen Spaltenalias:

SELECT s.titel,
       s.plaetze,
       (SELECT COUNT(*)
        FROM teilnahme AS t
        WHERE t.schulung_id = s.schulung_id) AS belegt
FROM schulung AS s;

Diese Schreibweise hat drei Vorteile, die in Aufgaben oft den Ausschlag geben:

• Alle Zeilen bleiben erhalten, auch ohne passende Datensätze. Das leistet sonst nur ein LEFT JOIN.

• Mehrere Kennzahlen aus verschiedenen Tabellen lassen sich nebeneinander berechnen, ohne dass sich Zeilen vervielfachen. Zwei LEFT JOINs auf zwei 1:n-Tabellen mit anschließendem COUNT würden dagegen jede Kombination zählen (siehe „Vorsicht, Vervielfachung“ im JOIN-Artikel).

• Kein GROUP BY nötig – jede Unterabfrage fasst nur „ihre“ Zeilen zusammen.

Achte auf den Unterschied zwischen den Aggregatfunktionen, wenn die Unterabfrage keine Zeile findet: COUNT(*) liefert dann 0, SUM, AVG, MIN und MAX liefern NULL. Soll statt NULL eine 0 erscheinen, hilft COALESCE((SELECT SUM(…) …), 0) oder IFNULL(…, 0).

SELECT a.bezeichnung,
       (SELECT COUNT(*) FROM mitarbeiter AS m
         WHERE m.abteilung_id = a.abteilung_id) AS anzahl,
       COALESCE((SELECT SUM(m.wochenstunden) FROM mitarbeiter AS m
                  WHERE m.abteilung_id = a.abteilung_id), 0) AS stunden
FROM abteilung AS a;

Die IT erscheint mit 0 Personen und 0 Stunden (ohne COALESCE mit NULL Stunden).

Alle Kombinationen zählen

Soll eine Liste jede Kombination zweier Stammdaten zeigen – etwa jede Abteilung mit jeder Schulung samt Zahl der Teilnehmenden, auch wenn sie 0 ist –, kombinierst Du das Kreuzprodukt der Stammtabellen mit einer korrelierten Unterabfrage, die auf beide äußeren Tabellen verweist:

SELECT a.bezeichnung, s.titel,
       (SELECT COUNT(*)
        FROM teilnahme AS t
        JOIN mitarbeiter AS m ON m.mitarbeiter_id = t.mitarbeiter_id
        WHERE t.schulung_id = s.schulung_id
          AND m.abteilung_id = a.abteilung_id) AS teilnehmende
FROM abteilung AS a, schulung AS s
ORDER BY a.bezeichnung, s.titel;

Die Abfrage liefert 4 · 4 = 16 Zeilen, je Abteilung und Schulung eine.

Bedingte Aggregation als Alternative

Mehrere Unterabfragen auf dieselbe Tabelle, die sich nur in einer Bedingung unterscheiden, lassen sich auch mit CASE innerhalb der Aggregatfunktion schreiben. Das ist kürzer und liest die Tabelle nur einmal:

SELECT abteilung_id,
       AVG(CASE WHEN wochenstunden >= 35 THEN wochenstunden END) AS schnitt_vollzeit,
       AVG(CASE WHEN wochenstunden <  35 THEN wochenstunden END) AS schnitt_teilzeit
FROM mitarbeiter
GROUP BY abteilung_id;

CASE ohne ELSE liefert NULL, und AVG überspringt NULL-Werte – so geht jede Zeile nur in „ihren“ Durchschnitt ein.

Anteile in Prozent

Für einen Anteil brauchst Du zwei Zahlen: den Teil und das Ganze. Das Ganze liefert eine skalare Unterabfrage:

-- Anteil der Werkstatt an allen Mitarbeitenden in Prozent
SELECT COUNT(*) * 100 / (SELECT COUNT(*) FROM mitarbeiter) AS anteil_werkstatt
FROM mitarbeiter
WHERE abteilung_id = 2;

Das Ergebnis ist 50 (3 von 6). Die WHERE-Bedingung der äußeren Abfrage gilt nicht für die Unterabfrage – sonst wäre das Ergebnis 100. Mit GROUP BY bekommst Du den Anteil jeder Gruppe:

SELECT abteilung_id,
       COUNT(*) AS anzahl,
       ROUND(COUNT(*) * 100 / (SELECT COUNT(*) FROM mitarbeiter), 1) AS anteil_prozent
FROM mitarbeiter
GROUP BY abteilung_id;

Drei Punkte solltest Du dabei beachten:

• Gleicher Bezugsrahmen: Gilt die Auswertung nur für einen Zeitraum (z. B. ein Jahr), muss dieselbe Einschränkung auch in der Unterabfrage stehen. Sonst bezieht sich der Anteil auf alle Jahre, und die Prozente ergeben zusammen nicht 100.

• Division: In MariaDB liefert / immer eine Dezimalzahl (3 · 100 / 6 = 50.0000). Andere Datenbanksysteme rechnen bei zwei ganzen Zahlen ganzzahlig (1 · 100 / 6 = 16). Erst mit 100 multiplizieren, dann teilen, und mit ROUND(…, 1) sinnvoll runden.

• Division durch null: Ist das Ganze 0, liefert MariaDB NULL (mit Warnung). Wo das vorkommen kann, fängst Du es mit NULLIF(…, 0) oder einer Bedingung ab.

Unterabfragen in FROM: abgeleitete Tabellen

Das Ergebnis einer Abfrage ist selbst eine Tabelle und kann deshalb in der FROM-Klausel stehen. Eine solche abgeleitete Tabelle (derived table) braucht in MariaDB immer einen Alias. Sie löst ein häufiges Problem: Ein Spaltenalias aus der SELECT-Liste ist in der WHERE-Klausel derselben Abfrage noch unbekannt, weil WHERE vor SELECT ausgewertet wird. Willst Du nach einer berechneten Spalte filtern, berechnest Du sie in der abgeleiteten Tabelle und filterst außen:

-- Schulungen mit weniger als 50 % Auslastung
SELECT titel, auslastung
FROM (SELECT s.titel,
             (SELECT COUNT(*)
              FROM teilnahme AS t
              WHERE t.schulung_id = s.schulung_id) * 100 / s.plaetze AS auslastung
      FROM schulung AS s) AS belegung
WHERE auslastung < 50;

Ohne abgeleitete Tabelle müsstest Du den ganzen Ausdruck in der WHERE-Klausel wiederholen. MariaDB erlaubt außerdem – abweichend vom SQL-Standard – Spaltenaliase in HAVING auch ohne GROUP BY (… HAVING auslastung < 50). Das ist kurz, aber nicht portabel.

Abgeleitete Tabellen eignen sich auch, um mit Gruppenergebnissen weiterzurechnen, etwa nur Abteilungen mit einem Durchschnitt über 30 Stunden anzuzeigen und dabei die Bezeichnung per JOIN zu ergänzen. Wer dieselbe Unterabfrage mehrfach braucht, kann sie in MariaDB (ab Version 10.2) mit WITH name AS (SELECT …) als Common Table Expression vor die Abfrage stellen.

Unterabfragen beim Einfügen, Ändern und Löschen

Unterabfragen sind nicht auf SELECT beschränkt. In INSERT, UPDATE und DELETE bestimmen sie, welche Datensätze übertragen, geändert oder gelöscht werden.

Archivieren in drei Schritten

Alte Daten werden häufig nicht einfach gelöscht, sondern zuerst in eine Archivtabelle verschoben. Das geschieht in drei Schritten:

1. Archivtabelle anlegen (einmalig) mit CREATE TABLE, oft mit weniger Spalten als das Original, wenn nicht alles aufbewahrt werden darf oder muss.

2. Datensätze übertragen mit INSERT INTO … SELECT – die SELECT-Abfrage ersetzt die VALUES-Liste und darf selbst Unterabfragen enthalten.

3. Übertragene Datensätze löschen mit DELETE, entweder mit derselben Bedingung wie in Schritt 2 oder über eine Unterabfrage auf die Archivtabelle.

CREATE TABLE schulung_archiv (
  schulung_id  INT NOT NULL PRIMARY KEY,
  titel        VARCHAR(40) NOT NULL,
  teilnehmende INT NOT NULL
);

INSERT INTO schulung_archiv (schulung_id, titel, teilnehmende)
SELECT s.schulung_id, s.titel,
       (SELECT COUNT(*) FROM teilnahme AS t WHERE t.schulung_id = s.schulung_id)
FROM schulung AS s
WHERE s.schulung_id IN (1, 3);

DELETE FROM teilnahme
WHERE schulung_id IN (SELECT schulung_id FROM schulung_archiv);

DELETE FROM schulung
WHERE schulung_id IN (SELECT schulung_id FROM schulung_archiv);

Die Reihenfolge ist wichtig: Erst übertragen, dann löschen – und zuerst die abhängigen Datensätze (teilnahme), dann die referenzierten (schulung), sofern der Fremdschlüssel nicht mit ON DELETE CASCADE angelegt ist. Damit nicht zwischen den Schritten etwas schiefgeht (Absturz, parallele Änderungen), fasst Du sie in einer Transaktion zusammen: START TRANSACTION; … COMMIT;. Ob die Archivtabelle bereits existiert, kannst Du mit CREATE TABLE IF NOT EXISTS abfangen.

Statt die Spalten einzeln anzulegen, kannst Du eine Archivtabelle mit gleichem Aufbau auch mit CREATE TABLE schulung_archiv LIKE schulung; erzeugen. CREATE TABLE … AS SELECT … legt Tabelle und Inhalt in einem Schritt an, übernimmt aber keine Schlüssel.

Löschen und Ändern mit Unterabfrage

Datensätze ohne Partner löschst Du mit denselben Bedingungen wie beim Suchen:

-- Schulungen ohne Teilnahme löschen
DELETE FROM schulung
WHERE NOT EXISTS (SELECT *
                  FROM teilnahme AS t
                  WHERE t.schulung_id = schulung.schulung_id);

In UPDATE kann eine Unterabfrage in der WHERE-Klausel stehen oder den neuen Wert liefern, zum Beispiel UPDATE schulung SET plaetze = plaetze + 2 WHERE schulung_id IN (SELECT schulung_id FROM teilnahme GROUP BY schulung_id HAVING COUNT(*) >= 2);. Ein typischer Fall ist das Verschieben von Terminen für alles, was zu einem Datensatz mit bekanntem Namen gehört: UPDATE fertigungsplan SET fertigungsdatum = DATE_ADD(fertigungsdatum, INTERVAL 7 DAY) WHERE fertigungsdatum = '2027-03-05' AND maschine_id IN (SELECT maschine_id FROM maschine WHERE bezeichnung = 'Fräse 2'); – die Unterabfrage übersetzt den Namen in den Schlüssel, DATE_ADD rechnet mit Kalendertagen.

Lange war es in MySQL und MariaDB verboten, in einer Unterabfrage aus der Tabelle zu lesen, die gerade geändert oder gelöscht wird (Fehler 1093). MariaDB erlaubt das bei DELETE seit Version 10.3.1 und bei UPDATE seit 10.3.2, MySQL bis heute nicht. Wer portabel bleiben will, weicht auf einen Verbund oder eine abgeleitete Tabelle aus.

Mengenoperationen: UNION und UNION ALL

Während ein JOIN Tabellen nebeneinander stellt (mehr Spalten), stellen die Mengenoperationen die Ergebnisse mehrerer Abfragen untereinander (mehr Zeilen). UNION bildet die Vereinigungsmenge:

-- Alle Personen der Werkstatt, Festangestellte und Praktika
SELECT 'Mitarbeiter*in' AS art, vorname, nachname
FROM mitarbeiter
WHERE abteilung_id = 2
UNION ALL
SELECT 'Praktikum', vorname, nachname
FROM praktikum
WHERE abteilung_id = 2
ORDER BY nachname;

Dafür gelten feste Regeln:

• Alle Teilabfragen liefern gleich viele Spalten, an gleicher Stelle mit verträglichen Datentypen. Die Spalten werden nach ihrer Position zusammengefügt, nicht nach ihrem Namen.

• Die Spaltennamen des Ergebnisses stammen aus der ersten Teilabfrage. Aliase in den weiteren Teilabfragen sind erlaubt, wirken aber nicht.

• Eine Konstante wie 'Praktikum' kennzeichnet, aus welcher Tabelle eine Zeile stammt.

• ORDER BY steht einmal am Ende und sortiert das Gesamtergebnis; es verwendet die Spaltennamen der ersten Teilabfrage. WHERE, GROUP BY und HAVING gelten dagegen jeweils nur für ihre Teilabfrage.

UNION oder UNION ALL? UNION entfernt doppelte Ergebniszeilen (wie DISTINCT), UNION ALL behält alle. Die Abteilungsnummern aller Festangestellten und Praktika liefern mit UNION die Liste NULL, 1, 2, 3, 4 (Alex Kaiser hat keine Abteilung), mit UNION ALL acht Werte (1, 2, 2, 2, 3, NULL, 2, 4). UNION ALL ist schneller, weil keine Duplikate gesucht werden müssen, und immer dann richtig, wenn gleiche Zeilen etwas bedeuten – etwa, wenn Du anschließend zählst oder summierst. Kann es keine Duplikate geben (wie oben dank der Spalte art), ist UNION ALL ebenfalls die bessere Wahl.

Die Grafik stellt die Mengenoperationen dieses und des nächsten Abschnitts am Beispiel der Abteilungsnummern nebeneinander:

Aktuelle und archivierte Daten gemeinsam auswerten

Liegen gleich aufgebaute Daten in zwei Tabellen, etwa aktuelle und archivierte Buchungen, verbindest Du sie mit UNION ALL. Gruppierst Du in jeder Teilabfrage, erhältst Du je Teilabfrage eigene Gruppen. Das ist in Ordnung, wenn die Gruppen sich nicht überschneiden (das Archiv enthält nur Vorjahre, die aktuelle Tabelle nur das laufende Jahr, gruppiert wird nach Jahr). Kommt dieselbe Gruppe in beiden Tabellen vor, erscheint sie aber zweimal. Dann vereinigst Du zuerst die Rohdaten in einer abgeleiteten Tabelle und gruppierst außen:

-- Personen je Abteilung, Festangestellte und Praktika zusammen
SELECT a.bezeichnung, COUNT(*) AS personen
FROM (SELECT abteilung_id FROM mitarbeiter
      UNION ALL
      SELECT abteilung_id FROM praktikum) AS p
JOIN abteilung AS a ON a.abteilung_id = p.abteilung_id
GROUP BY a.abteilung_id, a.bezeichnung;

Die Werkstatt erscheint hier einmal mit 4 Personen. Mit UNION statt UNION ALL wären es nur 1 – die gleichen Abteilungsnummern wären vor dem Zählen zusammengefallen.

INTERSECT und EXCEPT

Die Schnittmenge und die Differenz zweier Ergebnisse bilden INTERSECT und EXCEPT. MariaDB kennt beide seit Version 10.3, die Varianten INTERSECT ALL und EXCEPT ALL (mit Duplikaten) seit 10.5. In Oracle heißt EXCEPT MINUS. Für MySQL gibt es beide Operatoren erst seit Version 8.0.31.

-- Abteilungen mit Festangestellten UND Praktika
SELECT abteilung_id FROM mitarbeiter
INTERSECT
SELECT abteilung_id FROM praktikum;      -- Ergebnis: 2

-- Abteilungen ohne Festangestellte
SELECT abteilung_id FROM abteilung
EXCEPT
SELECT abteilung_id FROM mitarbeiter;    -- Ergebnis: 4

Wie bei UNION müssen Spaltenzahl und Datentypen übereinstimmen, verglichen werden ganze Zeilen, und Duplikate werden entfernt. INTERSECT bindet stärker als UNION und EXCEPT; im Zweifel setzt Du Klammern. Beide lassen sich durch Unterabfragen ersetzen: INTERSECT durch IN bzw. EXISTS, EXCEPT durch NOT IN bzw. NOT EXISTS. Das ist in älteren Systemen nötig – und bei EXCEPT auf die NULL-Falle achten: EXCEPT behandelt NULL wie einen normalen Wert, NOT IN nicht.

Unterabfrage oder JOIN?

Viele Aufgaben lassen sich sowohl mit Unterabfrage als auch mit JOIN lösen. Die Datenbank optimiert beide Varianten oft zum selben Ausführungsplan, entscheidend sind meist Lesbarkeit und Korrektheit:

Schritt für Schritt zur Abfrage mit Unterabfrage

Bei Aufgaben hilft ein festes Vorgehen:

1. Ergebnistabelle lesen: Welche Spalten, welche Zeilen? Erscheinen Zeilen mit 0 oder NULL? Dann eher Unterabfrage in der SELECT-Liste (oder LEFT JOIN).

2. Teilfragen erkennen: Steckt ein Vergleichswert („mehr als der Durchschnitt“, „teurer als X“), ein Gesamtwert („Anteil an allen“) oder eine Existenzfrage („ohne“, „noch nie“, „frei“) in der Aufgabe? Jede davon ist ein Kandidat für eine Unterabfrage.

3. Unterabfrage einzeln schreiben und prüfen: Was liefert sie – einen Wert, eine Liste, eine Tabelle? Passt der Operator (=, IN, EXISTS)?

4. Korrelation festlegen: Muss sich die Unterabfrage auf die äußere Zeile beziehen? Dann Aliase vergeben und die Verbindung über Schlüssel herstellen.

5. Bezugsrahmen abgleichen: Gelten Filter (Zeitraum, Status) für außen und innen?

6. Sonderfälle prüfen: NULL bei NOT IN, leere Unterabfrage, Division durch null, Duplikate bei UNION.

Viele Syntaxübersichten und Lehrbücher verwenden eine allgemeine SQL-Syntax, die nicht in allen Punkten MariaDB entspricht. TODAY und CURRENT heißen in MariaDB CURDATE() und NOW(), DATEDIFF(Datumsteil, …) entspricht TIMESTAMPDIFF(einheit, start, ende), und DELETE * FROM … ist kein gültiges SQL – richtig ist DELETE FROM …. In Aufgaben auf Papier zählt die richtige Idee; in der Praxis die genaue Syntax Deines Datenbanksystems.

Zum Üben findest Du zwei umfangreiche Aufgaben in den Übungsaufgaben zu Unterabfragen und Mengenoperationen.

Leitfragen

1. Was unterscheidet eine skalare Unterabfrage, eine Listen-Unterabfrage und eine abgeleitete Tabelle, und an welchen Stellen einer Abfrage stehen sie? Antwort

Eine skalare Unterabfrage liefert genau einen Wert und steht z. B. in einem Vergleich in WHERE/HAVING oder als Spalte in der SELECT-Liste. Eine Listen-Unterabfrage liefert eine Spalte mit beliebig vielen Zeilen und steht hinter IN, NOT IN, ANY oder ALL. Eine abgeleitete Tabelle liefert beliebig viele Zeilen und Spalten, steht in der FROM-Klausel und braucht in MariaDB einen Alias (siehe Abschnitt „Abfragen in Abfragen“ und Abschnitt „Unterabfragen in FROM: abgeleitete Tabellen“).

2. Wie findest Du alle Datensätze, deren Wert über dem Durchschnitt oder über dem Wert eines bestimmten anderen Datensatzes liegt, und was passiert, wenn die Unterabfrage mehr als eine Zeile liefert? Antwort

Du vergleichst mit einer skalaren Unterabfrage, z. B. WHERE wochenstunden > (SELECT AVG(wochenstunden) FROM mitarbeiter) oder mit dem Wert eines anderen Datensatzes über dessen Schlüssel (… WHERE mitarbeiter_id = 3). Liefert die Unterabfrage hinter =, < oder > mehr als eine Zeile, bricht MariaDB mit Subquery returns more than 1 row ab – ist eine Liste gemeint, gehört IN statt = hin (siehe Abschnitt „Vergleich mit einem berechneten Wert: die skalare Unterabfrage“ und Abschnitt „Fehler: Die Unterabfrage liefert mehr als eine Zeile“).

3. Warum liefert NOT IN ein leeres Ergebnis, sobald die Unterabfrage NULL enthält, und welche Alternativen gibt es? Antwort

x NOT IN (a, b, NULL) bedeutet x <> a AND x <> b AND x <> NULL; der Vergleich mit NULL ist unbekannt, deshalb wird die ganze AND-Verknüpfung nie wahr und das Ergebnis ist leer. Abhilfe: NULL in der Unterabfrage mit WHERE … IS NOT NULL ausschließen oder gleich NOT EXISTS verwenden, das die Falle nicht kennt (siehe Abschnitt „Die NULL-Falle bei NOT IN“).

4. Woran erkennst Du eine korrelierte Unterabfrage, wozu brauchst Du dabei Aliase, und woran merkst Du, dass die Korrelation fehlt? Antwort

Eine korrelierte Unterabfrage bezieht sich auf die gerade bearbeitete Zeile der äußeren Abfrage, z. B. über WHERE x.abteilung_id = m.abteilung_id. Aliase brauchst Du, um innere und äußere Tabelle zu unterscheiden – bei derselben Tabelle sind sie Pflicht. Liefert die Unterabfrage in jeder Zeile denselben Wert, fehlt meist die Korrelation, oft weil die äußere Tabelle innen noch einmal aufgeführt wurde und den äußeren Alias überdeckt (siehe Abschnitt „Korrelierte Unterabfragen: Bezug auf die äußere Zeile“ und Abschnitt „Fehler: Die Korrelation fehlt“).

5. Welche Schreibweisen gibt es, um Datensätze ohne Partner zu finden oder zu löschen? Antwort

Gleichwertig sind NOT EXISTS (SELECT * FROM t WHERE t.fk = a.pk) (robust auch bei NULL), a.pk NOT IN (SELECT fk FROM t) (NULL-Falle beachten), das Zählen mit (SELECT COUNT(*) …) = 0 und der Anti-Join LEFT JOIN … WHERE t.fk IS NULL. Zum Löschen nutzt Du dieselben Bedingungen in DELETE FROM … WHERE NOT EXISTS (…) (siehe Abschnitt „EXISTS und NOT EXISTS“ und Abschnitt „Löschen und Ändern mit Unterabfrage“).

6. Wie berechnest Du den Anteil einer Gruppe an der Gesamtzahl in Prozent, und warum muss ein Zeitraumfilter auch in der Unterabfrage stehen? Antwort

Den Teil zählst Du in der äußeren Abfrage, das Ganze liefert eine skalare Unterabfrage: COUNT(*) * 100 / (SELECT COUNT(*) FROM mitarbeiter), erst mit 100 multiplizieren, dann teilen und ggf. mit ROUND runden. Die WHERE-Bedingung der äußeren Abfrage gilt nicht für die Unterabfrage – ein Zeitraumfilter muss deshalb auch dort stehen, sonst bezieht sich der Anteil auf alle Jahre und die Prozente ergeben zusammen nicht 100 (siehe Abschnitt „Anteile in Prozent“).

7. In welcher Reihenfolge archivierst Du Datensätze, und warum gehören die Schritte in eine Transaktion? Antwort

Erst legst Du die Archivtabelle an, dann überträgst Du die Datensätze mit INSERT INTO … SELECT, und erst danach löschst Du sie mit DELETE – zuerst die abhängigen Datensätze, dann die referenzierten (sofern kein ON DELETE CASCADE). In eine Transaktion (START TRANSACTION; … COMMIT;) gehören die Schritte, damit bei Absturz oder parallelen Änderungen keine Daten verloren gehen oder doppelt vorliegen (siehe Abschnitt „Archivieren in drei Schritten“).

8. Welche Regeln gelten für UNION, wann nimmst Du UNION ALL, und wie wertest Du aktuelle und archivierte Daten gemeinsam aus? Antwort

Alle Teilabfragen brauchen gleich viele Spalten mit verträglichen Datentypen (zusammengefügt nach Position), die Spaltennamen kommen aus der ersten Teilabfrage, und ORDER BY steht einmal am Ende. UNION entfernt Duplikate, UNION ALL behält sie – nimm es, wenn gleiche Zeilen etwas bedeuten (Zählen, Summieren) oder keine Duplikate entstehen können. Aktuelle und archivierte Daten vereinigst Du mit UNION ALL in einer abgeleiteten Tabelle und gruppierst außen, damit gleiche Gruppen nicht doppelt erscheinen (siehe Abschnitt „Mengenoperationen: UNION und UNION ALL“ und Abschnitt „Aktuelle und archivierte Daten gemeinsam auswerten“).

9. Wie bildest Du Schnittmenge und Differenz mit INTERSECT und EXCEPT, und wie ersetzt Du sie durch Unterabfragen? Antwort

INTERSECT liefert die Zeilen, die in beiden Ergebnissen vorkommen, EXCEPT die Zeilen des ersten Ergebnisses, die im zweiten fehlen; Spaltenzahl und Datentypen müssen übereinstimmen, Duplikate werden entfernt (MariaDB ab 10.3). Ersetzen kannst Du INTERSECT durch IN bzw. EXISTS und EXCEPT durch NOT IN bzw. NOT EXISTS – bei NOT IN aber auf die NULL-Falle achten (siehe Abschnitt „INTERSECT und EXCEPT“).

Links und weitere Informationen

• Übungsaufgaben zu SQL-Unterabfragen und Mengenoperationen

• SQL: Abfragen über mehrere Tabellen mit JOIN

• SQL: Aggregierende Funktionen

• SQL: Selektion und Projektion

• SQL-DML: Datensätze einfügen und löschen

• SQL-Übungsaufgaben auf oer-informatik.de

• als Nachschlagewerk: MariaDB Knowledge Base: Subqueries

• als Nachschlagewerk: MariaDB Knowledge Base: UNION, INTERSECT und EXCEPT

• zu Unterabfragen auf dieselbe Tabelle: MariaDB Knowledge Base: DELETE und UPDATE

Hinweis zur Nachnutzung als Open Educational Resource (OER)

Dieser Artikel und seine Texte, Bilder, Grafiken, Code und sonstiger Inhalt sind - sofern nicht anders angegeben - lizenziert unter CC BY 4.0. Nennung gemäß TULLU-Regel bitte wie folgt: “SQL: Unterabfragen und Mengenoperationen” von oer-informatik.de (H. Stein), Lizenz: CC BY 4.0. Der Artikel wurde unter https://oer-informatik.de/sql-unterabfragen veröffentlicht, die Quelltexte sind in weiterverarbeitbarer Form verfügbar im Repository unter https://gitlab.com/oer-informatik/db-sql/sql. Erstellt mit KI-Assistenz; von mir umfassend redaktionell überarbeitet, geprüft und freigegeben. Stand: 28.10.2026.

[Kommentare zum Artikel lesen, schreiben] / [Artikel teilen] / [gitlab-Issue zum Artikel schreiben]