There are three functions designed to handle a subset of the CASE functionality:
NULLIF, COALESCE, and IFNULL.
The NULLIF function returns a null value if the arguments are equal, otherwise it returns the value of the first argument.
Example
SELECT item, NULLIF(cost, -1)
FROM inventory;
For this example, say that a cost value of -1 indicates no cost at all. With the .NULLS system variable set to 'N/A', it might be more informative to return a null value instead of -1. Using the NULLIF function, a null value can be substituted for every return of -1.
The equivalent (simple) CASE expression for this example would be the following:
SELECT item,
CASE cost
WHEN -1 THEN NULL
ELSE cost
END
FROM inventory;
The COALESCE function returns the first argument that is not null. The arguments are evaluated in the order in which they are specified. The result is null only if all the arguments are null.
Example
SELECT lastname,
job_desc,
COALESCE(salary, contract, commission, subsistence)
FROM payroll;
In this example, a worker on the payroll is paid either a regular salary, contract pay, a commission, or subsistence wages. The COALESCE function returns the value for the appropriate pay type, assuming all but the applicable pay field store null values. If none of the pay types apply, a null value is returned.
The equivalent (searched) CASE expression for this example would be the following:
SELECT lastname,
job_desc,
CASE
WHEN salary IS NOT NULL THEN salary
WHEN contract IS NOT NULL THEN contract
WHEN commission IS NOT NULL THEN commission
WHEN subsistence IS NOT NULL THEN subsistence
ELSE NULL
END
FROM payroll;
The IFNULL function returns the first of two arguments that is not null. This is the same as calling the COALESCE function with only two arguments. As with the COALESCE function, the IFNULL arguments are evaluated in the order in which they are specified, and the result is null only if both arguments are null.
Example
SELECT item, IFNULL(price, -1) AS Price
FROM inventory;
In this example, a SELECT statement returns a list each item and its associated price from the inventory table. The IFNULL function is used here to intercept item prices that have not been set (and are therefore null) and return a value of -1 instead. For items whose price is not null, the IFNULL function returns the price value unchanged.
The equivalent (searched) CASE expression for this example would be the following:
SELECT item,
CASE
WHEN price IS NOT NULL THEN price
ELSE -1
END
AS Price
FROM inventory;