IF

The IF statement is used to construct basic conditional statements.

The syntax of the IF statement is as follows:

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

The IF statement can contain THEN, ELSE, and ELSEIF clauses and is terminated by END IF.

If the specified search_condition evaluates to True, the corresponding THEN or ELSEIF clause statement list is executed. If no search_condition matches, the statement_list in the ELSE clause is executed.

Each statement_list consists of one or more SQL statements; empty statement_list is not allowed.

In stored programs, the IF ... END IF block must be terminated with a semicolon (;). Like other flow control constructs, the IF ... END IF block can be nested within other control statements, including IF statements. Each IF must be terminated with its own END IF;. You can indent nested flow control blocks to make them easier to read, as shown in the following example:

DELIMITER //

CREATE FUNCTION comp_fuc (n INT, m INT)
   RETURNS VARCHAR(50)

   BEGIN
     DECLARE x VARCHAR(50);

     IF n = m THEN SET x = 'equal';
     ELSE
       IF n > m THEN SET x = 'greater than';
       ELSE SET x = 'less than';
       END IF;

     END IF;

     SET x = CONCAT(n, ' ', x, ' ', m, '.');
     RETURN x;
   END //
Query OK, 0 rows affected

In the preceding example, the inner IF statement is only evaluated when n is not equal to m.