BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

Using T-SQL's COALESCE Function

In denormalised databases, such as those used for reporting purposes or to feed data warehouses, it is common to need to query information from a group of columns where only one has a value. This can be simplified with the coalesce function.

NULL Values

A NULL value in a SQL Server database table usually indicates that the row has no value in the particular column. Rather than this representing a blank string or a zero, the NULL means that no value is defined whatsoever.

Occasionally you might need to find a non-NULL item in a group of values that each may be null. For example, consider the table and sample data defined by the script below:

CREATE TABLE VehicleMileage
(
    VehicleOwner VARCHAR(10),
    CarMileage INT,
    MotorcycleMileage INT,
    BicycleMileage INT
)

INSERT INTO VehicleMileage VALUES ('Bob', 9500, NULL, NULL)
INSERT INTO VehicleMileage VALUES ('Sam', NULL, 6500, NULL)
INSERT INTO VehicleMileage VALUES ('Mel', NULL, NULL, 1000)
INSERT INTO VehicleMileage VALUES ('Jim', 8475, NULL, NULL)
INSERT INTO VehicleMileage VALUES ('Kat', NULL, 3875, NULL)

This simple table holds information about vehicle owners. The first column holds the name of the person who owns a vehicle. The remaining three columns indicate the mileage travelled by that person. If the person is a driver, the mileage is in the CarMileage column. For motorcycle riders the mileage is in the MotorcycleMileage Column. The BicycleMileage column indicates the distance travelled by bicycle riders.

This denormalised schema may be helpful for generating expense reports if different vehicles attract different payments. It makes sense to hold NULLs, rather than zeroes, as this indicates that the person does not own such a vehicle. A zero might indicate that the vehicle is available but was not used.

If you wanted to find the mileage travelled by each individual, there are a number of approaches you could use. A simple one would be to sum the mileage columns, using isnull to change NULLs to zeroes for the query. This function checks if a single value is null. If it is, a default value is returned. If not, the original value is used. Such a query is shown below:

SELECT
    VehicleOwner,
    isnull(CarMileage,0) + isnull(MotorcycleMileage,0) + isnull(BicycleMileage,0) AS Mileage
FROM
    VehicleMileage

/* RESULTS

VehicleOwner Mileage
------------ -------
Bob          9500
Sam          6500
Mel          1000
Jim          8475
Kat          3875

*/

This method would not be useful if a company rule meant that only one vehicle could be used within an expenses claim. In this case you might decide to use a searched CASE expression, checking the types of vehicle in the order of priority. The query below does this, preferring mileage claims for cars over motorcycles and any motorised vehicle over a bicycle.

SELECT
    VehicleOwner,
    CASE
        WHEN CarMileage IS NOT NULL THEN CarMileage
        WHEN MotorcycleMileage IS NOT NULL THEN MotorcycleMileage
        WHEN BicycleMileage IS NOT NULL THEN BicycleMileage
        ELSE 0
    END AS Mileage
FROM
    VehicleMileage

/* RESULTS

VehicleOwner Mileage
------------ -------
Bob          9500
Sam          6500
Mel          1000
Jim          8475
Kat          3875

*/

NB: The "ELSE 0" option at the end of the CASE clause is present so that any person with no vehicle returns zero miles, rather than NULL. This is not necessary for the query to operate but might be useful in report production.

COALESCE Function

The CASE expression shown above can be simplified using Transact SQL's (T-SQL) coalesce function. This function accepts a number of parameters, each a value that may or may not be NULL. It cycles through the provided items in the order they appear until a non-NULL value is encountered. This becomes the return value. If all of the arguments of the function are NULL, the return value is also NULL.

We can recreate the previous query using coalesce for a briefer, more pleasing query, as shown below. Note that the three columns and zero are used in the function; if all of the mileage columns are NULL, the person's mileage will be reported as zero.

SELECT
    VehicleOwner,
    COALESCE(CarMileage, MotorcycleMileage, BicycleMileage, 0) AS Mileage
FROM
    VehicleMileage

/* RESULTS

VehicleOwner Mileage
------------ -------
Bob          9500
Sam          6500
Mel          1000
Jim          8475
Kat          3875

*/
18 July 2013