The MySQL IF ELSE statement explained

Learn how to create conditional MySQL functions and stored procedures with the IF statement

Posted on September 17, 2021


The MySQL database provides you with an IF statement that you can use in stored procedures and functions.

The IF statement allows you to create conditional queries that will be executed only when a specified condition has been met.

For example, suppose you want to check if a TEXT value length is greater than 5 or not.

You can create a charLength() function with an IF statement as shown below:

DELIMITER //

CREATE FUNCTION charLength(str TEXT)
  RETURNS TEXT DETERMINISTIC
  BEGIN
    DECLARE result TEXT DEFAULT CONCAT(str, " length is 5 or less");
    IF CHAR_LENGTH(str)> 5 THEN
      SET result = CONCAT(str, " length is greater than 5 ");
    END IF;
    RETURN result;
  END //
  
DELIMITER ;

Then, you can use the SELECT statement to call the function as follows:

SELECT charLength("Hello"),
  charLength("Morning");

The returned output will be as shown below:

+---------------------------+-----------------------------------+
| charLength("Hello")       | charLength("Morning")             |
+---------------------------+-----------------------------------+
| Hello length is 5 or less | Morning length is greater than 5  |
+---------------------------+-----------------------------------+
1 row in set (0.00 sec)

The IF statement can be combined with the ELSE statement to provide an alternative query to execute when the IF statement condition is not met.

For example, suppose you want to compare two INT values and print a text that says x is larger than y or x is smaller than y.

You can create a function in MySQL and create a conditional IF ELSE statement as follows:

DELIMITER //

CREATE FUNCTION compare(x INT, y INT)
  RETURNS TEXT DETERMINISTIC
  BEGIN
    DECLARE result TEXT;
    IF x > y THEN
      SET result = CONCAT(x, " is larger than ", y);
    ELSE
      SET result = CONCAT(x, " is smaller than ", y);
    END IF;
    RETURN result;
  END //
  
DELIMITER ;

Now you can call the function using the SELECT statement as follows:

SELECT compare(1, 2), compare(10, 5);

The result set will be as shown below:

+---------------------+---------------------+
| compare(1, 2)       | compare(10, 5)      |
+---------------------+---------------------+
| 1 is smaller than 2 | 10 is larger than 5 |
+---------------------+---------------------+
1 row in set (0.00 sec)

And that’s how you create an IF ELSE statement in MySQL functions.

When you have more than two conditions, you can also use multiple ELSEIF statements where you define each condition you want to check in a sequence.

Here’s another example function where you also need to check if the two INT values are equal:

DELIMITER //

CREATE FUNCTION compare(x INT, y INT)
  RETURNS TEXT DETERMINISTIC
  BEGIN
    DECLARE result TEXT;
    IF x > y THEN
      SET result = CONCAT(x, " is larger than ", y);
    ELSEIF x < y THEN
      SET result = CONCAT(x, " is smaller than ", y);
    ELSE
      SET result = CONCAT(x, " and ", y, " are equal");
    END IF;
    RETURN result;
  END //
  
DELIMITER ;

Now the function will run the ELSE statement only when the value of x is neither larger or smaller than the value of y.

And that’s how you can use IF, ELSEIF, and ELSE statements in MySQL function and stored procedure.

Related articles:

Level up your programming skills

I'm sending out an occasional email with the latest programming tutorials. Drop your email in the box below and I'll send new stuff straight into your inbox!

No spam. Unsubscribe anytime.