Returns the first nonnull expression among its arguments.
COALESCE ( expression [ ,...n ] )
Is an expression of any type.
Returns the data type of expression with the highest data type precedence. If all expressions are nonnullable, the result is typed as nonnullable.
If all arguments are NULL, COALESCE returns NULL.
At least one of the null values must be a typed NULL.
COALESCE(expression1,...n) is equivalent to the following CASE expression:
WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expression2 IS NOT NULL) THEN expression2
ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL. In SQL Server, to index expressions involving COALESCE with non-null parameters, the computed column can be persisted using the PERSISTED column attribute as in the following statement:
CREATE TABLE #CheckSumTest ( ID int identity , Num int DEFAULT ( RAND() * 100 ) , RowCheckSum AS COALESCE( CHECKSUM( id , num ) , 0 ) PERSISTED PRIMARY KEY );
A. Running a simple example
The following example shows how COALESCE selects the data from the first column that has a nonnull value.
USE AdventureWorks2008R2; GO SELECT Name, Class, Color, ProductNumber, COALESCE(Class, Color, ProductNumber) AS FirstNotNull FROM Production.Product ; GO
B. Running a complex example
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 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
Here is the result set.
(12 row(s) affected)