Einsatz von ISNULL und COALESCE

Die ISNULL- und COALESCE-Funktion stellen verkürzte Abfrageformen dar, wenn es darum geht mehrere Attribute oder Variablen hinsichtlich möglicher NULL-Werte zu analysieren.

Sollen beispielsweise mit T-SQL mehrere Attribut-Werte selektiert und der erste Nicht-NULL-Wert zurückgegeben werden, bietet sich, sofern keine Kenntnisse von ISNULL oder COALESCE vorhanden sind, ein CASE-Expression folgender Form an:

   1: SELECT 

   2:     CASE

   3:         WHEN Value1 IS NOT NULL THEN Value1

   4:         WHEN Value2 IS NOT NULL THEN Value2

   5:         [...]

   6:         WHEN ValueN IS NOT NULL THEN ValueN

   7:         ELSE NULL

   8:     END;

Der Nachteil bei dieser Abfrage mit CASE-Expression ist offensichtlich. Sie erscheint weniger SQL-affinen Entwicklern sehr komplex und kann, je nach Anzahl der möglichen Werte, sehr lang und unübersichtlich werden.

Deutlich kürzer und lesbarer kann ein solches Statement werden, wenn die COALESCE-Funktion zum Einsatz kommt:

   1: SELECT COLESCE(Value1, Value2, [...], ValueN);

Kommen gar nur zwei Werte als mögliche Nicht-NULL-Werte in Frage, bietet sich auch die ISNULL-Funktion in folgendem Format an:

   1: SELECT ISNULL(Value1, Value2);

Das Resultat, wenn die beiden letzten SELECT-Statements ausgeführt werden, ist in beiden Fällen gleich: Sowohl das Ergebnis der COALESCE- als auch das der ISNULL-Funktion liefert den ersten Attribut-/Variablen-Wert zurück, welches keinen NULL-Wert besitzt.

Unterschiede zwischen ISNULL und COALESCE

Es hat den Anschein, dass sich die ISNULL-und COALESCE-Funktion für sehr ähnliche Anwendungsszenarien eignen. Im Folgenden werden nun die wesentlichen Unterschiede zwischen diesen beiden Funktionen dargelegt:

1. Standardisierung/Normierung

Auch wenn der erste Unterschied nur wie eine kleine Randnotiz erscheint, so sollte es doch zumindest kurz erwähnt werden: Die COALESCE-Funktion ist fixer Bestandteil des vom American National Standards Institute definiert und genormten ANSI SQLs. Sie kann somit eventuell also auch bei anderen SQL-Dialekten oder Datenbank-Providern mit SQL-basierten Abfragesprachen vorgefunden werden. Die ISNULL-Funktion steht in der in diesem Artikel beschriebene Variante dagegen nur explizit beim Einsatz von T-SQL zur Verfügung.

2. Anzahl der Parameter

Während die ISNULL-Funktion genau zwei Parameter erwartet, können an die COALESCE-Funktion mehr als zwei Funktionsargumente übergeben werden. Der folgende Code-Ausschnitt zeigt gültige Aufrufe für die beiden Funktionen, wobei der COALESCE-Funktion natürlich noch weitere Parameter als Funktionsargumente übergeben werden können:

   1: DECLARE @x1 VARCHAR(1) = 'a';

   2: DECLARE @x2 VARCHAR(1) = 'b';

   3: DECLARE @x3 VARCHAR(1) = 'c';

   4: DECLARE @x4 VARCHAR(1) = 'd';

   5: SELECT COALESCE(@x1, @x2, @x3, @x4) [COALESCE], ISNULL(@x1, @x2) [ISNULL];

3. Ergebnis-Typ

Werden unterschiedliche Datentypen an die Funktionen übergeben, so zeigt sich ein sehr signifikanter Unterschied. COALESCE betrachtet die Datentypen aller übergebenen Parameter und liefert den Typ mit der höchsten Präzedenz bzw. Rangfolge zurück. Die ISNULL-Funktion dagegen verwendet den Typen des ersten Parameters als finalen Ergebnistyp. Der folgende Code-Ausschnitt stellt dies exemplarisch dar.

   1: DECLARE @x VARCHAR(3) = NULL;   

   2: DECLARE @y NVARCHAR(10) = '1234567890';

   3: SELECT COALESCE(@x, @y) AS [COALESCE], ISNULL(@x, @y) AS [ISNULL];

Die COALESCE-Funktion liefert den kompletten Inhalt der @y-Variablen zurück. Dieser besitzt eine höhere Datentyp-Rangfolge gegenüber der @x-Variablen. Bei der ISNULL-Funktion wird der Ergebnistyp bereits durch den ersten Parameter (hier: @x-Variable) auf VARCHAR(3) festgelegt. Der zweite Parameter wird dann in den Datentypen des ersten Parameters umgewandelt. Die folgende Abbildung zeigt das Ergebnis der SQL-Statements des vorranggegangene Code-Ausschnitts: 

