Wie man die Funktion COALESCE() in SQL verwendet

SQL-Benutzer werden oft mit NULL-Werten in ihren Abfragen konfrontiert und müssen diese richtig verarbeiten. Die Funktion COALESCE() hilft bei der Handhabung von NULL-Werten. Lesen Sie diesen Artikel, um zu erfahren, wie Sie COALESCE() in Ihren Abfragen verwenden können.

SQL-Tabellen speichern Daten in Datensätzen, und Datensätze bestehen aus Feldern. Es kann Situationen geben, in denen wir den Wert für ein bestimmtes Feld nicht kennen. Nehmen wir zum Beispiel an, wir haben eine Tabelle mit Daten für persons. Sie enthält die Felder first_name, last_name und marital_status. Wenn wir die marital_status für eine bestimmte Person nicht kennen, können wir mit SQL einen NULL-Wert für dieses Feld zuweisen. Der NULL-Wert bedeutet jedoch nicht, dass die Person kein marital_status hat; er bedeutet nur, dass wir diesen Wert nicht kennen".

Mit anderen Worten: SQL verwendet NULL-Werte, um das Fehlen eines Wertes darzustellen. NULL-Werte können jedoch schwierig zu handhaben sein. Deshalb hat SQL die Funktion COALESCE() eingeführt, über die wir in diesem Artikel sprechen werden.

Bevor wir uns mit den technischen Details von NULL und COALESCE() befassen, empfehle ich Ihnen unseren interaktiven Kurs über Standard-SQL-Funktionen. Er enthält einen umfassenden Überblick über NULL-Werte, NULL-bezogene Funktionen und andere gängige Funktionen in SQL.

Was bewirkt COALESCE()?

In SQL-Datenbanken lässt jeder Datentyp NULL als gültigen Wert zu, d.h. jede Spalte kann einen NULL-Wert haben, unabhängig davon, um welchen Datentyp es sich handelt. (Natürlich sind einige Spalten obligatorisch (nicht-nullbar), aber das wird vom Datenbankdesigner festgelegt, nicht vom Datentyp selbst.) Lassen Sie uns ein einfaches Beispiel anhand der Tabelle persons.

Wir können die SQL-Funktion COALESCE() verwenden, um den NULL-Wert durch einen einfachen Text zu ersetzen:

SELECT 	
  first_name, 
  last_name, 
  COALESCE(marital_status,'Unknown')
FROM persons

In der obigen Abfrage wird die Funktion COALESCE() verwendet, um den Wert 'Unknown' nur dann zurückzugeben, wenn marital_status NULL ist. Wenn marital_status nicht NULL ist, gibt COALESCE() den Wert der Spalte marital_status zurück. Mit anderen Worten: COALESCE() gibt das erste Nicht-NULL-Argument zurück.

Lernen Sie die Beispieldaten kennen

Für den Rest des Artikels werden wir die Funktion COALESCE() anhand der Tabelle stockdemonstrieren, die unten abgebildet ist.

Diese Tabelle speichert Produktdatensätze für einen Marktplatz und enthält die Spalten product, brand, subcategory, category, family, units, quantity_available (der aktuelle Bestand dieses Produkts) und minimum_to_have (der Schwellenwert, ab dem der Markt dieses Produkt bei seinen Lieferanten bestellen muss).

Sie werden feststellen, dass einige der Produkte eine Unterkategorie haben, andere jedoch nicht. Zum Beispiel gehört das Produkt "Schweinerippchen" zur Unterkategorie "Schweinefleisch" in der Kategorie "Fleisch" und der Familie "Lebensmittel". Das Produkt "Tomaten" gehört zur Kategorie "Gemüse" und zur Familie "Lebensmittel"; es hat keine Unterkategorie, daher steht in diesem Feld NULL.

Beispielhafte SQL-Abfragen mit der Funktion COALESCE()

Sehen wir uns nun an, wie die Funktion COALESCE() in einigen realistischen Beispielen verwendet werden kann.

Beispiel 1: Verwendung von COALESCE() zum Ersetzen von NULL durch ein Label

