COALESCE (Transact-SQL)

Artikel
10/31/2024

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL Analytics-Endpunkt in Microsoft Fabric Warehouse in Microsoft Fabric

Wertet die Argumente in der vorliegenden Reihenfolge aus und gibt den aktuellen Wert des ersten Ausdrucks zurück, der anfangs nicht NULL ergibt. SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); gibt beispielsweise den dritten Wert zurück, weil der dritte der erste Wert ist, der nicht NULL ist.

Transact-SQL-Syntaxkonventionen

Syntax

COALESCE ( expression [ ,...n ] )

Argumente

expression
Ein Ausdruck beliebigen Typs.

Rückgabetypen

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

Bemerkungen

COALESCE gibt NULL zurück, wenn alle Argumente NULL sind. 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

Daher werden die Eingabewerte (expression1, expression2, expressionN usw.) mehrmals ausgewertet. Außerdem wird ein Wertausdruck, der eine Unterabfrage enthält, als nicht deterministisch angesehen, und die Unterabfrage wird zweimal ausgewertet. Dieses Ergebnis entspricht dem SQL-Standard. In beiden Fällen können zwischen der ersten Auswertung und kommenden 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 neu schreiben, 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. Die Eingabewerte können wie oben beschrieben 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. Im Gegensatz dazu wird COALESCE mit Parametern ungleich NULL als NULL betrachtet. Daher weisen der ISNULL(NULL, 1)-Ausdruck und der COALESCE(NULL, 1)-Ausdruck unterschiedliche Werte für die NULL-Zulässigkeit auf, obwohl sie gleich sind. Diese Werte verhalten sich unterschiedlich, wenn Sie die Ausdrücke in berechneten Spalten verwenden, Schlüsseleinschränkungen erstellen oder den Rückgabewert einer Skalar-UDF als deterministisch festlegen, sodass die Indizierung wie im folgenden Beispiel erfolgen kann:
```
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   
);  
```
Überprüfungen für ISNULL und COALESCE sind ebenfalls unterschiedlich. Beispielsweise wird ein NULL-Wert für ISNULL in int konvertiert, während Sie für COALESCE einen Datentyp angeben müssen.
ISNULL verwendet nur zwei Parameter. Im Gegensatz dazu ist bei COALESCE die Parameteranzahl variabel.

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 AdventureWorks2022-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

Hier sehen Sie das Ergebnis.

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)

C: Einfaches Beispiel

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 angenommen, dass die Products-Tabelle die folgenden Daten enthält:

Name         Color      ProductNumber  
------------ ---------- -------------  
Socks, Mens  NULL       PN1278  
Socks, Mens  Blue       PN1965  
NULL         White      PN9876

Wir führen anschließend die folgende COALESCE-Abfrage aus:

SELECT Name, Color, ProductNumber, COALESCE(Color, ProductNumber) AS FirstNotNull   
FROM Products ;

Hier sehen Sie das Ergebnis.

Name         Color      ProductNumber  FirstNotNull  
------------ ---------- -------------  ------------  
Socks, Mens  NULL       PN1278         PN1278  
Socks, Mens  Blue       PN1965         Blue  
NULL         White      PN9876         White

Beachten Sie, dass der FirstNotNull-Wert in der ersten Zeile PN1278, nicht Socks, Mens ist. Grund hierfür ist, dass die Name-Spalte im Beispiel nicht als Parameter für COALESCE angegeben wurde.

D: Komplexes Beispiel

Das folgende Beispiel verwendet COALESCE, um die Werte in drei Spalten zu vergleichen und nur die Werte ungleich NULL zurückzugeben, die in den Spalten gefunden wurden.

CREATE TABLE dbo.wages  
(  
    emp_id        TINYINT   NULL,  
    hourly_wage   DECIMAL   NULL,  
    salary        DECIMAL   NULL,  
    commission    DECIMAL   NULL,  
    num_sales     TINYINT   NULL  
);  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (1, 10.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (2, 20.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (3, 30.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (4, 40.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (5, NULL, 10000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (6, NULL, 20000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (7, NULL, 30000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (8, NULL, 40000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (9, NULL, NULL, 15000, 3);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (10,NULL, NULL, 25000, 2);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (11, NULL, NULL, 20000, 6);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (12, NULL, NULL, 14000, 4);  
  
SELECT CAST(COALESCE(hourly_wage * 40 * 52,   
   salary,   
   commission * num_sales) AS DECIMAL(10,2)) AS TotalSalary   
FROM dbo.wages  
ORDER BY TotalSalary;

Hier sehen Sie das Ergebnis.

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

Weitere Informationen

ISNULL (Transact-SQL)
CASE (Transact-SQL)

Teilen über

COALESCE (Transact-SQL)

Syntax

Argumente

Rückgabetypen

Bemerkungen

Vergleich zwischen COALESCE und CASE

Vergleich zwischen COALESCE und ISNULL

Beispiele

A. Ausführen eines einfachen Beispiels

B. Ausführen eines komplexen Beispiels

C: Einfaches Beispiel

D: Komplexes Beispiel

Weitere Informationen

Feedback

Zusätzliche Ressourcen