COALESCE (Transact-SQL)
Returns the first nonnull expression among its arguments.
Transact-SQL Syntax Conventions
- expression
-
Is an expression of any type.
If all arguments are NULL, COALESCE returns NULL.
| Note: |
|---|
| At least one of the null values must be a typed NULL. |
COALESCE(expression1,...n) is equivalent to this CASE function:
CASE WHEN (expression1 IS NOT NULL) THEN expression1 ... WHEN (expressionN IS NOT NULL) THEN expressionN ELSE NULL END
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
)
In the following example, the wages table includes three columns with 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 the COALESCE function to receive only the nonnull value found in hourly_wage, salary, and commission.
SET NOCOUNT ON;
GO
USE master;
IF EXISTS (SELECT name FROM sys.tables
WHERE name = 'wages')
DROP TABLE wages;
GO
CREATE TABLE wages
(
emp_id tinyint identity,
hourly_wage decimal NULL,
salary decimal NULL,
commission decimal NULL,
num_sales tinyint NULL
);
GO
INSERT wages VALUES(10.00, NULL, NULL, NULL);
INSERT wages VALUES(20.00, NULL, NULL, NULL);
INSERT wages VALUES(30.00, NULL, NULL, NULL);
INSERT wages VALUES(40.00, NULL, NULL, NULL);
INSERT wages VALUES(NULL, 10000.00, NULL, NULL);
INSERT wages VALUES(NULL, 20000.00, NULL, NULL);
INSERT wages VALUES(NULL, 30000.00, NULL, NULL);
INSERT wages VALUES(NULL, 40000.00, NULL, NULL);
INSERT wages VALUES(NULL, NULL, 15000, 3);
INSERT wages VALUES(NULL, NULL, 25000, 2);
INSERT wages VALUES(NULL, NULL, 20000, 6);
INSERT wages VALUES(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 wages;
GO
Here is the result set.
Total Salary ------------ 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 row(s) affected)
Reference
CASE (Transact-SQL)ISNULL (Transact-SQL)
System Functions (Transact-SQL)
