COALESCE gibt den ersten Wert seiner Liste zurück, der nicht NULL ist. So ersetzt SQL einen fehlenden Wert durch einen Ersatzwert. Die Beispiele funktionieren in PostgreSQL, MySQL, SQLite und SQL Server.
So verwendest du COALESCE
Übergib COALESCE beliebig viele Werte. Es prüft sie von links nach rechts und gibt den ersten zurück, der nicht NULL ist – oder NULL, wenn alle NULL sind:
COALESCE(value1, value2, ...)
SELECT COALESCE(NULL, 'second', 'third');
-- Output: second
SELECT COALESCE(NULL, NULL);
-- Output: NULL
In einer echten Abfrage sind die Werte meist Spalten, mit einem Literal am Ende als letzte Rettung:
-- Input: users
-- id | name | nickname
-- ---+--------------+---------
-- 1 | Maria Garcia | Mia
-- 2 | Tom Becker | NULL
-- 3 | Priya Shah | P
SELECT id, name, COALESCE(nickname, name) AS display_name
FROM users;
-- Output:
-- id | name | display_name
-- ---+--------------+-------------
-- 1 | Maria Garcia | Mia
-- 2 | Tom Becker | Tom Becker
-- 3 | Priya Shah | P
Wichtige Regeln
- Es stoppt beim ersten Wert, der nicht NULL ist, spätere Argumente werden danach also nicht mehr ausgewertet. Setz den Wert, der am häufigsten gefüllt ist, an den Anfang.
- Alle Argumente brauchen einen kompatiblen Typ. Zahlen mit Text zu mischen schlägt in PostgreSQL und SQL Server fehl, also caste vorher:
-- Input: products
-- name | stock
-- -------------+------
-- Desk Lamp | 12
-- Office Chair | NULL
SELECT name, COALESCE(stock, 'N/A') AS stock FROM products;
-- PostgreSQL: ERROR: invalid input syntax for type integer: "N/A"
-- SQL Server: Conversion failed when converting the varchar value 'N/A' to data type int.
-- (in MySQL, cast with CAST(stock AS CHAR))
SELECT name, COALESCE(CAST(stock AS VARCHAR(10)), 'N/A') AS stock FROM products;
-- Output:
-- name | stock
-- -------------+------
-- Desk Lamp | 12
-- Office Chair | N/A
-
Ein leerer String ist nicht NULL. COALESCE reicht
''einfach durch. Pack die Spalte inNULLIF(column, ''), damit auch leere Strings als fehlend gelten:
SELECT COALESCE('', 'No name');
-- Output: '' (an empty string)
SELECT COALESCE(NULLIF('', ''), 'No name');
-- Output: No name
Alternative Syntax
Du begegnest auch Kurzformen mit zwei Argumenten: IFNULL in MySQL und SQLite sowie ISNULL in SQL Server:
SELECT IFNULL(NULL, 'fallback');
-- Output: fallback
Nimm lieber COALESCE. Es akzeptiert beliebig viele Argumente, und ISNULL in SQL Server gibt dem Ergebnis den Typ des ersten Arguments, was deinen Ersatzwert abschneiden kann:
-- SQL Server
DECLARE @code VARCHAR(2) = NULL;
SELECT ISNULL(@code, 'none') AS with_isnull, COALESCE(@code, 'none') AS with_coalesce;
-- Output:
-- with_isnull | with_coalesce
-- ------------+--------------
-- no | none
Ein CASE-Ausdruck erledigt dasselbe, nur ausführlicher: COALESCE(nickname, name) ist die Kurzform von CASE WHEN nickname IS NOT NULL THEN nickname ELSE name END. Im Leitfaden zur SQL-CASE-Anweisung erfährst du, wann du die zusätzliche Flexibilität brauchst.
Wann solltest du COALESCE verwenden?
Greif zu COALESCE, wann immer ein NULL dein Ergebnis falsch, unschön oder leer machen würde. Die meisten Anwendungsfälle fallen in eine dieser vier Kategorien.
Auf eine andere Spalte ausweichen
Wenn der gewünschte Wert in einer von mehreren Spalten steht, wählt COALESCE die erste gefüllte: Spitzname oder vollständiger Name, Handy oder Festnetz, Liefer- oder Rechnungsadresse.
Einen Standardwert nutzen, wenn alle Werte fehlen
Setz ein Literal ans Ende der Liste, und das Ergebnis ist nie NULL. Genau das willst du für Berichte und Oberflächen, wo „Adresse unbekannt“ oder 0.00 besser aussieht als ein leeres Feld.
Lücken mit einem berechneten Wert füllen
Der Ersatzwert muss keine Spalte oder Konstante sein. Es kann auch ein Ausdruck sein, etwa ein Benutzername oder eine E-Mail-Adresse aus anderen Spalten. Das macht COALESCE praktisch zum Aufräumen importierter Daten.
NULLs aus Berechnungen heraushalten
Jede Rechnung mit NULL ergibt NULL, ein fehlender Rabatt oder Bonus macht also die ganze Summe zunichte. COALESCE(discount, 0) verwandelt den fehlenden Wert in eine Zahl, mit der die Berechnung arbeiten kann.
Praxisbeispiele für COALESCE
Einen Preis anzeigen, egal welcher hinterlegt ist
Produkte haben vielleicht einen Angebotspreis, einen regulären Preis oder (vor dem Launch) keinen von beiden. Prüf sie der Reihe nach und nimm standardmäßig null:
-- Input: products
-- product_id | name | sale_price | regular_price
-- -----------+---------------+------------+--------------
-- 1 | Desk Lamp | 19.99 | 24.99
-- 2 | Office Chair | NULL | 149.00
-- 3 | Standing Desk | NULL | NULL
SELECT product_id, name, COALESCE(sale_price, regular_price, 0.00) AS price
FROM products;
-- Output:
-- product_id | name | price
-- -----------+---------------+-------
-- 1 | Desk Lamp | 19.99
-- 2 | Office Chair | 149.00
-- 3 | Standing Desk | 0.00
Die beste Telefonnummer auswählen
Kontakte haben bis zu drei Nummern. Zeig die erste verfügbare an und weise darauf hin, wenn es keine gibt:
-- Input: contacts
-- name | primary_phone | mobile_phone | work_phone
-- -----+---------------+--------------+-----------
-- Ada | 555-0101 | 555-0199 | NULL
-- Bo | NULL | 555-0142 | 555-0177
-- Cy | NULL | NULL | 555-0123
-- Di | NULL | NULL | NULL
SELECT name, COALESCE(primary_phone, mobile_phone, work_phone, 'No phone') AS phone
FROM contacts;
-- Output:
-- name | phone
-- -----+---------
-- Ada | 555-0101
-- Bo | 555-0142
-- Cy | 555-0123
-- Di | No phone
Eine Platzhalter-E-Mail-Adresse erzeugen
Mitarbeitende ohne hinterlegte E-Mail bekommen eine aus ihrem Namen, damit jede Zeile im Verzeichnis einen Wert hat:
-- Input: employees
-- employee_id | first_name | last_name | email
-- ------------+------------+-----------+-----------------
-- 1 | Grace | Hopper | ghopper@navy.mil
-- 2 | Alan | Turing | NULL
SELECT
employee_id,
first_name || ' ' || last_name AS full_name,
COALESCE(
email,
LOWER(first_name) || '.' || LOWER(last_name) || '@company.com'
) AS email
FROM employees;
-- Output:
-- employee_id | full_name | email
-- ------------+--------------+------------------------
-- 1 | Grace Hopper | ghopper@navy.mil
-- 2 | Alan Turing | alan.turing@company.com
In MySQL und SQL Server verbindest du die Strings mit CONCAT() statt mit ||.
Bestellsummen mit optionalen Rabatten berechnen
Ohne COALESCE sorgt schon eine Position ohne Rabatt dafür, dass die gesamte Bestellsumme falsch ist, weil SUM die NULL-Zeilen komplett überspringt:
-- Input: order_items
-- order_id | product | quantity | unit_price | discount
-- ---------+----------+----------+------------+---------
-- 101 | Keyboard | 1 | 49.00 | 5.00
-- 101 | Mouse | 2 | 19.50 | NULL
-- 102 | Monitor | 1 | 189.00 | NULL
-- 102 | Cable | 3 | 6.00 | 2.00
-- Wrong: lines with a NULL discount drop out of the total
SELECT order_id, SUM(quantity * unit_price - discount) AS total
FROM order_items
GROUP BY order_id
ORDER BY order_id;
-- Output:
-- order_id | total
-- ---------+------
-- 101 | 44.00
-- 102 | 16.00
-- Right: a missing discount counts as 0
SELECT order_id, SUM(quantity * unit_price - COALESCE(discount, 0)) AS total
FROM order_items
GROUP BY order_id
ORDER BY order_id;
-- Output:
-- order_id | total
-- ---------+-------
-- 101 | 83.00
-- 102 | 205.00
Weiterführende Infos zu COALESCE
Hier im Blog:
-
MySQL COALESCE: COALESCE im Vergleich zu
IFNULLin MySQL - Leitfaden zur SQL-CASE-Anweisung: Bedingungslogik jenseits von NULL-Prüfungen
- Leitfaden zu SQL „ungleich“: warum Vergleiche mit NULL nie zutreffen
- Leitfaden zu SQL GROUP BY: die Aggregatfunktionen, in die COALESCE oft einfließt
- Leitfaden zu SQL SUBSTRING: Text aus Spalten extrahieren, die NULL sein können
Offizielle Dokumentation:
Beekeeper Studio ist eine kostenlose & quelloffene Datenbank-GUI
Das beste SQL-Abfrage- & Editor-Tool, das ich je benutzt habe. Es bietet alles, was ich zum Verwalten meiner Datenbank brauche. - ⭐⭐⭐⭐⭐ Mit
Beekeeper Studio ist schnell, intuitiv und einfach zu bedienen. Beekeeper unterstützt jede Menge Datenbanken und läuft hervorragend auf Windows, Mac und Linux.
Was Nutzer über Beekeeper Studio sagen
„Beekeeper Studio hat meinen alten SQL-Workflow komplett ersetzt. Es ist schnell, intuitiv und macht die Arbeit mit Datenbanken wieder zum Vergnügen.“
„Ich habe viele Datenbank-GUIs ausprobiert, aber Beekeeper trifft die perfekte Balance zwischen Funktionsumfang und Einfachheit. Es funktioniert einfach.“