SELECT if_case process function, selectif_case Process

SELECT if_case process function, selectif_case Process

Common flow functions in DQL if_case

--- Process functions implement conditional Selection in an SQL statement

 

Employee salaries are classified as follows:

mysql> create table salary_tab(userid int not null primary key,salary dec(9,2));Query OK, 0 rows affected (0.04 sec)mysql> insert into salary_tab values(1,1000);mysql> insert into salary_tab values(2,2000);mysql> insert into salary_tab values(3,3000);mysql> insert into salary_tab(userid) values(4);mysql> insert into salary_tab values(5,1000);mysql> select * from salary_tab;+--------+---------+| userid | salary  |+--------+---------+|      1 | 1000.00 ||      2 | 2000.00 ||      3 | 3000.00 ||      4 |    NULL ||      5 | 1000.00 |+--------+---------+5 rows in set (0.00 sec)

 

1. IF (expr1, expr2, expr3)

Determine whether the first parameter expr1 is TRUE (expr1 <> 0 and expr1 <> NULL ):

Success. The second parameter expr2 is returned.

Failed. The third parameter expr3 is returned.

The returned values can be numbers, strings, and column values.

Mysql> select if (null, 'non-null', 'null'); + --------------------- + | if (null, 'non-null', 'null ') | + ------------------------- + | null | + ------------------------- + mysql> select userid, if (salary> 2000, 'high', 'low') from salary_tab; + -------- + ---------------------------- + | userid | if (salary> 2000, 'high', 'low ') | + -------- + -------------------------- + | 1 | low | 2 | low | 3 | high | 4 | low | 5 | low | + -------- + ------------------------------ + mysql> select if (1> 2, 2, 3), if (1 <2, 'yes', 'no'); + ------------- + -------------------- + | if (1> 2, 2, 3) | if (1 <2, 'yes', 'no') | + ------------- + ------------------ + | 3 | yes | + --------------- + -------------------- +

 

 

2. IFNULL (expr1, expr2)

Determine whether the first parameter expr1 is NULL:

If expr1 is not null, expr1 is returned directly;

If expr1 is null, the second parameter expr2 is returned.

It is often used in arithmetic expression calculation and group functions to convert null values (return values are numbers or strings)

mysql> select ifnull(salary,0) from salary_tab;+------------------+| ifnull(salary,0) |+------------------+|          1000.00 ||          2000.00 ||          3000.00 ||             0.00 ||          1000.00 |+------------------+mysql> select ifnull(1/0,'yes');+-------------------+| ifnull(1/0,'yes') |+-------------------+| yes               |+-------------------+

 

NULLIF (expr1, expr2): If the two parameters are equal, NULL is returned. Otherwise, the value of the first parameter expr1 is returned.

mysql> select nullif(1,1),nullif(123,321);+-------------+-----------------+| nullif(1,1) | nullif(123,321) |+-------------+-----------------+|        NULL |             123 |+-------------+-----------------+

 

 

3. Implement the "if-then-else" logic computing function in SQL statements

There are two forms: simple case and searched case.

1) syntax structure of simple case:

CASE  value    WHEN  [compare_value] THEN  result    [WHEN [compare_value] THEN  result ...]     [ELSE  result]  END

Semantics:

Compare the value after case with compare_value after each when clause respectively:

If the value is equal to the value after a when clause, the result is returned;

If the value is not equal to the value after all the when clauses, the value following the else clause is returned;

If no else part exists, null is returned.

Note:

① Value can be a literal, expression, or column name.

② The Data Type of the CASE expression depends on the type of the expression following then or else.

The types must be the same (implicit type conversion can be attempted); otherwise, an error occurs.

Mysql> select userid, case salary-> when 1000 then 'Low'-> when 2000 then 'med '-> when 3000 then 'high'-> else 'invalid value 'end salary_grade-> from salary_tab; + -------- + -------------- + | userid | salary_grade | + -------- + -------------- + | 1 | low | 2 | med | 3 | high | 4 | invalid value | 5 | low | + -------- + -------------- +

 

2) syntax structure of searched case:

CASE    WHEN [condition] THEN result    [WHEN [condition] THEN result ...]    [ELSE result]  END

Semantics:

If the condition after a when clause is true, the result is returned;

If the condition after all the when clauses is not true, the value following the else clause is returned;

If no else part exists, null is returned.

Mysql> select userid, case-> when salary <= 1000 then 'Low'-> when salary = 2000 then 'med '-> when salary> = 3000 then 'high'-> else 'invalid value' end salary_grade-> from salary_tab; + -------- + -------------- + | userid | salary_grade | + -------- + -------------- + | 1 | low | 2 | med | 3 | high | 4 | invalid value | 5 | low | + -------- + -------------- +