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.
Contact Us