Hierbei ist also durchaus Vorsicht angebracht, damit nicht aus Versehen Daten formatiert und wichtige Informationen verändert werden.

4. NULL-Parameter Validierung

Die COALESCE-Funktion benötigt mindestens einen Parameter, welcher keine NULL-Konstante ist. Ansonsten kann der SQL-Befehl nicht erfolgreich ausgeführt werden. Somit stellt folgender Code-Ausschnitt ein ungültiges SQL-Statement dar:

   1: SELECT COALESCE(NULL, NULL) [COALESCE(NULL, NULL)];

Nach Absetzen dieses Statements wird im SQL-Server Management Studio folgende Fehlermeldung zurückgeliefert:

Msg 4127, Level 16, State 1 
At least one of the arguments to COALESCE must be an expression that is not NULL constant.

Die ISNULL-Funktion kann dagegen ausschließlich mit NULL-Konstanten aufgerufen werden. Folgendes SQL-Statement ist beispielsweise valide:

   1: SELECT ISNULL(NULL, NULL) [ISNULL(NULL, NULL)];

Das Ergebnis ist, wie zu erwarten, NULL:

Das Verhalten lässt sich durch die unterschiedliche Typ-Validierung der NULL-Parameter-Validierung durch die beiden Funktionen erklären. Während die ISNULL-Funktion die NULL-Werte in die Datentypen INT bzw. INTEGER konvertiert, verwendet die COALESCE-Funktion die Datentypen der übergebene Parameter. Dieses Problem kann umgangen werden, indem, wie im folgenden Code-Ausschnitt dargestellt, ein NULL-Parameter auf den INT-Datentypen abgebildet wird:

   1: SELECT COALESCE(NULL, CAST (NULL AS INT)) [COALESCE_WITH_CAST];

Das Ergebnis des SQL-Statements ist dann wie bei der vorherigen Abfrage NULL:

5. Ausführungspläne

Als letzter interessantester Unterschied zwischen diesen beiden Funktionen sollen noch die unterschiedlichen Ausführungspläne der ISNULL- und COALESCE-Funktion untersucht werden. Dies kann insbesondere bei größeren Datenmengen durchaus extreme Auswirkungen hinsichtlich der Performance des eingesetzten Datenbank-Servers haben. 

Als Beispiel dient eine einfache Tabellenstruktur mit wie im folgenden Code-Ausschnitt beschrieben:

   1: CREATE TABLE Tbl (TblColumn int);

Anschließend wird folgendes SQL-Statements ausgeführt:

   1: SELECT ISNULL((SELECT MAX(TblColumn) FROM Tbl), 1);

Der Ausführungsplan des eben beschriebenen SQL-SELECT-Befehls liefert folgenden Verlauf:

Wird im SQL-Statements nun die ISNULL-Funktion durch die COALESCE-Funktion ersetzt, so ergibt sich folgendes Code-Snippet:

   1: SELECT COALESCE((SELECT MAX(TblColumn) FROM Tbl), 1);

Letztes SQL-SELECT-Statement ausgeführt liefert schließlich einen Ausführungsplan welcher in der folgenden Grafik abgebildet ist:

Es zeigt sich, dass der Ausführungsplan des SQL-Statements mit der COALESCE-Funktion mehr Stream-Aggregationen, Table-Scans und Join-Operationen beinhaltet. Die unterschiedlichen Ausführungspläne zwischen ISNULL und COALESCE erklären sich daraus, dass COALESCE für den Query-Optimizer opak ist. Das bedeutet, dass der Optimizer das Ergebnis der Funktion nicht vorhersagen kann. Dagegen ist die Anwendung von ISNULL transparent und wird bei der Optimierung mit einbezogen. In der Regel wird der Einsatz von ISNULL daher zu besseren Query-Plänen führen.

Zusammenfassung

Für die Programmiersprache T-SQL stellen diese beiden Funktionen im Zusammenhang mit NULL-Werten sicherlich eine nützliche Unterstützung für Datenbank-Entwickler dar. Dennoch sollte ihr Einsatz immer mit Vorsicht bedacht werden und zielgerichtet erfolgen, denn der Teufel steckt bekanntlich im Detail .

Quellen:

1. http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt (Letzter Zugriff: 21.05.2014)
2. BNF Grammar for ISO/IEC 9075:1992 – Database Language SQL (SQL-92) (Letzter Zugriff: 21.05.2014)
3. Differences between ISNULL and COALESCE (Letzter Zugriff: 21.05.2014)