Discussion on ifnull of function similar to nvl of function in Mysql

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL () returns expr1, otherwise it returns expr2. IFNULL () returns a number or string value, depending on the context in which it is used.

mysql> select IFNULL(1,0);
    -> 1
mysql> select IFNULL(0,10);
    -> 0
mysql> select IFNULL(1/0,10);
    -> 10
mysql> select IFNULL(1/0,'yes');
    -> 'yes'
 
IF(expr1,expr2,expr3) 

If expr1 is TRUE (expr1) < > 0 and expr1 < > NULL), then IF () returns expr2, otherwise it returns expr3. IF () returns a number or string value, depending on the context in which it is used.

mysql> select IF(1>2,2,3);
    -> 3
mysql> select IF(1<2,'yes','no');
    -> 'yes'
mysql> select IF(strcmp('test','test1'),'yes','no');
    -> 'no'

expr1 is evaluated as an integer value, which means that if you are testing floating-point or string values, you should use a comparison operation to do so.

mysql> select IF(0.1,1,0);
    -> 0
mysql> select IF(0.1<>0,1,0);
    -> 1

In the first case above, IF (0.1) returns 0 because 0.1 is converted to an integer value, resulting in testing IF (0). This may not be what you expected. In the second case, the comparison tests the original floating-point value to see if it is non-zero, and the result of the comparison is used as an integer.

CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END 
 　  
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END 

Version 1 returns result, where value=compare-value. In version 2, if the first condition is true, result is returned. If there is no matching result value, the result is returned result after ELSE. If there is no ELSE section, NULL is returned.

mysql> SELECT CASE 1 WHEN 1 THEN "one" WHEN 2 THEN "two" ELSE "more" END;
    -> "one"
mysql> SELECT CASE WHEN 1>0 THEN "true" ELSE "false" END;
    -> "true"
mysql> SELECT CASE BINARY "B" when "a" then 1 when "b" then 2 END;
-> NULL