COALESCE (Transact-SQL)COALESCE (Transact-SQL)

GILT FÜR: JaSQL Server JaAzure SQL-Datenbank JaAzure Synapse Analytics (SQL DW) JaParallel Data Warehouse APPLIES TO: YesSQL Server YesAzure SQL Database YesAzure Synapse Analytics (SQL DW) YesParallel Data Warehouse

Wertet die Argumente in der vorliegenden Reihenfolge aus und gibt den aktuellen Wert des ersten Ausdrucks zurück, der anfangs nicht NULL ergibt.Evaluates the arguments in order and returns the current value of the first expression that initially doesn't evaluate to NULL. 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.For example, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); returns the third value because the third value is the first value that isn't null.

Symbol für Themenlink Transact-SQL-SyntaxkonventionenTopic link icon Transact-SQL Syntax Conventions

SyntaxSyntax

COALESCE ( expression [ ,...n ] )   

ArgumenteArguments

expressionexpression
Ein Ausdruck beliebigen Typs.Is an expression of any type.

RückgabetypenReturn Types

Gibt den Datentyp des expression-Ausdrucks zurück, der in der Datentyprangfolge am höchsten steht.Returns the data type of expression with the highest data type precedence. Falls für alle Ausdrücke NULL nicht zulässig ist, wird das Ergebnis entsprechend eingegeben.If all expressions are nonnullable, the result is typed as nonnullable.

BemerkungenRemarks

COALESCE gibt NULL zurück, wenn alle Argumente NULL sind.If all arguments are NULL, COALESCE returns NULL. Mindestens einer der NULL-Werte muss ein typisierter NULL-Wert sein.At least one of the null values must be a typed NULL.

Vergleich zwischen COALESCE und CASEComparing COALESCE and CASE

Der COALESCE-Ausdruck ist eine syntaktische Kurzform für den CASE-Ausdruck.The COALESCE expression is a syntactic shortcut for the CASE expression. Dies bedeutet, dass der Code COALESCE(expression1, ...n) vom Abfrageoptimierer in den folgenden CASE-Ausdruck umgeschrieben wird:That is, the code COALESCE(expression1,...n) is rewritten by the query optimizer as the following CASE expression:

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.As such, the input values (expression1, expression2, expressionN, and so on) are evaluated multiple times. Außerdem wird ein Wertausdruck, der eine Unterabfrage enthält, als nicht deterministisch angesehen, und die Unterabfrage wird zweimal ausgewertet.A value expression that contains a subquery is considered non-deterministic and the subquery is evaluated twice. Dieses Ergebnis entspricht dem SQL-Standard.This result is in compliance with the SQL standard. In beiden Fällen können zwischen der ersten Auswertung und kommenden Auswertungen unterschiedliche Ergebnisse zurückgegeben werden.In either case, different results can be returned between the first evaluation and upcoming evaluations.

Beispiel: Wenn der Code COALESCE((subquery), 1) ausgeführt wird, wird die Unterabfrage zweimal ausgewertet.For example, when the code COALESCE((subquery), 1) is executed, the subquery is evaluated twice. Folglich können Sie abhängig von der Isolationsstufe der Abfrage unterschiedliche Ergebnisse erhalten.As a result, you can get different results depending on the isolation level of the query. Beispielsweise kann der Code auf der READ COMMITTED-Isolationsstufe in einer Mehrbenutzerumgebung NULL zurückgeben.For example, the code can return NULL under the READ COMMITTED isolation level in a multi-user environment. Um sicherzustellen, dass beständige Ergebnisse zurückgegeben werden, verwenden Sie die SNAPSHOT ISOLATION-Isolationsstufe oder ersetzen COALESCE durch die ISNULL-Funktion.To ensure stable results are returned, use the SNAPSHOT ISOLATION isolation level, or replace COALESCE with the ISNULL function. Alternativ können Sie die Abfrage neu schreiben, um die Unterabfrage in eine untergeordnete SELECT-Anweisung zu verschieben, wie im folgenden Beispiel gezeigt:As an alternative, you can rewrite the query to push the subquery into a subselect as shown in the following example:

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 ISNULLComparing COALESCE and ISNULL

