MySQL IF Statement with Examples


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)
BEGIN
    DECLARE message VARCHAR(100) DEFAULT "";
    IF a > b THEN
        SET message = CONCAT("The first parameter is greater than the second parameter.", a, ">", b);
        ELSEIF a < b THEN
            SET message = CONCAT("The first parameter is less than the second parameter.", a, "<", b);
        ELSE
            SET 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)
BEGIN
    DECLARE max INT DEFAULT 0;
    DECLARE message VARCHAR(100) DEFAULT "";
    SET max = a;
    IF b > max THEN
        SET max = b;
    END IF;
    IF c > max THEN
        SET 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)
BEGIN
   DECLARE min INT DEFAULT 0;
   DECLARE message VARCHAR(100) DEFAULT "";
   SET min = a;
   IF b < min THEN
     SET min = b;
   END IF;
   IF c < min THEN
     SET 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 MySQLIF 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.

Leave a Reply

Your email address will not be published. Required fields are marked *