471,887 Members | 739 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 471,887 developers and data experts.

How to use COALESCE SQL function

13,262 8TB
This is a short tip describing the sql coalesce function.


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.
Expand|Select|Wrap|Line Numbers
  1. COALESCE (a1, a2, a3, ..., an)
will return a1 if a1 is not null, a2 if a1 is null but a2 is not null,
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
  2. WHEN (a2 IS NOT NULL) THEN a2
  3. WHEN (a3 IS NOT NULL) THEN a3
  4. ...
  6. WHEN (an IS NOT NULL) THEN an
Notice that the ELSE NULL at the end ensures that NULL is returned
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT  COALESCE (name, description) as Product FROM Products 
will show the description only if the name is null.
Optional parameters

  • Coalesce can also be used to provide a way of passing optional parameters.
Normally for a parameter say $P you would do
Expand|Select|Wrap|Line Numbers
  1. 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 only
the product with name $P is selected. With coalesce, this can be done as:
Expand|Select|Wrap|Line Numbers
  1. 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.
Jul 24 '08 #1
1 28590
533 Expert 512MB
Another good use of COALESCE is in UPDATE statements. To prevent having to resubmit an entire record, you can add a listener for which fields have changed from the underlying data source, and then your update procedure would be similar to the following:
Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE updatemyrecord
  2.     @myparam1 nvarchar(255),
  3.     @myparam2 nvarchar(255),
  4.     @myparam3 nvarchar(255),
  5.     @myparam4 nvarchar(255),
  6.     @recordid int
  7. AS
  8. BEGIN
  9.     UPDATE mytable SET
  10.     MyColumn1 = COALESCE(@myparam1, MyColumn1),
  11.     MyColumn2 = COALESCE(@myparam2, MyColumn2),
  12.     MyColumn3 = COALESCE(@myparam3, MyColumn3),
  13.     MyColumn4 = COALESCE(@myparam4, MyColumn4)
  14. WHERE RecordId = @recordid
  15. END
This prevents the need for submitting every value to the database, just the ones that have changed, and thus helping to prevent accidental loss of data.

Oct 3 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

24 posts views Thread by Edmund Dengler | last post: by
5 posts views Thread by John Greve | last post: by
5 posts views Thread by Simon Windsor | last post: by
10 posts views Thread by db2group88 | last post: by
1 post views Thread by =?Utf-8?B?R1ROMTcwNzc3?= | last post: by
reply views Thread by YellowAndGreen | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.