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