Conditional Statements

Conditional Statements

Conditional Control statements execute code only if a condition holds true. These statements allow different actions to be taken based on different conditions. The condition can range from a literal or variable to a function that returns a value.

MySQL supports two conditional control statements IF and CASE. Both provide similar functionality and the choice between the two is a matter of personal preference, However, we will discuss situations in which choosing one type of statement may be better than the other.

IF Statement

The structure of MySQL IF statement is very similar to one used in other programming languages.There are three different forms of the IF statement depending on how many conditions are being tested.

A single IF-THEN block is used if some statements are to be executed based on a specific condition. In the case of MySQL, a condition can evaluate to TRUE, FALSE, or NULL (neither true nor false). So unlike other programming languages if a condition is not TRUE it does not automatically mean that it is FALSE. The condition to execute the code is given between the IF and THEN words. If the condition holds true then statements written between the IF-THEN and END IF are executed otherwise the control moves to the next statement after the IF block. Multiple statements can be written in a block including calls to stored procedures, SET statements, loops and nested IFs.

IF Condition THEN

If_statements;

END IF;

The second variation of IF statement is used when we want to execute some statements when the condition for IF block is true and some other statements when the condition is false. In this case the IF THEN ELSE statement is used. The ELSE block is executed when the condition for IF block evaluates to both FALSE and NULL.

IF Condition THEN

If_statements;

ELSE

else_statements;

END IF;

If there are multiple conditions to test then the full syntax of the IF__ statement IF-THEN-ELSEIF-ELSE is used. In this syntax the statements corresponding to the first condition that holds true are executed. If no condition is found to be true, then the ELSE block is executed. There can be as many ELSEIF blocks as required.

IF Condition THEN

If_statements;

ELSEIF else-if_condition

else-if_statements;

…

ELSE

else_statements;

END IF;

CASE Statement

CASE statement is an alternate conditional control statement offered by MySQL. Any stored procedure that has IF statement can be replaced by CASE statement and vice versa. There are two forms of CASE statement.

The simple CASE statement is when we compare the output to multiple distinct values. The values after the WHEN keyword are sequentially compared and if there is a match then the statements in the corresponding THEN block are executed. If no match is found then the optional ELSE block is executed. In case the ELSE block does not exist and CASE cannot find a match, then an error message is issued.

CASE case_value

WHEN case_value1 THEN statements

WHEN case_value2 THEN statements

…

[ELSE else-statements]

END CASE;

The other type of CASE statement is the searched CASE statement which can handle complex conditions with multiple expressions. It is used to test conditions involving ranges. The condition after the WHEN keyword is evaluated and if it evaluates to TRUE then the statements in the corresponding THEN block are executed. Similar to the simple case statement, if no condition evaluates to TRUE then the optional ELSE block is executed and an error message is issued if the ELSE block does not exist. To ignore errors an empty BEGIN END block can be written in the ELSE block.

CASE

WHEN case_1 condition THEN statements

WHEN case_2 condition THEN statements

…

[ELSE else-statements]

END CASE;

The following code snippet explains the flow control:

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/54lesson.sh and wait for the MySQL prompt to start-up.