COALESCE

Description

Evaluates the arguments in order and returns the current value of the first expression that initially doesn't evaluate to NULL. 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. (COALESCE in Transact-SQL).

Sample Source Pattern

Syntax

SQL Server

COALESCE ( expression [ ,...n ] )   

Snowflake SQL

Snowflake SQL Documentation

COALESCE( <expr1> , <expr2> [ , ... , <exprN> ] )

Examples

SQL Server

Code:

SELECT TOP 10 StartDate, 
COALESCE(EndDate,'2000-01-01') AS FIRST_NOT_NULL 
FROM HumanResources.EmployeeDepartmentHistory

Result:

StartDate |FIRST_NOT_NULL|
----------+--------------+
2009-01-14|    2000-01-01|
2008-01-31|    2000-01-01|
2007-11-11|    2000-01-01|
2007-12-05|    2010-05-30|
2010-05-31|    2000-01-01|
2008-01-06|    2000-01-01|
2008-01-24|    2000-01-01|
2009-02-08|    2000-01-01|
2008-12-29|    2000-01-01|
2009-01-16|    2000-01-01|

Snowflake SQL

Code:

SELECT TOP 10 StartDate,
COALESCE(EndDate,'2000-01-01') AS FIRST_NOT_NULL 
FROM HumanResources.EmployeeDepartmentHistory;

Result:

StartDate |FIRST_NOT_NULL|
----------+--------------+
2009-01-14|    2000-01-01|
2008-01-31|    2000-01-01|
2007-11-11|    2000-01-01|
2007-12-05|    2010-05-30|
2010-05-31|    2000-01-01|
2008-01-06|    2000-01-01|
2008-01-24|    2000-01-01|
2009-02-08|    2000-01-01|
2008-12-29|    2000-01-01|
2009-01-16|    2000-01-01|