IF Syntax

The IF statement for stored programs implements a basic conditional construct.Note

There is also an IF() function, which differs from the IF statement described here. See , "Control Flow Functions". The IF statement can have THEN, ELSE, and ELSEIF clauses, and it is terminated with END IF.

If the search_condition evaluates to true, the corresponding THEN or ELSEIF clause statement_list executes. If no search_condition matches, the ELSE clause statement_list executes.

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

An IF ... END IF block, like all other flow-control blocks used within stored programs, must be terminated with a semicolon, as shown in this example:

DELIMITER //
CREATE FUNCTION SimpleCompare(n INT, m INT)
 RETURNS VARCHAR(20)
 BEGIN
 DECLARE s VARCHAR(20);
 IF n > m THEN SET s = '>';
 ELSEIF n = m THEN SET s = '=';
 ELSE SET s = '<';
 END IF;
 SET s = CONCAT(n, ' ', s, ' ', m);
 RETURN s;
 END //
DELIMITER ;

As with other flow-control constructs, IF ... END IF blocks may be nested within other flow-control constructs, including other IF statements. Each IF must be terminated by its own END IF followed by a semicolon. You can use indentation to make nested flow-control blocks more easily readable by humans (although this is not required by MySQL), as shown here:

DELIMITER //
CREATE FUNCTION VerboseCompare (n INT, m INT)
 RETURNS VARCHAR(50)
 BEGIN
 DECLARE s VARCHAR(50);
 IF n = m THEN SET s = 'equals';
 ELSE
 IF n > m THEN SET s = 'greater';
 ELSE SET s = 'less';
 END IF;
 SET s = CONCAT('is ', s, ' than');
 END IF;
 SET s = CONCAT(n, ' ', s, ' ', m, '.');
 RETURN s;
 END //
DELIMITER ;

In this example, the inner IF is evaluated only if n is not equal to m.

Retornar