Conditional Functions

MySQL also includes a set of functions that return their result based on a condition, just like the conditions you use in a WHERE clause.

TRUE and FALSE

Every condition returns a value, trUE or FALSE, depending on whether the condition is satisfied. There is no Boolean data type in MySQLTRUE equates to 1, and FALSE equates to 0.

You can actually see these values by executing a condition as a query, as follows:

 mysql> SELECT code, price, price > 10.00     -> FROM products; +------+-------+---------------+ | code | price | price > 10.00 | +------+-------+---------------+ | MINI | 5.99  |             0 | | MIDI | 9.99  |             0 | | MAXI | 15.99 |             1 | +------+-------+---------------+ 3 rows in set (0.00 sec) 

The output from this query shows each product and its price from the products table. The conditional column returns TRUE when the price is greater than 10.00.

The IF() Function

The IF() function provides a way to return a value based on a condition within a query.

The first argument is a condition that is evaluated for each row of the query. The value in the second is returned if the condition is trUE, and the third argument is returned if it is FALSE.

The following example is a very simple shipping rate calculator. If the product weight is less than 5 pounds, shipping costs $1.99; otherwise, it costs $2.99.

 mysql> SELECT code, weight, IF(weight < 5, 1.99, 2.99)     -> FROM products; +------+--------+------------------------------+ | code | weight |    IF(weight <5, 1.99, 2.99) | +------+--------+------------------------------+ | MINI |   1.50 |                         1.99 | | MIDI |   4.50 |                         1.99 | | MAXI |   8.00 |                         2.99 | +------+--------+------------------------------+ 3 rows in set (0.00 sec)  

The CASE Statement

The CASE statement is a multiple-valued conditional construct. Suppose you wanted to set three or more shipping rates based on weight. This would require a complex series of nested IF() functions.

The following example uses a CASE statement to determine the shipping rate based on three different weight bands:

 mysql> SELECT code, weight,     ->        CASE WHEN weight < 2 THEN 1.99     ->             WHEN weight < 5 THEN 2.99     ->             ELSE 4.99 END as shipping     -> FROM products;  +------+--------+----------+ | code | weight | shipping | +------+--------+----------+ | MINI |   1.50 |     1.99 | | MIDI |   4.50 |     2.99 | | MAXI |   8.00 |     4.99 | +------+--------+----------+ 3 rows in set (0.00 sec) 

A CASE can contain as many WHEN clauses as you need (in this example, there are two) and an ELSE clause that specifies a default value. A CASE statement can grow quite large; you must use the END keyword to indicate that it has ended.

If one of the WHEN clauses contains a condition that is trUE, its corresponding value, given after the keyword THEN, is returned. Otherwise, the ELSE value is returned.

MySQL examines the WHEN clauses in the order they appear. In this example, the first value returned from the database is 1.50, which satisfies both WHEN conditions (it is less than both 2 and 5), but only the value from the first clause is returned.

ELSE

If no ELSE clause is specified, a CASE statement that does not match any of the WHEN clauses returns NULL.

The IFNULL() Function

The IFNULL() function provides a shortcut when you expect to see NULL values. If the first argument is NULL, the second argument is returned; otherwise, the first argument is returned unchanged.

 mysql> SELECT IFNULL(NULL, 99), IFNULL(55, 99); +------------------+---------------+ | IFNULL(NULL, 99) | IFNULL(0, 99) | +------------------+---------------+ | 99               |             0 | +------------------+---------------+ 1 row in set (0.00 sec) 

This example reaffirms that NULL and zero are not the same value.

The NULLIF() Function

Use NULLIF() to return NULL when two values are the same. If the two expressions passed to NULLIF() are the same, the result is NULL; otherwise, the first argument is returned. The following query replaces the price of a product that costs $9.99 with NULL.

 mysql> SELECT code, price, NULLIF(price, 9.99)     -> FROM products; +------+-------+---------------------+ | code | price | NULLIF(price, 9.99) | +------+-------+---------------------+ | MINI |  5.99 |                5.99 | | MIDI |  9.99 |                NULL | | MAXI | 15.99 |               15.99 | +------+-------+---------------------+ 3 rows in set (0.00 sec)