Differences between IFNULL, IF, and CASE in mysql _ MySQL

Description of the differences between IFNULL, IF, and CASE in mysql: bitsCN.com assume that the status field of a data table is of the varchar type and has the following values: NULL, pending, pending refund, refund, and cancel.
We know the order whose query status is cancel. the SQL statement can be written as follows: SELECT o. oid, o. moneyreceip, o. moneyget, o. thecurrency, o. status FROM qorder o WHERE o. status = 'cancel'
The SQL statement can query the correct data, but when we want to query orders in a non-cancel status, it may be troublesome because the status field is NOT set to NOT NULL, therefore, the status value of most orders is NULL. in this case, the data queried using '<>' is incorrect, and only non-empty data except the status cancel is queried, NULL is not found. SELECT o. oid, o. moneyreceept, o. moneyget, o. thecurrency, o. status FROM qorder o WHERE o. status <> 'cancel' reason: NULL value operation
The NULL value may be strange until you get used to it. In concept, NULL means "no value" or "unknown value", and it is regarded as a different value. To test NULL, you cannot use arithmetic comparison operators such as =, <或!=。为了说明它，试试下列查询：mysql> SELECT 1 = NULL, 1 <> NULL, 1 <NULL, 1> NULL;
+ ---------- + ----------- + ---------- +
| 1 = NULL | 1 <> NULL | 1 <NULL | 1> NULL |
+ ---------- + ----------- + ---------- +
| NULL |
+ ---------- + ----------- + ---------- +
Obviously, you cannot get meaningful results through these comparisons. On the contrary, use the is null and is not null operators: mysql> SELECT 1 is null, 1 is not null;
+ ----------- + --------------- +
| 1 is null | 1 is not null |
+ ----------- + --------------- +
| 0 | 1 |
+ ----------- + --------------- +
Note that in MySQL, 0 or NULL means false, while other values mean true. The default true value of Boolean operations is 1. Based on the above NULL value operation results, this method is used to solve the problem: SELECT o. oid, o. moneyreceip, o. moneyget, o. thecurrency, o. status FROM qorder o where ifnull (o. status, 'pending') <> 'cancel' learning: 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.0000
Mysql> select IFNULL (1/0, 'yes');-> 'yes' IF (expr1, expr2, expr3) IF expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), IF () returns expr2; otherwise, expr3 is returned. 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'

(1) CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result...] [ELSE result] END // I haven't thought about it yet. if you know it, you can leave a message,
(2) case when [condition] THEN result [WHEN [condition] THEN result...] [ELSE result] END // This interview was asked and can be used as a condition for judgment
Result (1), where value = compare-value. If the first condition in (2) is true, result is returned. If no matching result value exists, the result after ELSE is returned. If no ELSE part exists, 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

In the above example, I passed the test in mysql5.2.6 ============================ ========================================================== ================ PS: the above is the reprinted content, and I think it is good to turn around, the CASE... WHEN... THEN, IF, and IFNULL are commonly used functions such as SELECT id, username, CASE gender WHEN 1 THEN "male" WHEN 2 THEN "female" end from t_people; SELECT B. id, B. book_name, CASE B. type WHEN 1 THEN "computer" WHEN 2 THEN "accounting" ELSE "other" end from book B; bitsCN.com