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" .