Control Flow Functions

The first set of functions we will consider are the control flow functions. The most useful of these are IF and CASE. These work similarly to an if statement and a switch or case statement (respectively) in most programming languages.

The IF function has the prototype

IF (e1, e2, e3)

If the expression e1 is true, IF returns e2; otherwise, it returns e3. For example, using the employee database, we can run the following query:

select name, if(job='Programmer', "nerd", "not a nerd")
from employee;

This will produce the following result:

+---------------+--------------------------------------------+
| name | if(job='Programmer', "nerd", "not a nerd") |
+---------------+--------------------------------------------+
| Ajay Patel | nerd |
| Nora Edwards | nerd |
| Candy Burnett | not a nerd |
| Ben Smith | not a nerd |
+---------------+--------------------------------------------+
4 rows in set (0.00 sec)

The CASE function has the following two possible prototypes (from the MySQL manual):

CASE value
WHEN [compare-value] THEN result
[WHEN [compare-value] THEN result ...]
[ELSE result]
END

or

CASE
WHEN [condition] THEN result
[WHEN [condition] THEN result ...]
[ELSE result]
END

We can use this function to return one of a number of values. For example, consider the following query:

select workdate, case
when workdate < 2000-01-01 then "archived"
when workdate < 2003-01-01 then "old"
else "current"
end
from assignment;

This query evaluates the workdate for each assignment in the assignment table. Assignments from the last century are categorized as "archived", ones prior to this year are categorized as "old", and everything else is "current".