COALESCE

SQL COALESCE

COALESCE returns the first non-null expression among its arguments. If all arguments are NULL, COALESCE returns NULL.

The general syntax for SQL COALESCE:

COALESCE (expression [ ,...n ] )

expression: Is an expression of any type.

Example of SQL COALESCE:
As an example, see how the below query is showing the NULLs in the result

Select ShipCity, ShipRegion
From Orders

ShipCity	ShipRegion
Reims	NULL
Münster	NULL
Rio de Janeiro	RJ
Lyon	NULL
Charleroi	NULL
Rio de Janeiro	RJ
Bern	NULL
Genève	NULL
Resende	SP

and some people do not want to use NULLs in any kind of reports as NULLs don't look nice, so with the help of COALESCE you can write another little bit better query:

Select ShipCity, Coalesce(ShipRegion, 'Default') as ShipRegion
From Orders

ShipCity	ShipRegion
Reims	Default
Münster	Default
Rio de Janeiro	RJ
Lyon	Default
Charleroi	Default
Rio de Janeiro	RJ
Bern	Default
Genève	Default
Resende	SP

SQL COALESCE is a shorthand form of particular CASE expression.

Uses of SQL COALESCE
1. You may want to use COALESCE after you perform outer join operation. In such cases COALESCE can save you a lot of typing.

2. COALESCE is often used to display a specific value instead of Null in the result, which is helpful if your users find Null confusing.

3. COALESCE (expr1, expr2, expr3) is equal to

CASE
WHEN expr1 is NOT NULL THEN expr1
WHEN expr2 is NOT NULL THEN expr2
Else expr3
END

4. COALESCE is more flexible and compliant with the ISO standard to boot. This means that it is also the more portable option among ISO-compliant systems. COALESCE also implicitly converts the result to the data type with the highest precedence from the list of expressions.

More on COALESCE
You can use COALESCE in the SELECT, WHERE or ORDER BY CLAUSE or wherever an Expression is allowed.

If your SQL statement needs to be dynamic but only the WHERE clause needs to be dynamic then you can use the COALESCE function to create statement that has the following advantages:

Quicker to write
Easier to debug
No data type conversion issues
Can have its execution plan stored for repeated use

[Source]

One of the drawbacks of SQL COALESCE is that COALESCE is little slower then ISNULL [Source]


Good books for learning SQL COALESCE/ References:
SQL for Dummies By Allen G. Taylor
SQL: Visual QuickStart Guide By Chris Fehily
Pro T-SQL 2008 Programmer's Guide By Michael Coles