Description
COALESCE accepts a comma separated list of parameters and returns
the first value that is not null in that list or null if all the arguments are null.
Thus
Expand|Select|Wrap|Line Numbers
- COALESCE (a1, a2, a3, ..., an)
a3 if a1, a2 are null but a3 is not null and so on.
The same thing can be achieved by using a CASE. For the snippet above the CASE would be
Expand|Select|Wrap|Line Numbers
- CASE WHEN (a1 IS NOT NULL) THEN a1
- WHEN (a2 IS NOT NULL) THEN a2
- WHEN (a3 IS NOT NULL) THEN a3
- ...
- WHEN (an IS NOT NULL) THEN an
- ELSE NULL
when all the arguments are null to conform to the coalesce behaviour.
Clearly the coalesce version is much more compact.
Common uses
Preferential selection
- Usually used when many columns can be used to represent a value but only one should be used at a time.
In this case the parameters are normally specified starting with the most specific or detailed representation.Optional parameterswill show the description only if the name is null.Expand|Select|Wrap|Line Numbers
- SELECT COALESCE (name, description) as Product FROM Products
- Coalesce can also be used to provide a way of passing optional parameters.
Normally for a parameter say $P you would doExpand|Select|Wrap|Line Numbers
- SELECT * FROM Products WHERE (name = '$P' OR $P IS NULL)
If the parameter $P is passed as NULL all the products will be selected otherwise onlyPortability
the product with name $P is selected. With coalesce, this can be done as:Expand|Select|Wrap|Line Numbers
- SELECT * FROM Products WHERE (name LIKE COALESCE($P, '%' ))
This function is an ANSI standard function and so is available in most
RDBMs and generally more advisable to use than RDMS specific
functions (e.g SQL Server's ISNULL) .
Hope this was useful.