Conditional Functions mysql

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)