IF

IF statements are 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 it ends with 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; an empty statement_list is not allowed.

In stored procedures, IF ... END IF blocks must end with a semicolon (;). Like other flow control constructs, you can nest IF blocks inside other flow control constructs such as other IF statements. Every IF must end with its own END IF;. You can improve the readability of nested flow control blocks by indenting the code, as shown in the following example:

obclient> DELIMITER //

obclient> 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 internal IF statement is computed only when n does not equal m.