Léigh i mBéarla Cuir in eagar

Comhroinn trí

COALESCE (Transact-SQL)

  • Alt

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric SQL database in Microsoft Fabric

Evaluates the arguments in order and returns the current value of the first expression that initially doesn't evaluate to NULL. The following example returns the third value because the third value is the first value that isn't null.

SQL 
SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value');

Nóta

If you want to concatenate strings, use STRING_AGG instead.

Transact-SQL syntax conventions

Syntax

syntaxsql 
COALESCE ( expression [ , ...n ] )

Arguments

expression

An expression of any type.

Return types

Returns the data type of expression with the highest data type precedence. If all expressions are non-nullable, the result is typed as non-nullable.

Remarks

If all arguments are NULL, COALESCE returns NULL. At least one of the null values must be a typed NULL.

Compare COALESCE and CASE

The COALESCE expression is a syntactic shortcut for the CASE expression. That is, the code COALESCE(<expression1>, ...n) is rewritten by the query optimizer as the following CASE expression:

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

As such, the input values (expression1, expression2, expressionN, and so on) are evaluated multiple times. A value expression that contains a subquery is considered nondeterministic and the subquery is evaluated twice. This result is in compliance with the SQL standard. In either case, different results can be returned between the first evaluation and upcoming evaluations.

For example, when the code COALESCE((subquery), 1) is executed, the subquery is evaluated twice. As a result, you can get different results depending on the isolation level of the query. For example, the code can return NULL under the READ COMMITTED isolation level in a multi-user environment. To ensure stable results are returned, use the SNAPSHOT ISOLATION isolation level, or replace COALESCE with the ISNULL function. As an alternative, you can rewrite the query to push the subquery into a subselect as shown in the following example:

SQL 
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;

Compare COALESCE and ISNULL

The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.

  1. Because ISNULL is a function, it's evaluated only once. As described previously, the input values for the COALESCE expression can be evaluated multiple times.

  2. Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, and COALESCE follows the CASE expression rules to return the data type of value with the highest precedence.

  3. The NULLability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one). By contrast,COALESCE with non-null parameters is considered to be NULL. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1), although equal, have different nullability values. 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 user-defined function (UDF) deterministic, so that it can be indexed as shown in the following example:

    SQL 
    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 INT 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 INT NULL,
    col2 AS COALESCE (col1, 0),
    col3 AS ISNULL(col1, 0) PRIMARY KEY
);

  4. Validations for ISNULL and COALESCE are also different. For example, a NULL value for ISNULL is converted to int though for COALESCE, you must provide a data type.

  5. ISNULL takes only two parameters. By contrast COALESCE takes a variable number of parameters.

Examples

The code samples in this article use the AdventureWorks2022 or AdventureWorksDW2022 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

A. Return data from the first column that has a non-null value

The following example demonstrates how COALESCE selects the data from the first column that has a non-null value. Assume for this example that the Products table contains this data:

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

We then run the following COALESCE query:

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

Here's the result set.

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

In the first row, the FirstNotNull value is PN1278, not Socks, Mens. This value is this way because the Name column wasn't specified as a parameter for COALESCE in the example.

B. Return the non-null value in a wages table

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. However, an employee receives only one type of pay. To determine the total amount paid to all employees, use COALESCE to receive only the non-null value found in hourly_wage, salary, and commission.

SQL 
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

Here's the result set.

Output 
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

Related content