What is COALESCE

The ISNULL function is not ANSI standard, so you may wish to use the COALESCE function instead. COALESCE is a little more flexible in that it can take a variable number of arguments, each of which is an expression. It will return the first expression in the list that is not NULL.

If there are only two arguments, COALESCE behaves like ISNULL. However, with more than two arguments, COALESCE can be used as an alternative to a multipart CASE expression using ISNULL.

If all arguments are NULL, COALESCE returns NULL. All the expressions must return the same or compatible data types.

The syntax is as follows:

SQLCopy

SELECT COALESCE ( expression1, expression2, [ ,...n ] )

The following example uses a fictitious table called HR.Wages, which includes three columns that contain information about the weekly earnings of the employees: the hourly rate, the weekly salary, and a commission per unit sold. However, an employee receives only one type of pay. For each employee, one of those three columns will have a value, the other two will be NULL. To determine the total amount paid to each employee, you can use COALESCE to return only the non-null value found in those three columns.

SQLCopy

SELECT EmployeeID,
      COALESCE(HourlyRate * 40,
                WeeklySalary,
                Commission * SalesQty) AS WeeklyEarnings
FROM HR.Wages;

The results might look something like this:

EmployeeID

WeeklyEarnings

1

899.76

2

1001.00

3

1298.77

…

…

NULLIF

The NULLIF function allows you to return NULL under certain conditions. This function has useful applications in areas such as data cleansing, when you wish to replace blank or placeholder characters with NULL.

NULLIF takes two arguments and returns NULL if they’re equivalent. If they aren’t equal, NULLIF returns the first argument.

In this example, NULLIF replaces a discount of 0 with a NULL. It returns the discount value if it is not 0:

SQLCopy

SELECT SalesOrderID,
      ProductID,
      UnitPrice,
      NULLIF(UnitPriceDiscount, 0) AS Discount
FROM Sales.SalesOrderDetail;

The results might look something like this:

SalesOrderID

ProductID

UnitPrice

Discount

71774

836

356.898

NULL

71780

988

112.998

0.4

71781

748

818.7

NULL

71781

985

112.998

0.4

…

…

…

…