Die ISNULL-Funktion und der COALESCE-Ausdruck dienen einem ähnlichen Zweck, können jedoch ein unterschiedliches Verhalten aufweisen.The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.

  1. Da ISNULL eine Funktion ist, wird sie nur einmal ausgewertet.Because ISNULL is a function, it's evaluated only once. Die Eingabewerte können wie oben beschrieben für den COALESCE-Ausdruck mehrmals ausgewertet werden.As described above, the input values for the COALESCE expression can be evaluated multiple times.

  2. Die Datentypen des resultierenden Ausdrucks werden auf unterschiedliche Weise bestimmt.Data type determination of the resulting expression is different. 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.ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.

  3. Die NULL-Zulässigkeit des Ergebnisausdrucks ist bei ISNULL und COALESCE unterschiedlich.The NULLability of the result expression is different for ISNULL and COALESCE. Beim ISNULL-Rückgabewert wird (in der Annahme, dass er keine NULL-Werte zulässt) immer davon ausgegangen, dass er NOT NULL ist.The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one). Im Gegensatz dazu wird COALESCE mit Parametern ungleich NULL als NULL betrachtet.By contrast,COALESCE with non-null parameters is considered to be NULL. 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.So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1), although equal, have different nullability values. 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:These values make a difference if you're using these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed as shown in the following example:

    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   
    );  
    
  4. Überprüfungen für ISNULL und COALESCE sind ebenfalls unterschiedlich.Validations for ISNULL and COALESCE are also different. Beispielsweise wird ein NULL-Wert für ISNULL in int konvertiert, während Sie für COALESCE einen Datentyp angeben müssen.For example, a NULL value for ISNULL is converted to int though for COALESCE, you must provide a data type.

  5. ISNULL verwendet nur zwei Parameter.ISNULL takes only two parameters. Im Gegensatz dazu ist bei COALESCE die Parameteranzahl variabel.By contrast COALESCE takes a variable number of parameters.

BeispieleExamples

A.A. Ausführen eines einfachen BeispielsRunning a simple example

Im folgenden Beispiel wird veranschaulicht, wie COALESCE die Daten aus der ersten Spalte auswählt, die einen Wert ungleich NULL aufweist.The following example shows how COALESCE selects the data from the first column that has a nonnull value. In diesem Beispiel wird die AdventureWorks2012AdventureWorks2012-Datenbank verwendet.This example uses the AdventureWorks2012AdventureWorks2012 database.

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

B.B. Ausführen eines komplexen BeispielsRunning a complex example

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.In the following example, the wages table includes three columns that contain information about the yearly wages of the employees: the hourly wage, salary, and commission. Allerdings wird ein Angestellter nur nach einem dieser Gehaltstypen bezahlt.However, an employee receives only one type of pay. 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.To determine the total amount paid to all employees, use COALESCE to receive only the nonnull value found in hourly_wage, salary, and commission.

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 ist das Resultset.Here is the result set.

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 BeispielC: Simple Example

Im folgenden Beispiel wird veranschaulicht, wie COALESCE die Daten aus der ersten Spalte auswählt, die einen Wert ungleich NULL aufweist.The following example demonstrates how COALESCE selects the data from the first column that has a non-null value. In diesem Beispiel wird angenommen, dass die Products-Tabelle die folgenden Daten enthält:Assume for this example that the Products table contains this data:

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

Wir führen anschließend die folgende COALESCE-Abfrage aus:We then run the following COALESCE query:

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

Hier ist das Resultset.Here is the result set.

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.Notice that in the first row, the FirstNotNull value is PN1278, not Socks, Mens. Grund hierfür ist, dass die Name-Spalte im Beispiel nicht als Parameter für COALESCE angegeben wurde.This value is this way because the Name column wasn't specified as a parameter for COALESCE in the example.

D: Komplexes BeispielD: Complex Example

Im folgenden Beispiel werden die Werte in drei Spalten mit COALESCE verglichen und nur die Werte ungleich NULL zurückgegeben, die in den Spalten gefunden wurden.The following example uses COALESCE to compare the values in three columns and return only the non-null value found in the columns.

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 ist das Resultset.Here is the result set.

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 InformationenSee Also

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