How to Use SQL with Microsoft Access
How to Calculate Date Values in SQL Data for HTML5and CSS3 Programming
How to Implement a Database in MySQL for HTML5 and CSS3 Programming

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.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
How to Run a Script with phpMyAdmin in SQL for HTML5and CSS3 Programming
How to Identify Relationships in SQL Data for HTML5and CSS3 Programming
How to Export SQL Data and Structure for HTML5and CSS3 Programming
How to Take Away SQL Privileges
Managing Rows in a Table in SQL
Advertisement

Inside Dummies.com