MySQL Controller flow function

There are four types of control flow functions in MySQL

Name	Description
CASE	Case operator
IF()	If/else Construct
IFNULL()	Null If/else Construct
NULLIF()	Return NULL If expr1 = expr2

1. Case process

 CaseValue when [Compare_value]  ThenResult[When [Compare_value]  ThenResult ...][ELSE Result] END  Case  when [condition]  ThenResult[When [condition]  ThenResult ...][ELSE Result] END 

First case: return result when value = Compare_value

Second case: Return result when condition is true

If there is no match, result will be returned after else, if no else statement will return null

eg

Mysql> SELECT  Case 1  when 1  Then ' One'     -      when 2  Then ' Both' 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

2. If process--if (EXPR1,EXPR2,EXPR3)

If Expr1 is true, it returns EXPR2, otherwise returns EXPR3

Mysql> SELECT IF(1>2,2,3);  - 3MySQL> SELECT IF(1<2,'Yes','No');  - 'Yes'MySQL> SELECT IF(STRCMP ('Test','test1'),'No','Yes');  - 'No'

3, Ifnull (EXPR1,EXPR2) Process---if EXPR1 is not NULL, return EXPR1, otherwise return EXPR2

Mysql> SELECTIfnull (1,0);  - 1MySQL> SELECTIfnull (NULL,Ten);  - TenMySQL> SELECTIfnull (1/0,Ten);  - TenMySQL> SELECTIfnull (1/0,'Yes');  - 'Yes'

4, Nullif (EXPR1,EXPR2) Process---if EXPR1 = EXPR2 returns NULL, otherwise returns EXPR1; equivalent to case when expr1 = Expr2 then null else EXPR1 end

MySQL>SELECTnullif(1,1);          - NULL MySQL > SELECT Nullif (1,2);          - 1

MySQL Controller flow function