Wir möchten alle Produkte mit ihrer Unterkategorie, Kategorie und Familie anzeigen. Es gibt jedoch einige Produkte mit NULL in ihrer Kategorie oder Unterkategorie. Für diese Produkte möchten wir einen Text anzeigen: 'No Category' oder 'No Subcategory'. Hier ist die Abfrage, die wir verwenden würden:

SELECT product, 
  COALESCE(subcategory,'No Subcategory') AS subcategory,
  COALESCE(category,'No Category') AS category,
  COALESCE(family,'No Family') AS family
FROM stock

Wir verwenden die Funktion COALESCE(), um NULL-Werte durch einen Text zu ersetzen. Das Ergebnis können Sie unten sehen:

Beispiel 2: Verwendung von COALESCE() bei der Verkettung von NULL und Strings

Ein häufiges SQL-Problem im Zusammenhang mit NULL-Werten ist die String-Verkettung. Viele Operationen, die NULL-Werte beinhalten, geben als Ergebnis NULL zurück. Wenn wir zwei Strings verketten wollen und einer davon NULL ist, wird das Ergebnis der Verkettung NULL sein. Hier ist eine einfache Textkonkatenation:

SELECT 'Hello, how are you ' || 'Peter ' || '?'  AS example

Sie gibt zurück:

Wenn wir jedoch einen NULL-Wert verwenden ...

SELECT 'Hello, how are you ' || null || '?'  AS example

... erhalten wir:

Das Ergebnis ist NULL, da jede Verkettung von Textstrings mit einem NULL-Wert einen NULL-Wert ergibt. Um dies zu vermeiden, können wir die Funktion COALESCE() verwenden, um eine leere Zeichenkette (oder ein Leerzeichen) anstelle eines NULL-Wertes zurückzugeben. Nehmen wir zum Beispiel an, wir wollen eine Liste der Produktnamen mit dem Markennamen. Wir können die folgende Abfrage schreiben:

SELECT 
    product || ', brand: ' || COALESCE(brand, '--') AS product_brand
FROM stock

Wenn eine Marke NULL ist, setzen wir ein '--' anstelle von NULL ein. Beachten Sie das Ergebnis:

Beispiel 3: Verwendung von COALESCE() mit mehreren Argumenten

Sie können die Funktion COALESCE() mit mehr als zwei Argumenten verwenden. Angenommen, wir möchten einen Bericht erstellen, der Produkte und ihre Unterkategorien auflistet. Wenn die Unterkategorie NULL ist, wollen wir die Unterkategorie durch die Kategorie ersetzen. Und wenn sowohl Unterkategorie als auch Kategorie NULL sind, wollen wir sie durch die Produktfamilie ersetzen. Schauen wir uns die SQL-Abfrage an:

SELECT 
   product ||' - '||
   COALESCE(subcategory, category, family, 'no product description ')
   AS product_and_subcategory
FROM stock

Wir verwenden die Funktion COALESCE() mit vier Argumenten; das erste Nicht-NULL-Argument wird zurückgegeben, wie wir im untenstehenden Ergebnis sehen können:

Für weitere Details hierzu empfehle ich den Artikel How to Tackle SQL NULLs.

Beispiel 4: Verwendung von COALESCE() zum Ersetzen von NULL durch einen berechneten Wert

Die SQL-Funktion COALESCE() kann auch verwendet werden, um einen Wert zu berechnen oder zu schätzen, wenn dieser Wert nicht vorhanden ist. Zum Beispiel hat jedes Produkt einen Schwellenwert (dargestellt durch die Spalte minimum_to_have), der eine neue Bestellung beim Lieferanten erfordert. Einige Datensätze könnten jedoch einen NULL-Wert in der Spalte minimum_to_have haben; in diesem Fall können wir festlegen, dass der Schwellenwert 50% der Spalte quantity_available beträgt. Die Abfrage zur Berechnung des geschätzten Schwellenwerts lautet:

SELECT product,
  quantity_available,
  minimum_to_have,
  COALESCE(minimum_to_have, quantity_available * 0.5) AS threshold
