RSS Feed

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

ShipCityShipRegion
ReimsNULL
MünsterNULL
Rio de JaneiroRJ
LyonNULL
CharleroiNULL
Rio de JaneiroRJ
BernNULL
GenèveNULL
ResendeSP

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

ShipCityShipRegion
ReimsDefault
MünsterDefault
Rio de JaneiroRJ
LyonDefault
CharleroiDefault
Rio de JaneiroRJ
Bern Default
GenèveDefault
ResendeSP

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