Conditional Constructs - IF...ELSEIF..ELSE

This construct is used to evaluate some complex conditions and based on the results execute different set of statements. The basic syntax is

IF <conditions> THEN
     <operations to be performed>
ELSEIF <conditions> THEN
     <operations to be performed>
ELSE
     <operations to be performed>
END IF;

Here multiple conditions can be checked in ELSEIF. ELSE takes care of the default execution i.e in case no condition is satisfied then what to do.
In IF, ELSE can be skipped if no default execution is required.

For example the code snippet discussed for Searched CASE can be re-written using IF...ELSEIF..ELSE as :

DELIMITER $$

CREATE PROCEDURE `getRange`(num int)
BEGIN

    IF num > 0 AND num <=99 THEN
        SELECT 'Tens';
    ELSEIF num >=100 AND num <=999 THEN
        SELECT 'Hundreds';
    ELSE
        SELECT 'out of range';
    END IF;
END$$

DELIMITER ;

The output will be same.
If no steps are to be executed in case none of the conditions are satisfied we can simply change the code to

DELIMITER $$

CREATE PROCEDURE `getRange`(num int)
BEGIN

    IF num > 0 AND num <=99 THEN
        SELECT 'Tens';
    ELSEIF num >=100 AND num <=999 THEN
        SELECT 'Hundreds';  
    END IF;
END$$

DELIMITER ;

Now

call getRange(5000)

Will not return any result.

Which one is better ?

• More readable
• Efficient

If should be used when :

• Conditions are too complex to be managed in CASE
• No default operation is required i.e. nothing is to be done if none of the values are satisfied.

Previous
Conditional Constructs - CASE