How to Use the SQL CASE Expressions NULLIF and COALESCE
The one thing you can be sure of in this world is change. Luckily, SQL accounts for this. Classical thermodynamics and modern chaos theory both indicate that systems naturally migrate from a well-known, ordered state into a disordered state that no one can predict. Anyone who has ever monitored the status of a teenager’s room for a one-week period after the room is cleaned can vouch for these theories.
Database tables have definite values in fields containing known contents. Usually, if the value of a field is unknown, the field contains the null value. In SQL, you can use a CASE expression to change the contents of a table field from a definite value to a null value. The null value indicates that you no longer know the field’s value. Consider the following example.
Imagine that you own a small airline that offers flights between Southern California and Washington state. Until recently, some of your flights stopped at San Jose International Airport to refuel before continuing. Unfortunately, you just lost your right to fly into San Jose. From now on, you must make your refueling stop at either San Francisco International Airport or Oakland International Airport.
At this point, you don’t know which flights stop at which airport, but you do know that none of the flights are stopping at San Jose. You have a FLIGHT database that contains important information about your routes, and now you want to update the database to remove all references to San Jose. The following example shows one way to do this:
UPDATE FLIGHT SET RefuelStop = CASE WHEN RefuelStop = 'San Jose' THEN NULL ELSE RefuelStop END ;
Because occasions like this one — in which you want to replace a known value with a null value — frequently arise, SQL offers a shorthand notation to accomplish this task. The preceding example, expressed in this shorthand form, looks like this:
UPDATE FLIGHT SET RefuelStop = NULLIF(RefuelStop, 'San Jose') ;
You can translate this expression to English as, Update the FLIGHT table by setting the RefuelStop column to null if the existing value of RefuelStop is ‘San Jose’. Otherwise make no change.
NULLIF is even handier if you’re converting data that you originally accumulated for use with a program written in a standard programming language such as C++ or Java. Standard programming languages don’t have nulls, so a common practice is to use special values to represent the concept of not known or not applicable.
A numeric –1 may represent a not-known value for SALARY, for example, and a character string *** may represent a not-known or not-applicable value for JOBCODE. If you want to represent these not-known and not-applicable states in an SQL-compatible database by using nulls, you have to convert the special values to nulls. The following example makes this conversion for an employee table, in which some salary values are unknown:
UPDATE EMP SET Salary = CASE Salary WHEN -1 THEN NULL ELSE Salary END ;
You can perform this conversion more conveniently by using NULLIF, as follows:
UPDATE EMP SET Salary = NULLIF(Salary, -1) ;
COALESCE, like NULLIF, is a shorthand form of a particular CASE expression. COALESCE deals with a list of values that may or may not be null. Here’s how it works:
If one of the values in the list is not null: The COALESCE expression takes on that value.
If more than one value in the list is not null: The expression takes on the value of the first non-null item in the list.
If all the values in the list are null: The expression takes on the null value.
A CASE expression with this function has the following form:
CASE WHEN value1 IS NOT NULL THEN value1 WHEN value2 IS NOT NULL THEN value2 ... WHEN valuen IS NOT NULL THEN valuen ELSE NULL END
The corresponding COALESCE shorthand looks like this:
COALESCE(value1, value2, ..., valuen)
You may want to use a COALESCE expression after you perform an OUTER JOIN operation. In such cases, COALESCE can save you a lot of typing.