COALESCE (Transact-SQL)

Artikel
03/11/2014

Wertet die Argumente in der vorliegenden Reihenfolge aus und gibt den aktuellen Wert des ersten Ausdrucks zurück, der anfangs nicht NULL ergibt.

Gilt für: SQL Server (SQL Server 2008 bis aktuelle Version), Windows Azure SQL-Datenbank (Ursprüngliche Version bis aktuelle Version).

Themenlink (Symbol) Transact-SQL-Syntaxkonventionen

Syntax

COALESCE ( expression [ ,...n ] )

Argumente

expression
Ein Ausdruck eines beliebigen Typs.

Rückgabetypen

Gibt den Datentyp von expression zurück, der in der Datentyp-Rangfolge am höchsten steht. Falls für alle Ausdrücke NULL nicht zulässig ist, wird das Ergebnis entsprechend eingegeben.

Hinweise

Sind alle Argumente NULL, gibt COALESCE NULL zurück. Mindestens einer der NULL-Werte muss ein typisierter NULL-Wert sein.

Vergleich zwischen COALESCE und CASE

Der COALESCE-Ausdruck ist eine syntaktische Kurzform für den CASE-Ausdruck. Dies bedeutet, dass der Code COALESCE(expression1,...n) vom Abfrageoptimierer in den folgenden CASE-Ausdruck umgeschrieben wird:

CASE

WHEN (expression1 IS NOT NULL) THEN expression1

WHEN (expression2 IS NOT NULL) THEN expression2

...

ELSE expressionN

END

Das heißt, dass die Eingabewerte (expression1, expression2, expressionN usw.) mehrmals ausgewertet werden. Außerdem wird ein Wertausdruck, der eine Unterabfrage enthält, gemäß dem SQL-Standard als nicht deterministisch angesehen und die Unterabfrage zweimal ausgewertet. In beiden Fällen können zwischen der ersten Auswertung und nachfolgenden Auswertungen unterschiedliche Ergebnisse zurückgegeben werden.

Beispiel: Wenn der Code COALESCE((subquery), 1) ausgeführt wird, wird die Unterabfrage zweimal ausgewertet. Folglich können Sie abhängig von der Isolationsstufe der Abfrage unterschiedliche Ergebnisse erhalten. Beispielsweise kann der Code auf der READ COMMITTED-Isolationsstufe in einer Mehrbenutzerumgebung NULL zurückgeben. Um sicherzustellen, dass beständige Ergebnisse zurückgegeben werden, verwenden Sie die SNAPSHOT ISOLATION-Isolationsstufe oder ersetzen COALESCE durch die ISNULL-Funktion. Alternativ können Sie die Abfrage umschreiben, um die Unterabfrage in eine untergeordnete SELECT-Anweisung zu verschieben, wie im folgenden Beispiel gezeigt.

SELECT CASE WHEN x IS NOT NULL THEN x ELSE 1 END
from
(
SELECT (SELECT Nullable FROM Demo WHERE SomeCol = 1) AS x
) AS T;

Vergleich zwischen COALESCE und ISNULL

Die ISNULL-Funktion und der COALESCE-Ausdruck dienen einem ähnlichen Zweck, können jedoch ein unterschiedliches Verhalten aufweisen.

