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:

snippet

IF condition THEN 
   statements;
END IF;

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.

snippet

DELIMITER $$
CREATE PROCEDURE myResult(original_rate NUMERIC(6,2),OUT discount_rate NUMERIC(6,2))
     NO SQL
      BEGIN
         IF (original_rate>200) THEN
            SET discount_rate=original_rate*.5;
         END IF;
	     select discount_rate;
     END$$
DELIMITER $$;

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

snippet

mysql> set @p = 600;
mysql> set @dp = 500;

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

snippet

mysql> call myResult(@p, @dp)

We will get the following output:

MySQL IF Statement

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:

snippet

IF condition THEN
   statements;
ELSE
   else-statements;
END IF;

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:

snippet

Mysql> DROP procedure myResult;

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

snippet

DELIMITER $$
CREATE PROCEDURE myResult(original_rate NUMERIC(6,2),OUT discount_rate NUMERIC(6,2))
     NO SQL
      BEGIN
         IF (original_rate>200) THEN
            SET discount_rate=original_rate*.5;
		ELSE
            SET discount_rate=original_rate;
         END IF;
	     select discount_rate;
     END$$
DELIMITER ;

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

snippet

mysql> set @p = 150;
mysql> set @dp = 180;

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

snippet

mysql> call myResult(@p, @dp)

It will give the following output:

MySQL IF Statement

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:

snippet

IF condition THEN
   statements;
ELSEIF elseif-condition THEN
   elseif-statements;
...
ELSE
   else-statements;
END IF;

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:

snippet

Mysql> DROP procedure myResult;

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

snippet

DELIMITER $$
CREATE PROCEDURE myResult(original_rate NUMERIC(6,2),OUT discount_rate NUMERIC(6,2))
     NO SQL
      BEGIN
         IF (original_rate>500) THEN
            SET discount_rate=original_rate*.5;
		ELSEIF (original_rate<=500 AND original_rate>250) THEN
            SET discount_rate=original_rate*.8;
		ELSE
            SET discount_rate=original_rate;
         END IF;
	     select discount_rate;
     END$$
DELIMITER ;

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

snippet

mysql> set @p = 150;
mysql> set @dp = 150;

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

snippet

mysql> call myResult(@p, @dp)

It will give the following output:

MySQL IF Statement