Die COALESCE-Funktion in SQL: Syntax und Beispiele

COALESCE ist eine Funktion, die eine Liste von Argumenten entgegennimmt und das erste zurückgibt, das nicht NULL ist. Am häufigsten wird sie verwendet, um in Abfrageergebnissen einen Standardwert anstelle von NULL einzusetzen.

MySQL 8.1
COALESCE(wert_1, wert_2, ..., wert_N)

Sie funktioniert in allen verbreiteten DBMS identisch: MySQL, PostgreSQL, SQLite, SQL Server, Oracle. Sie ist Teil des SQL-Standards — deshalb sollte man COALESCE den nicht standardisierten Funktionen IFNULL, ISNULL und NVL vorziehen. Dazu weiter unten mehr.

Wie COALESCE funktioniert

Die Funktion prüft ihre Argumente von links nach rechts und stoppt beim ersten, das nicht NULL ist:

MySQL 8.1
SELECT COALESCE(NULL, NULL, 'SQL Academy', 'Ersatzwert') AS result;

Die ersten beiden Argumente sind NULL und werden übersprungen. Das dritte Argument ist nicht NULL — es wird zum Ergebnis, und der String 'Ersatzwert' wird gar nicht mehr ausgewertet.

Sind alle Argumente NULL, ist auch das Ergebnis NULL.

Praxisbeispiel: NULL im Bericht ersetzen

Schauen wir uns die Tabelle orders aus der Datenbank eines Lieferdienstes an. Die Spalte promo_code enthält den Promocode der Bestellung — wurde ohne Promocode bestellt, steht dort NULL:

MySQL 8.1
SELECT order_id, total_amount, promo_code
FROM orders
WHERE order_id IN (1, 4, 8, 13, 24)
ORDER BY order_id;

In einem Bericht für das Management wirken leere Zellen wie ein Exportfehler. Setzen wir statt NULL einen verständlichen Text ein:

MySQL 8.1
SELECT order_id, total_amount, COALESCE(promo_code, 'ohne Promocode') AS promo
FROM orders
WHERE order_id IN (1, 4, 8, 13, 24)
ORDER BY order_id;

Wo ein Promocode existiert, gibt COALESCE ihn unverändert zurück. Wo NULL stand, wird der Text aus dem zweiten Argument eingesetzt. Der Block oben ist live: Ändern Sie den Ersatztext oder wickeln Sie die Spalte delivery_time_min in COALESCE ein und sehen Sie, was passiert.

Eine Kette von Ersatzwerten

Die Stärke von COALESCE: Die Anzahl der Argumente ist beliebig. Ein Klassiker ist die Wahl des ersten verfügbaren Kontakts eines Kunden — Festnetz, sonst Mobilnummer, sonst ein Platzhalter:

MySQL 8.1
SELECT name, COALESCE(work_phone, mobile_phone, 'keine Kontaktdaten') AS contact
FROM clients;

Jede Zeile durchläuft die Prüfung von links nach rechts unabhängig von den anderen: Anna bekam die Festnetznummer, Boris die Mobilnummer, Vera den Platzhalter.

COALESCE mit Aggregatfunktionen

Die Aggregatfunktionen SUM, AVG, MIN, MAX liefern NULL, wenn die Gruppe keinen einzigen Wert enthält. Berechnen wir die durchschnittliche Lieferzeit pro Bestellstatus:

MySQL 8.1
SELECT status, ROUND(AVG(delivery_time_min)) AS avg_delivery
FROM orders
GROUP BY status
ORDER BY status;

Stornierte Bestellungen haben keine Lieferzeit — sie wurden nie geliefert, also gibt AVG NULL zurück. Fließt dieses Ergebnis in weitere Berechnungen oder ein Diagramm, macht NULL sie kaputt. Wickeln wir das Aggregat in COALESCE ein:

MySQL 8.1
SELECT status, COALESCE(ROUND(AVG(delivery_time_min)), 0) AS avg_delivery
FROM orders
GROUP BY status
ORDER BY status;

Wichtig ist die Bedeutung der Ersetzung: Die Null steht hier für „keine Daten", nicht für „sofort geliefert". In Berichten, in denen dieser Unterschied zählt, lässt man besser NULL stehen oder weist solche Gruppen separat aus.

NULLIF: die Gegenspielerin

NULLIF macht das Gegenteil: Sie verwandelt einen Wert in NULL. Sie nimmt genau zwei Argumente und gibt NULL zurück, wenn beide gleich sind, sonst das erste Argument:

MySQL 8.1
NULLIF(wert_1, wert_2)

MySQL 8.1
SELECT NULLIF('delivered', 'canceled') AS a, NULLIF('canceled', 'canceled') AS b;

Schutz vor Division durch null

Der häufigste Einsatz von NULLIF ist der Nenner eines Bruchs. In PostgreSQL bricht eine Division durch null die Abfrage ab:

MySQL 8.1
SELECT 100 / 0 AS result;

MySQL liefert in derselben Situation stillschweigend NULL samt Warnung. NULLIF macht das Verhalten in beiden DBMS einheitlich und vorhersehbar:

MySQL 8.1
SELECT 100 / NULLIF(0, 0) AS result;

Der Nenner wurde zu NULL, die Division durch NULL ergab NULL — die Abfrage ist nicht abgestürzt. Außen kann man noch COALESCE ergänzen und etwa eine Null zurückgeben.

Leere Strings mit NULLIF und COALESCE bereinigen

In echten Daten gibt es neben NULL oft leere Strings und Strings aus Leerzeichen. COALESCE ersetzt sie nicht — ein leerer String ist nicht NULL. Unsere Tabelle orders hat genau dieses Problem:

MySQL 8.1
SELECT order_id, promo_code, COALESCE(promo_code, 'ohne Promocode') AS promo
FROM orders
WHERE order_id IN (1, 2, 3, 8, 24)
ORDER BY order_id;

Bestellung 1 ist korrigiert, aber die Bestellungen 2 und 3 mit Leerzeichen und leerem String bleiben „Löcher" im Bericht. Die Lösung ist eine Kombination aus drei Funktionen: TRIM entfernt die Leerzeichen, NULLIF verwandelt den leeren String in NULL, COALESCE setzt den Platzhalter ein:

MySQL 8.1
SELECT order_id, COALESCE(NULLIF(TRIM(promo_code), ''), 'ohne Promocode') AS promo
FROM orders
WHERE order_id IN (1, 2, 3, 8, 24)
ORDER BY order_id;

Diese Kombination ist das Arbeitspferd der Datenbereinigung — merken lohnt sich.

Was liefert der Ausdruck COALESCE(NULL, '', 'SQL Academy')?

COALESCE vs IFNULL vs ISNULL vs NVL

COALESCE hat nicht standardisierte Verwandte, die an bestimmte DBMS gebunden sind:

Die größte Falle ist ISNULL. In SQL Server ersetzt sie NULL mit zwei Argumenten, während die gleichnamige Funktion in MySQL nur ein Argument nimmt und es schlicht auf NULL prüft — Ergebnis 1 oder 0:

MySQL 8.1
SELECT ISNULL(NULL) AS is_null, ISNULL('Text') AS is_not_null, IFNULL(NULL, 'Ersatz') AS replaced;

Code mit ISNULL(a, b), der von SQL Server nach MySQL übernommen wird, schlägt mit einem Fehler über die falsche Argumentanzahl fehl.

PostgreSQL kennt weder IFNULL noch ISNULL — eine Abfrage damit endet mit einem Fehler:

MySQL 8.1
SELECT IFNULL(promo_code, 'ohne Promocode') FROM orders;

Der einzige Weg, NULL hier zu ersetzen, ist COALESCE — ein Grund mehr, gleich damit zu schreiben.

Das Fazit ist einfach: Wenn der Code je auf einem anderen DBMS laufen könnte — schreiben Sie COALESCE, und er funktioniert überall.

Welche Funktion zum Ersetzen von NULL funktioniert in MySQL, PostgreSQL und SQL Server gleichermaßen?

Wo sich COALESCE unerwartet verhält

Inkompatible Argumenttypen. PostgreSQL verlangt, dass sich alle Argumente auf einen Typ bringen lassen. Der Versuch, einen String anstelle eines numerischen NULL einzusetzen, endet mit einem Fehler:

MySQL 8.1
SELECT COALESCE(delivery_time_min, 'keine Daten') FROM orders;

MySQL wandelt in derselben Situation die Zahl stillschweigend in einen String um — die Abfrage läuft, aber die Spalte wechselt ihren Typ, was die lesende Anwendung überraschen kann.

COALESCE ersetzt kein IS NULL in Bedingungen. Der Vergleich WHERE promo_code = NULL liefert keine einzige Zeile, und COALESCE hilft da nicht: Zum Filtern nach NULL gibt es den Operator IS NULL. Mehr dazu in der Lektion IS NULL, BETWEEN, IN.

Ein leerer String ist nicht NULL. COALESCE('', 'Ersatz') gibt den leeren String zurück, nicht den Ersatz. Wenn „leer" in Ihren Daten „kein Wert" bedeutet, nutzen Sie die Kombination NULLIF und COALESCE.

Wie weiter

Der beste Weg, COALESCE zu festigen, ist die praktische Anwendung:

• lösen Sie SQL-Aufgaben im Trainer — Funktionen für den Umgang mit NULL kommen dort regelmäßig vor;
• wie sich NULL in Vergleichen und Filtern verhält, zeigt die Lektion IS NULL, BETWEEN, IN;
• Kurzreferenzen zu den Funktionen finden Sie im Handbook: COALESCE, IFNULL, NULLIF.