Umesh Chandra

The control flow function evaluates the condition specified in it. The output generated by them can be a true, false, static value or column expression. We can use the control flow functions in the SELECT, WHERE, ORDER BY, and GROUP BY clause.

Flow Control Operators:

Name	Description
CASE	Case Operator
IF()	If/else construct
IFNULL()	Null if/else construct
NULLIF()	Return NULL if expr1 = expr2

---

1. CASE:

Syntax:

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

The first CASE syntax returns the result for the first value=compare_value comparison that is true.

The second syntax returns the result for the first condition that is true. If no comparison or condition is true, the result after ELSE is returned, or NULL if there is no ELSE part.

The return type of a CASE expression result is the aggregated type of all result values:

• If all types are numeric, the aggregated type is also numeric:
• If at least one argument is double precision, the result is double precision.
• If at least one argument is DECIMAL, the result is DECIMAL.
• If all types are BIT, the result is BIT.
• If all types are character string (CHAR or VARCHAR), the result is VARCHAR with maximum length determined by the longest character length of the operands.

Example:

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 'c'
    ->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
        -> NULL

---

2. IF() :

Syntax:

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 IS NOT NULL),

• Note: <> is same as NOT EQUAL TO operator

IF() returns expr2.

Otherwise, it returns expr3.

Note: IF() is not an Conditional Statement, IF() is an function There is also an IF Statement, which differs from the IF() function

Example:

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

---

3. IFNULL() :

Syntax:

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.

Example:

mysql> SELECT IFNULL(1,0);
        -> 1
mysql> SELECT IFNULL(NULL,10);
        -> 10
mysql> SELECT IFNULL(1/0,10);
        -> 10
mysql> SELECT IFNULL(1/0,'yes');
        -> 'yes'

---

4. NULLIF() :

Syntax:

NULLIF(expr1,expr2)

Returns NULL if expr1 = expr2 is true,

otherwise returns expr1.

This is the same as 

CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

The return value has the same type as the first argument.

Example:

mysql> SELECT NULLIF(1,1);
        -> NULL
mysql> SELECT NULLIF(1,2);
        -> 1

Note: MySQL evaluates expr1 twice if the arguments are not equal.