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