Es gibt keine Unterschiede zwischen ISNULL und COALESCE. Diese Meinung kann man recht häufig in Online Communities lesen. Der einzige Unterschied zwischen beiden ist, daß ISNULL SQL Server spezifisch ist, während COALESCE ANSI-SQL Standard ist. Auch dies kann man recht häufig lesen. Beide beschäftigen sich mit der Umwandlung von NULL und damit fehlenden Informationen. Wozu also einen eigenen Beitrag?

Nun, genau deshalb. Und es gibt sogar recht gewichtige Unterschiede zwischen beiden Funktionen. Zunächst einmal ein bißchen Theorie.

In BOL steht zu ISNULL:

Syntax

ISNULL ( check_expression , replacement_value )

Argumente

check_expression

Der Ausdruck, der auf NULL überprüft wird. check_expression kann einen beliebigen Datentyp haben.

replacement_value

Der Ausdruck, der zurückgegeben wird, wenn check_expression NULL ist. replacement_value muss denselben Datentyp besitzen wie check_expresssion.

Rückgabetypen

Gibt denselben Datentyp zurück wie check_expression.

Hinweise

Ist check_expression nicht NULL, wird der Wert von check_expression zurückgegeben. Andernfalls wird replacement_value zurückgegeben.

Nachfolgendes findet man in BOL zu COALESCE:

COALESCE

Gibt den ersten Ausdruck ohne NULL-Wert an die Argumente des Ausdrucks zurück

Syntax

COALESCE ( expression [ ,...n ] )

Argumente

expression

Ein Ausdruck beliebigen Typs.

n

Ein Platzhalter, der anzeigt, dass mehrere Ausdrücke angegeben werden können. Alle Ausdrücke müssen vom gleichen Typ sein oder sich implizit in den gleichen Typ umwandeln lassen.

Rückgabetypen

Gibt einen Wert zurück, dessen Typ mit dem von expression übereinstimmt.

Hinweise

Sind alle Argumente NULL, gibt COALESCE NULL zurück.

COALESCE(expression1,...n) entspricht der folgenden CASE-Funktion:

CASE
   WHEN (expression1 IS NOT NULL) THEN expression1
   ...
   WHEN (expressionN IS NOT NULL) THEN expressionN
   ELSE NULL

So, jetzt aber in die Details: Unsere Ausgangssituation

CREATE TABLE #t
(
 c1 CHAR
)
INSERT INTO #t VALUES (NULL)

Unsere Basisabfrage

SELECT
 ISNULL(c1,'Frank')
 , COALESCE(c1,'Frank') FROM #t

Das Ergebnis:

---- -----
F    Frank

(1 row(s) affected)

Aha, hier läuft doch irgendetwas schief, oder?

Jein, ISNULL macht genau das, was in BOL steht. Allerdings muß man etwas "verdreht" denken. BOL beschreibt, daß ISNULL NULL durch den angegebenen Ersatzwert ersetzt. Und BOL beschreibt auch, daß derselbe Datentyp wie check_expression zurückgegeben wird. Wenn man sich jetzt vor Augen hält, daß der Datentyp CHAR der Spalte c1 äquivalent zu CHAR(1) ist, erscheint das Ergebnis von ISNULL absolut plausibel. Der Rest, der da dranhängt, wird einfach abgeschnitten. Dabei spielt es übrigens auch keine Rolle, ob - wie in unserem Beispiel -  die Zeichenfolge 'Frank' angegeben wird oder eine andere Spalte der Tabelle. Diese würde ebenfalls auf 1 Stelle gekürzt.

Wie aber verhält es sich mit COALESCE? Ebenfalls absolut stimmig mit BOL, oder? Dort steht halt nur, daß ein Wert zurückgegeben wird, dessen Typ mit dem von expression übereinstimmt. So, zweifelsohne ist "Frank" genauso eine Zeichenfolge wie "F", nur halt länger, aber BOL hat ja auch nicht erwähnt, daß derselbe Datentyp zurückgegeben, oder? ;-) Übrigens ergibt:

SELECT
 ISNULL(c1,'Frank')
 , COALESCE(c1,'Frank')
 ,CASE WHEN c1 IS NULL THEN 'Frank' ELSE c1 END
FROM
 #t

---- ----- -----
F    Frank Frank

(1 row(s) affected)

Also ist zumindest dies konsistent gehandhabt. Aber wie verhält es sich nun mit nummerischen Ausdrücken?

SELECT
 7 / ISNULL(CAST(NULL AS INT), 2.00)
 , 7 / COALESCE(CAST(NULL AS INT), 2.00)

----------- ------------------
3           3.5000000000000

(1 row(s) affected)

Hier sieht es etwas anders aus. ISNULL erhält INT als Inputparameter und gibt auch einen INT Wert zurück. Die Division 7/2 ergibt 3. Okay, läßt sich nachvollziehen.

Anders hingegen bei COALESCE. Auch hier kommt INT rein, aber herauskommt FLOAT oder DECIMAL. Da beide Datentypen eine höhere Präzedenz haben als INT, scheint SQL Server eine implizite Konvertierung vorzunehmen und auf den Rückgabewert von COALESCE anzuwenden. Meiner Meinung nach kommen wir aber nun in Schwierigkeiten mit der Definition, die in BOL zu lesen ist. Hier wird definitiv kein Typ zurückgegeben, der mit dem von expression übereinstimmt! Weiterhin interessant ist, wenn man das vorherige Statement um die CASE Struktur erweitert, wie ebenfalls in BOL gezeigt.

SELECT
 7 / ISNULL(CAST(NULL AS int), 2.00)
 , 7 / COALESCE(CAST(NULL AS int), 2.00)
 , 7 / CASE WHEN CAST(NULL AS int) IS NULL THEN 2.00 END

----------- ------------------ -----------
3           3.5000000000000    3.500000

(1 row(s) affected)

Offensichtlich arbeitet CASE dann auch noch wieder etwas anders als COALESCE. An dieser Stelle habe ich beschlossen, nicht weiter darüber nachzudenken und das Ganze als "by design" hinzunehmen. Entweder stimmt die Implementierung in SQL Server nicht, oder die Dokumentation. Wobei ich dazu tendiere, es auf die Dokumentation zu schieben.

Als ich aber auf der Suche nach einer Erklärung auf ein Posting von Umachandar Jayachandran in der MS Newsgroup aus 2002 gestoßen bin, habe ich beschloßen, COALESCE nicht zu verwenden, solange ich nicht die speziellen erweiterten Möglichkeiten von COALESCE brauche.

Umachandar schlug vor, einmal die Ausführungspläne folgender Statements miteinander zu vergleichen:

select
 coalesce((
  select
   a2.au_id
  from
   pubs..authors a2
  where
   a2.au_id = a1.au_id ),'')
from
 pubs..authors a1

select
 isnull((
  select
   a2.au_id
  from
   pubs..authors a2
  where
   a2.au_id = a1.au_id ),'')
from
 pubs..authors a1

Gesagt, getan. Heraus kommt folgendes:

Und im zweiten Beispiel:

So, und nun kann man es drehen und wenden, wie man will. Entweder ist ISNULL besser optimiert oder der Optimierer erkennt nicht, daß es sich "nur" um ein Argument bei COALESCE handelt. Und solange mir keiner plausibel erklärt, warum COALESCE unter allen Umständen ISNULL vorzuziehen sei (Portabilität mal außer Acht gelassen), werde ich COALESCE nicht verwenden.