Artikel
09/24/2010

COALESCE (Transact-SQL)

Gibt den ersten Ausdruck ungleich NULL an die Argumente des Ausdrucks zurück.

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.

Hinweis
Mindestens einer der NULL-Werte muss ein typisierter NULL-Wert sein.

COALESCE(expression1,...n) ist äquivalent zum folgenden CASE-Ausdruck:

CASE

WHEN (expression1 IS NOT NULL) THEN expression1

WHEN (expression2 IS NOT NULL) THEN expression2

...

ELSE expressionN

END

ISNULL und COALESCE entsprechen zwar einander, sie können jedoch ein unterschiedliches Verhalten aufweisen. Ein Ausdruck unter Beteiligung von ISNULL mit Parametern ungleich NULL wird als NOT NULL behandelt, während Ausdrücke unter Beteiligung von COALESCE mit Parametern ungleich NULL als NULL behandelt werden. In SQL Server kann zum Indizieren von Ausdrücken unter Beteiligung von COALESCE mit Parametern ungleich NULL die berechnete Spalte wie in der folgenden Anweisung mithilfe des PERSISTED-Spaltenattributs beibehalten werden:

CREATE TABLE #CheckSumTest 
    (
        ID int identity ,
        Num int DEFAULT ( RAND() * 100 ) ,
        RowCheckSum AS COALESCE( CHECKSUM( id , num ) , 0 ) PERSISTED PRIMARY KEY
    );

Beispiele

Einfaches Beispiel

Im folgenden Beispiel wird veranschaulicht, wie COALESCE die Daten aus der ersten Spalte auswählt, die einen Wert ungleich NULL aufweist.

USE AdventureWorks ;

SELECT Name, Class, Color, ProductNumber,

COALESCE(Class, Color, ProductNumber) AS FirstNotNull

FROM Production.Product ;

Komplexes Beispiel

Im folgenden Beispiel enthält die wages-Tabelle drei Spalten mit Informationen zu den Jahresgehältern der Angestellten: den Stundenlohn, das Gehalt und die Kommission. 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.

Summe des Gehalts

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

20800.0000

41600.0000

62400.0000

83200.0000

10000.0000

20000.0000

30000.0000

40000.0000

45000.0000

50000.0000

120000.0000

56000.0000

(12 Zeile(n) betroffen)

Siehe auch

Verweis

ISNULL (Transact-SQL)

CASE (Transact-SQL)

Freigeben über