FROM   stock

Die Funktion COALESCE() gibt hier minimum_to_have zurück, wenn der Wert minimum_to_have nicht NULL ist. Wenn minimum_to_have NULL ist, wird COALESCE() zurückgegeben. quantity_available * 0.5

Beispiel 5: Verwendung von COALESCE() mit der ROLLUP-Klausel

Im nächsten Beispiel verwenden wir die ROLLUP Klausel (eine Erweiterung von GROUP BY), um die Gesamtmenge der Produkte zu erhalten, die wir für jede Unterkategorie haben, einschließlich einer Zwischensumme der Produkte für jede Kategorie und Familie. Schauen wir uns die Abfrage an:

SELECT family,
 category,
 subcategory,
 SUM(quantity_available) as quantity_in_stock 
FROM stock
GROUP BY ROLLUP(family, category, subcategory)
ORDER BY family, category, subcategory

Die Klausel ROLLUP geht von einer Hierarchie zwischen den Spalten family, category und subcategory aus. Daher erzeugt sie alle Gruppierungssätze, die unter Berücksichtigung der Hierarchie sinnvoll sind: GROUP BY family, GROUP BY family, category und GROUP BY family, category, subcategory. Dies ist der Grund, warum ROLLUP häufig verwendet wird, um Zwischensummen und Gesamtsummen für Berichte zu erzeugen.

Schauen wir uns die Ergebnisse unten an:

Sie können einige NULLs in dem vorherigen Ergebnis sehen. Jeder NULL-Wert bedeutet, dass diese Spalte in der GROUP BY für die Berechnung der Bestandsmenge nicht vorhanden war. Zum Beispiel sind diese Zeilen ...

... sind das Ergebnis der Ausführung von GROUP BY family, category. Dies ist der Grund dafür, dass unter der Spalte subcategory eine NULL steht.

In der nächsten Abfrage werden wir die Funktion COALESCE() verwenden, um die Lesbarkeit des Berichts zu verbessern. Wir werden diese NULL-Werte durch einen Text ersetzen, der den Grund für die NULL verdeutlicht:

SELECT COALESCE(family,'All Families') AS family,
 COALESCE(category,'All Categories') AS category,
 COALESCE(subcategory,'All Subcategories') AS subcategory,
 SUM(quantity_available) as quantity_in_stock 
FROM stock
GROUP BY ROLLUP(family, category, subcategory)
ORDER BY family, category, subcategory

Das Ergebnis ist:

Im vorherigen Ergebnis können wir beobachten, wie die NULL-Werte durch Texte ersetzt werden und die Zeilen der Berichte mit den Texten, die mit 'All' beginnen, eine Zwischensumme in quantity_in_stock anzeigen.

Ich möchte Ihnen den Artikel Die SQL-Funktion COALESCE empfehlen : Handling NULL Values, wenn Sie mehr darüber erfahren möchten.

Nutzen Sie COALESCE() zur Verarbeitung von NULL-Werten

In diesem Artikel haben wir mehrere Möglichkeiten zur Verwendung der SQL-Funktion COALESCE() gezeigt. Wir haben unter anderem gezeigt, wie Sie COALESCE() verwenden, um NULL-Werte zu ersetzen, wie Sie einen alternativen Wert berechnen und wie Sie COALESCE() mit der ROLLUP-Klausel kombinieren.

Bevor ich zum Schluss komme, habe ich noch eine Empfehlung für Sie. Mit unserem kostenlosen Standard-SQL-Funktionen Cheat Sheet können Sie schnell Details über die eingebauten SQL-Funktionen, Aggregatfunktionen und mehr finden. Ich benutze es fast jeden Tag, wenn ich mit SQL arbeite. Sie können auch unseren Standard-SQL-Funktionen Kurs ausprobieren, in dem Sie numerische, Text-, Datums- und NULL-Funktionen von SQL lernen und üben können. Verbessern Sie Ihre Fähigkeiten und steigern Sie Ihr Vermögen!