COALESCE function is used to get first nonnull expression among specified arguments. If all arguments are NULL then COALESCE returns NULL.
It is a deterministic i.e It returns same value every time it is called with a specific set of values.
Syntax of COALESCE Function :
COALESCE ( expression [ ,...n ] )
expression is any valid sql server expression.
n is a placeholder indicates that multiple expressions can be specified. All expressions must be of the same type or must be implicitly convertible to the same type.
Return type of COALESCE function is same as expression.
Examples of COALESCE Function :
Example 1 : Use of COALESCE function in select clause
SELECT COALESCE(10/2,NULL)
Output
5
Above example returns nonnull expression result i.e 5
Example 2 : Use of COALESCE function to display column value of table in select clause
SELECT ProductName, COALESCE(UnitPrice * Quantity,NULL) AS Total
FROM Invoices
Output
Scottish Longbreads 225
Queso Cabrales 840
Côte de Blaye 10540
Alice Mutton 585
Sasquatch Ale 140
Jack's New England Clam Chowder 135.1
Nord-Ost Matjeshering 388.35
Above example returns nonnull expression.