Control Flow Functions

 Example:-

SELECT                --- here (below) condition is defined first then it is matched with when statement
    CASE 2               --- means condition here 2 is condition to be checked
    WHEN 1 THEN 'one'    --- 2 will be matched with 1 if matched then one will be output
    WHEN 2 THEN 'two'     --- 2 (of CASE 2) will be matched with 2(of WHERE) .if matched two will be output
    WHEN 3 THEN 'three'   --- 2 will be matched with 3
    ELSE 'others'         --- if no match  found others will be output
END;                  --- end of the loop


Query output : 'two'

 Example :-

SELECT                     --- here (below) condition is not defined first. It is checked with when statement
    CASE WHEN 1>0 THEN 'true'  --- checking if 1 is greater than 0 if it is, true will outputted
    ELSE 'false'           --- else false will outputted
END;              --- end of the loop


Query output : 'true'
 
Example :-

SELECT
    CASE BINARY 'B'     --- here condition is checked against binary means here B and b are not equal.
    WHEN 'a' THEN 1    --- if condition is not matched then null will outputted
    WHEN 'b' THEN 2
END;

Query output : NULL

These are three variations of case statement in MySQL.

The if() function:-

If expr1 is TRUE  then IF() returns expr2; otherwise it returns expr3.

IF() returns a numeric or string value, depending on the context in which it is used.

Example:-

SELECT IF( 1 > 2, 2, 3 ); ---here first condition is checked i.e. if 1 is greater than 2, if it is 2 is outputted else 3 is outputed


Query output : 3

Example :-

SELECT IF( 1< 2, 'yes', 'no' );

Query output : 'yes'

If only one of expr2 or expr3 is explicitly NULL, the result type of the IF() function is the type of the non-NULL expression.

The default return type of IF() is calculated as follows;- expr2 or expr3 returns a string then return type is string expr2 or expr3 returns a floating-point value then return type is floating-point expr2 or expr3 returns an integer then return type is integer