MySQL in a Nutshell

Name

NULLIF( )

Synopsis

NULLIF(condition1, condition2)

This function returns NULL if the two arguments given are equal. Otherwise, it returns the value or results of the first argument.

SELECT clients.client_id AS ID,
CONCAT(name_first, SPACE(1), name_last) AS Client,
telephone_home AS Telephone,
NULLIF(
   (SELECT SUM(qty * price)
    FROM investments, stock_prices
    WHERE stock_symbol = symbol
    AND client_id = ID ), 0)
AS Value
FROM clients, investments
WHERE clients.client_id = investments.client_id
GROUP BY clients.client_id;

In this example, NULL is returned for the Value column if the value of the client’s stocks is 0 (i.e., the client had stocks but sold them all). If there is a value to the stocks, however, the sum of their values is displayed.

This function is the same as:

CASE WHEN condition1 = condition2
THEN NULL

ELSE condition1;