Da ISNULL eine Funktion ist, wird sie nur einmal ausgewertet. Wie oben beschrieben, können die Eingabewerte für den COALESCE-Ausdruck mehrmals ausgewertet werden.
Die Datentypen des resultierenden Ausdrucks werden auf unterschiedliche Weise bestimmt. ISNULL verwendet den Datentyp des ersten Parameters, während bei COALESCE die Regeln des CASE-Ausdrucks befolgt werden und der Datentyp des Werts mit der höchsten Rangfolge zurückgegeben wird.
Die NULL-Zulässigkeit des Ergebnisausdrucks ist bei ISNULL und COALESCE unterschiedlich. Beim ISNULL-Rückgabewert wird (in der Annahme, dass er keine NULL-Werte zulässt) immer davon ausgegangen, dass er NOT NULL ist, während COALESCE mit Parametern ungleich NULL als NULL betrachtet wird. Daher weisen der ISNULL(NULL, 1)-Ausdruck und der COALESCE(NULL, 1)-Ausdruck unterschiedliche Werte für die NULL-Zulässigkeit auf, obwohl sie äquivalent sind. Dieser Unterschied macht sich bemerkbar, wenn Sie die Ausdrücke in berechneten Spalten verwenden, Schlüsseleinschränkungen erstellen oder den Rückgabewert einer Skalar-UDF als deterministisch festlegen, sodass er indiziert werden kann, wie im folgenden Beispiel veranschaulicht.
```
USE tempdb;
GO
-- This statement fails because the PRIMARY KEY cannot accept NULL values
-- and the nullability of the COALESCE expression for col2 
-- evaluates to NULL.
CREATE TABLE #Demo 
( 
col1 integer NULL, 
col2 AS COALESCE(col1, 0) PRIMARY KEY, 
col3 AS ISNULL(col1, 0) 
); 

-- This statement succeeds because the nullability of the 
-- ISNULL function evaluates AS NOT NULL.

CREATE TABLE #Demo 
( 
col1 integer NULL, 
col2 AS COALESCE(col1, 0), 
col3 AS ISNULL(col1, 0) PRIMARY KEY 
);
```
Auch Überprüfungen für ISNULL und COALESCE sind unterschiedlich. Beispielsweise wird ein NULL-Wert für ISNULL in int konvertiert, während Sie für COALESCE einen Datentyp angeben müssen.
ISNULL akzeptiert nur zwei Parameter, während für COALESCE eine beliebige Anzahl von Parametern verwendet werden kann.

Beispiele

A.Ausführen eines einfachen Beispiels

Im folgenden Beispiel wird veranschaulicht, wie COALESCE die Daten aus der ersten Spalte auswählt, die einen Wert ungleich NULL aufweist. In diesem Beispiel wird die AdventureWorks2012-Datenbank verwendet.

SELECT Name, Class, Color, ProductNumber,
COALESCE(Class, Color, ProductNumber) AS FirstNotNull
FROM Production.Product;

B.Ausführen eines komplexen Beispiels

Im folgenden Beispiel enthält die wages-Tabelle drei Spalten mit Informationen zu den Jahresgehältern der Angestellten: den Stundensatz, das Gehalt und die Provision. Allerdings wird ein Angestellter nur nach einem dieser Gehaltstypen bezahlt. Um die Gesamtsumme aller Auszahlungen an die Angestellten zu bestimmen, verwenden Sie COALESCE, damit Sie nur die Werte ungleich NULL in den Spalten hourly_wage, salary und commission erhalten.

SET NOCOUNT ON;
GO
USE tempdb;
IF OBJECT_ID('dbo.wages') IS NOT NULL
    DROP TABLE wages;
GO
CREATE TABLE dbo.wages
(
    emp_id        tinyint   identity,
    hourly_wage   decimal   NULL,
    salary        decimal   NULL,
    commission    decimal   NULL,
    num_sales     tinyint   NULL
);
GO
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)
VALUES
    (10.00, NULL, NULL, NULL),
    (20.00, NULL, NULL, NULL),
    (30.00, NULL, NULL, NULL),
    (40.00, NULL, NULL, NULL),
    (NULL, 10000.00, NULL, NULL),
    (NULL, 20000.00, NULL, NULL),
    (NULL, 30000.00, NULL, NULL),
    (NULL, 40000.00, NULL, NULL),
    (NULL, NULL, 15000, 3),
    (NULL, NULL, 25000, 2),
    (NULL, NULL, 20000, 6),
    (NULL, NULL, 14000, 4);
GO
SET NOCOUNT OFF;
GO
SELECT CAST(COALESCE(hourly_wage * 40 * 52, 
   salary, 
   commission * num_sales) AS money) AS 'Total Salary' 
FROM dbo.wages
ORDER BY 'Total Salary';
GO

Dies ist das Resultset.

Total Salary

------------

10000.00

20000.00

20800.00

30000.00

40000.00

41600.00

45000.00

50000.00

56000.00

62400.00

83200.00

120000.00

(12 row(s) affected)

Siehe auch

Verweis

ISNULL (Transact-SQL)

CASE (Transact-SQL)

Freigeben über