How to use COALESCE SQL function

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • r035198x
    MVP
    • Sep 2006
    • 13225

    #1

    How to use COALESCE SQL function

    This is a short tip describing the sql coalesce function.

    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
    Code:
    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

    Code:
    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
    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.
    Code:
    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
    Code:
    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:
    Code:
    SELECT * FROM Products WHERE (name  LIKE COALESCE($P, '%' ))
    Portability

    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.
  • JamieHowarth0
    Recognized Expert Contributor
    • May 2007
    • 537

    #2
    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:
    Code:
    CREATE PROCEDURE updatemyrecord
        @myparam1 nvarchar(255),
        @myparam2 nvarchar(255),
        @myparam3 nvarchar(255),
        @myparam4 nvarchar(255),
        @recordid int
    AS
    BEGIN
        UPDATE mytable SET
        MyColumn1 = COALESCE(@myparam1, MyColumn1),
        MyColumn2 = COALESCE(@myparam2, MyColumn2),
        MyColumn3 = COALESCE(@myparam3, MyColumn3),
        MyColumn4 = COALESCE(@myparam4, MyColumn4)
    WHERE RecordId = @recordid
    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.

    medicineworker

    Comment

    Working...