MySQL – IF, IF-THEN, IF-THEN-ELSE and IF-THEN-ELSEIF-ELSE Statement

Decision Making in scripting is similar to decision-making in real life. In scripting, DBAs face some situations where they want a certain part of the script to be executed when some condition is fulfilled.

The MySQL IF statement is used for validating a condition. The IF statement returns the statements if the condition is TRUE. In another word; the MySQL IF statement is used to execute a bunch of SQL statements based upon a pre-defined condition.

Note: MySQL IF statement is different from the IF() function.

The IF statement has three ways:

• IF-THEN statement
• IF-THEN-ELSE statement
• IF-THEN-ELSEIF- ELSE statement

Parameters	MySQL IF-THEN statement	MySQL IF-THEN-ELSE statement	MySQL IF-THEN-ELSE IF-ELSE statement
Definition	The IF-THEN statement is used to execute a set of SQL statements based upon a pre-defined condition.	When the condition checks to be TRUE, the statements between IF-THEN and ELSE execute. On the other hand, the statements (else-statements) between the ELSE and END IF execute.	MySQL IF-THEN-ELSE IF-ELSE statement could be used to execute statements conditionally based on multiple conditions.
Syntax	IF condition THEN   statements; END IF;	IF condition THEN statements; ELSE else-statements; END IF;	IF condition THEN statements; ELSEIF elseif-condition THEN elseif-statements; ELSE else-statements; END IF;
Parameters Used	condition – It is used to define the condition that will be evaluated. statements – It is a set of SQL statements based upon a pre-defined condition.	condition – It is used to define the condition that will be evaluated. statements – It is a set of SQL statements executed when a pre-defined condition is true. else-statements – It is a set of SQL statements executed when a pre-defined condition is false.	condition – It is used to define the condition that will be evaluated. statements – It is a set of SQL statements executed when a pre-defined condition is true. else if-condition – It is used to define the condition that will be evaluated when the first condition is false. else-statements – It is a set of SQL statements executed when the pre-defined condition is false.

MySQL IF-THEN Statement:

The IF-THEN statement is used to execute a set of SQL statements based upon a pre-defined condition.

Syntax:

IF condition THEN  

statements;

END IF;

Parameters Used:

• condition – It is used to define the condition that will be evaluated.
• statements – It is a set of SQL statements based upon a pre-defined condition.

Example:

DELIMITER $$
CREATE PROCEDURE Geekdemo( num1 INT)
BEGIN
  IF num1 < 5000
THEN
 RETURN 'Input is less than 5000.'
  END IF;
END; $$
DELIMITER;

//To check output
CALL Geekdemo();

// Input- 4500

Output:

Input is less than 5000.

MySQL IF-THEN-ELSE Statement:

When the condition checks to be TRUE, the statements between IF-THEN and ELSE execute. On the other hand, the statements (else-statements) between the ELSE and END IF execute.

Syntax:

IF condition THEN

 statements;

ELSE

 else-statements;

END IF;

Parameters Used:

• condition – It is used to define the condition that will be evaluated.
• statements – It is a set of SQL statements executed when a pre-defined condition is true.
• else-statements – It is a set of SQL statements executed when the pre-defined condition is false.

Example:

DELIMITER $$
CREATE PROCEDURE Geekdemo( num1 INT)
BEGIN
 IF num1 < 5000
THEN
RETURN 'Input is less than 5000.'
ELSE
RETURN 'Input is more than 5000.'
 END IF;
END; $$
DELIMITER;

// To check output
CALL Geekdemo();

// Input
5500

Output:

Input is more than 5000.

MySQL IF-THEN-ELSE IF-ELSE Statement:

MySQL IF-THEN-ELSE IF-ELSE statement could be used to execute statements conditionally based on multiple conditions.

Syntax:

IF condition THEN

 statements;

ELSEIF elseif-condition THEN

 elseif-statements;

ELSE

 else-statements;

END IF;

Parameters Used:

• condition – It is used to define the condition that will be evaluated.
• statements – It is a set of SQL statements executed when a pre-defined condition is true.
• elseif-condition – It is used to define the condition that will be evaluated when the first condition is false.
• else-statements – It is a set of SQL statements executed when the pre-defined condition is false.

Example:

DELIMITER $$
CREATE PROCEDURE Geekdemo( num1 INT)
BEGIN
IF num1 < 5000
THEN
RETURN 'Input is less than 5000.'
ELSEIF
num1 = 5000 THEN
RETURN 'Input is equal to 5000.'
ELSE
RETURN 'Input is more than 5000.'
END IF;
END; $$
DELIMITER;
To check output
CALL Geekdemo();
Input
5000

Output:

Input is equal to 5000.