SQL Server COALESCE() Function

The COALESCE() function in SQL Server is used to handle NULL values. It allows you to specify a list of values, and it returns the first value in that list that is not NULL.

COALESCE(): Definition and Usage

NULL values can sometimes cause problems in your queries or applications. COALESCE() is a simple yet effective way to deal with this situation by providing a replacement value if NULL is encountered. It checks each value in the list, in order, until it finds one that isn't NULL and returns that value.

Syntax


COALESCE(val1, val2, ..., val_n)

Parameter Values

Parameter	Description
`val1, val2, ..., val_n`	The values to check. At least one value is required. `COALESCE()` checks these in order, returning the first non-`NULL` value.

Examples

Returning the First Non-NULL Value

This example demonstrates finding the first non-NULL value in a list. Note that the data type of the returned value matches the data type of the first non-`NULL` value.

Syntax


SELECT COALESCE(NULL, NULL, NULL, 'W3Schools.com', NULL, 'Example.com');

Output


W3Schools.com

Another Example

Another example with a mix of numeric and string values. The function returns the first non-`NULL` value.

Syntax


SELECT COALESCE(NULL, 1, 2, 'W3Schools.com');