MySQL IF Statement

The IF statement is used in stored programs that implement the basic conditional construct in MySQL. Based on a certain condition, it allows us to execute a set of SQL statements. It returns one of the three values True, False, or NULL.

We can use this statement in three ways IF-THEN, IF-THEN-ELSE, IF-THEN-ELSEIF-ELSE clauses, and can terminate with END-IF. Let us see each of these statements in detail.

IF-THEN Statement

This statement executes a set of SQL queries based on certain conditions or expressions. The syntax of the IF-THEN statement is as follows:

In the above syntax, we have to specify a condition for executing the code. If the statement evaluates to true, it will execute the statement between IF-THEN and END-IF. Otherwise, it will execute the statement following the END-IF.

Example

The IF...ENDIF block executes with stored programs and terminates with a semicolon, as shown in the below example.

Next, take two variables and set the value for both as below:

Now, call the stored procedure function to check the output.

We will get the following output:

IF-THEN-ELSE Statement

If we want to execute other statements when the condition specifies in the IF block does not evaluate to true, this statement can be used. The syntax of an IF-THEN-ELSE statement is given below:

In the above syntax, we have to specify a condition for executing the code. If the statement evaluates to true, it will execute the statement between IF-THEN and ELSE. Otherwise, it will execute the statement following the ELSE and END-IF.

Let us modify the above myResult() stored procedure. So, first, remove myResult() stored procedure by using the command below:

Next, write the new code for this, as shown below:

Next, create two variables and set the value for both as below:

Now, call the stored procedure function to get the output.

It will give the following output:

IF-THEN-ELSEIF-ELSE Statement

If we want to execute a statement based on multiple conditions, this statement can be used. The syntax of the IF-THEN-ELSE statement is given below:

In the above syntax, if the condition becomes true, it will execute the IF-THEN branch. Otherwise, it will evaluate elseif-condition. When the elseif-condition becomes true, it will execute the elseif-statement. If this condition is also false, it will evaluate the next elseif-condition. Thus, here we will evaluate multiple elseif-condition, and if any condition in the IF and ELSE-IF does not becomes true, it will execute the statement of the ELSE branch.

Let us modify the above myResult() stored procedure. So, first, remove myResult() stored procedure by using the command below:

Next, write the new code for this, as shown below:

Next, create two variables and set the value for both as below:

Now, call the stored procedure function to get the output.

It will give the following output: