MySQL – IF, IF-THEN, IF-THEN-ELSE and IF-THEN-ELSEIF-ELSE Statement
Last Updated :
22 Sep, 2022
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.
Share your thoughts in the comments
Please Login to comment...