Was tut COALESCE?
Angenommen ihr habt in eurer Datenbank mehrere Spalten einer Tabelle, die alle NULL-Werte enthalten dürfen. Nun möchtet ihr den Wert der ersten Spalte, wenn dieser aber nicht vorhanden ist, dann den Wert der zweiten Spalte, wenn dieser nicht vorhanden ist, dann denn Wert der dritten Spalte und so weiter… Es gibt nun immer wieder Stimmen, die behaupten, dass COALESCE eigentlich wie ISNULL ist, nur eben anders als ISNULL dem ANSI-Standard entspricht. Doch es gibt noch weitere Unterschiede zwischen beiden Funktionen, die ihr heute kennenlernen sollt.
Wie unterscheidet sich COALESCE von ISNULL?
Der Hauptunterschied zwischen COALESCE und ISNULL ist, dass ihr bei ISNULL den Rückgabewert schon von vorneherein kennt, er ist nämlich der Datentyp der Spalte, die ihr als erstes Argument übergebt. Sehen wir uns das im Beispiel an. Dafür erzeugen wir zunächst eine Tabelle mit einer Spalte in die wir einen NULL-Wert einfügen:
DROP TABLE IF EXISTS #Ninja
CREATE TABLE #Ninja
(
NinjaHandle varchar(15) null
)
INSERT INTO #Ninja (NinjaHandle)
VALUES (NULL)
Nun arbeiten wir mit dieser NULL-Zeile. Ersetzen wir den NULL-Wert zunächst mittels ISNULL:
SELECT
ISNULL(NinjaHandle, 'T-SQL Ninja')
FROM #Ninja
Wie nicht anders zu erwarten steht in der Ergebnismenge der Wert “T-SQL Ninja”. Doch nun modifizieren wir die Abfrage leicht:
SELECT
ISNULL(NinjaHandle, 'Brilliantly querying T-SQL Ninja')
FROM #Ninja
Nun erhalten wir zu unserer Überraschung nur noch den Wert “Brilliantly que”. Warum ist das so? Weil ISNULL den einzufügenden Wert als Wert von NinjaHandle, also als varchar der Länge 15 zurückgibt. Ist unser String nun länger als 15 Zeichen, so schneidet ISNULL ab und gibt uns nur die ersten 15 Zeichen zurück.
Anders ist das, wenn wir COALESCE verwenden. Auch hier erhalten wir bei der ersten Abfrage den Wert “T-SQL Ninja” zurück:
SELECT
COALESCE(NinjaHandle, 'T-SQL Ninja')
FROM #Ninja
Anders ist das bei der zweiten Abfrage:
SELECT
COALESCE(NinjaHandle, 'Brilliantly querying T-SQL Ninja')
FROM #Ninja
Hier bekommen wir die vollständige Zeichenkette zurückgeliefert, unabhängig vom Typ von NinjaHandle. Das heißt, während ISNULL uns eine Typensicherheit garantiert, kann COALESCE das nur bedingt leisten. Das bedeutet, wenn eure Anwendung auf eine Typensicherheit in der Rückgabe angewiesen ist, dann seid ihr mit ISNULL immer auf der sicheren Seite, mit COALESCE kann euch hingegen auch eine böse Überraschung blühen.
Sehen wir uns ein weiteres Beispiel an, das dieses Problem verdeutlicht. Dafür modifizieren wir zuerst die Ninja-Tabelle und fügen eine weitere Spalte hinzu:
ALTER TABLE #Ninja ADD NinjaSkillLevel INT NULL
Nun möchten wir mit dieser Spalte rechnen und sie als Modifikator eines Wertes verwenden, dafür teilen wir den Wert durch 2 wenn kein NinjaSkillLevel vorhanden ist, sonst durch das Skill-Level. In SQL gegossen sehen die beiden Varianten, die wir haben um das zu tun wie folgt aus:
SELECT
5/ISNULL(NinjaSkillLevel, 2.00)
,5/COALESCE(NinjaSkillLevel, 2.00)
FROM #Ninja
Was nun geschieht ist, dass durch die Typensicherheit von ISNULL, die 2.00 als Integer-Wert 2 zurückgegeben wird, was zur Folge hat, dass die 5 durch die ganzzahlige 2 geteilt wird, hier liefert der SQL Server dann den ganzzahligen Wert zurück, also erhalten wir als Rückgabewert die 2. Im Fall von COALESCE hat die 2.00, die der Funktion übergeben wird aber eine höhere Genauigkeit als die Spalte NinjaSkillLevel, hier entscheidet sich der SQL Server also, den NinjaSkillLevel auf den Datentyp von 2.00 zu konvertieren, also eine implizite Konvertierung vorzunehmen. Dadurch teilen wir 5 durch eine Gleitkommazahl, was in einer Gleitkommazahl resultiert, wir erhalten hier also den korrekten Wert von 2.5 (mit einigen nachgestellten Nullen). Und tatsächlich finden wir auch im Ausführungsplan eine implizite Typenumwandlung:
Hier sehen wir zweierlei: einerseits, dass COALESCE intern in CASE-Statements umgewandelt wird, andererseits, dass der SQL Server implizit zum Datentypen numeric(12,2) umwandelt.
Doch neben der Gefahr eines unerwarteten Rückgabetypen hat die COALESCE-Funktion natürlich auch Vorteile. Einer der Vorteile ist, dass man ihr auch mehrere Spalten übergeben kann, so lange diese Typenkompatibel sind. Ein weiterer Vorteil ist, dass es auch möglich ist, wenn alle Spalten NULL-Werte enthalten, einen NULL-Wert zurück zu geben. Ein Seiteneffekt dieser Tatsache ist, dass wenn ihr eine abgeleitete Spalte mit ISNULL definiert, diese Spalte als Primärschlüssel verwendet werden kann, da ISNULL nicht NULL sein kann und der Rückgabewert für den SQL Server als Primärschlüssel in Frage kommt. Anders ist das bei COALESCE, der Rückgabewert hier kann theoretisch NULL sein, auch wenn ihr das mit einem konstanten Wert als letztem Argument faktisch unterbinden könnt. Deshalb schlägt der Versuch fehl, einen Primärschlüssel auf einer mit COALESCE abgeleiteten Spalte zu definieren.
Ihr solltet aus dem heutigen Dojo nicht die Nachricht mitnehmen, dass COALESCE nicht typensicher und damit böse ist. Vielmehr solltet ihr euch im Klaren darüber sein, dass hier unerwartete Datentypen zurückgegeben werden können und gegebenenfalls in euren Anwendungen darauf reagieren. Besser als die Funktion zu vermeiden ist es, die Funktion zu verstehen und sich ihre Eigenheiten gegebenenfalls zunutze zu machen.
