How to Use the MySQL IF Statement
In this tutorial, you'll learn how to use the MySQL IF statement. In MySQL, IF is a flow control statement and allows you to execute a block of SQL statements when a condition is TRUE, and execute a different block of SQL statements when the condition evaluates to FALSE.
MySQL IF Syntax
The syntax of the IF statement is as follows:
IF condition THEN statement_list
[ELSEIF condition THEN statement_list]
...[ELSE statement_list]
END IF
Examples
Example 1: Compare 2 Numbers
The following stored procedure illustrates how to compare two numbers using the IF statement:
CREATE PROCEDURE SP_COMPARE(a INT, b INT)BEGINDECLARE message VARCHAR(100) DEFAULT "";IF a > b THENSET message = CONCAT("The first parameter is greater than the second parameter.", a, ">", b);ELSEIF a < b THENSET message = CONCAT("The first parameter is less than the second parameter.", a, "<", b);ELSESET message = CONCAT("Values of both parameters are the same.", a, "=", b);END IF;SELECT message AS "Message";END;
Result:
When a = 100 and b = 200:
The first parameter is less than the second parameter.100<200
When a = 300 and b = 200:
The first parameter is greater than the second parameter.300>200
Example 2: Find the Largest Value
The following stored procedure illustrates how to find the maximum value in a set of three numbers using the IF statement:
CREATE PROCEDURE SP_FIND_MAX(a INT, b INT, c INT)BEGINDECLARE max INT DEFAULT 0;DECLARE message VARCHAR(100) DEFAULT "";SET max = a;IF b > max THENSET max = b;END IF;IF c > max THENSET max = c;END IF;SET message = CONCAT(max, " is the maximum value.");SELECT message AS "Message";END;
Result:
When a = 100, b = 200, and c = 300:
300 is the maximum value.
Example 3: Find the Smallest Value
The following stored procedure illustrates how to find the minimum value in a set of three numbers using the IF statement:
CREATE PROCEDURE SP_FIND_MIN(a INT, b INT, c INT)BEGINDECLARE min INT DEFAULT 0;DECLARE message VARCHAR(100) DEFAULT "";SET min = a;IF b < min THENSET min = b;END IF;IF c < min THENSET min = c;END IF;SET message = CONCAT(min, " is the minimum value.");SELECT message AS "Message";END;
Result:
When a = 200, b = 100, and c = 100:
100 is the minimum value.
In this tutorial, you've learned how to use the IF statement in MySQL. IF is a flow control statement and allows you to execute a block of SQL statements when a condition is TRUE, and execute another block of SQL statements when the condition evaluates to FALSE.