The pitfall record of case when judging NULL value in MySQL

Preface

Today, when developing a program, I used the case when syntax to extract data from MySQL for judgment. During the use, I encountered a small problem when judging the NULL value.

The case when statement in MySQL is similar to the switch statement in Java, which is more flexible, but the handling of Null in MySQL is a bit special.

Mysql case when syntax:

Syntax 1:

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

Syntax 2:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

Note: There is a difference between these two syntaxes, the difference is as follows:

1: The first syntax: case_value must be an expression, such as userid%2=1 or username is null. This syntax cannot be used to test for NULL.

2: The second syntax CASE does not require variables or expressions. When executed directly, the conditions after each WHEN are evaluated and executed if they are met.

Case practice:

The table structure is as follows: a value is null, b value is 1

mysql> SELECT NULL AS a, 1 AS b;
+------+---+
| a | b |
+------+---+
| NULL | 1 |
+------+---+

Now implement, if the value of a is null, then take the value of b, otherwise take the value of a

Method 1: ifnull usage

SELECT
    IFNULL(a, b) AS new,
    a,
    b
FROM
    -- Create a temporary table: the value of a is null, and the value of b is 1
    (SELECT NULL AS a, 1 AS b) tmp;

Method 2: case when usage

SELECT
    (
        CASE
        WHEN a IS NULL THEN
            b
        ELSE
            a
        END
    ) AS new,
    a,
    b
FROM
    (SELECT NULL AS a, 1 AS b) tmp;

We found that the result was wrong. The value of new was null instead of 1 as we wanted.

Why does this error occur? This is caused by mixing the first syntax with the second syntax. There are two values ​​of commission_pct after case: a real value or null, and commission_pct is null after when also has two values: true or false. Therefore, when the value after case is null, it can never match true or false, so the output is not null.

If you must use syntax 1 in this case, you can rewrite it as follows:

SELECT
    (
        CASE a IS NULL
        WHEN TRUE THEN b
        ELSE         
        END
    ) AS new,
    a,
    b
FROM
    (SELECT NULL AS a, 1 AS b) tmp;

You can also use syntax 2 to write:

SELECT
    (
        CASE
        WHEN a is NULL THEN b 
        ELSE 
        END
    ) AS new,
    a,
    b
FROM
    (SELECT NULL AS a, 1 AS b) tmp;

Note another case where errors may exist but are not easy to spot:

SELECT
    (
        CASE
        WHEN NULL THEN b
        ELSE         
        END
    ) AS new,
    a,
    b
FROM
    (SELECT NULL AS a, 1 AS b) tmp;

It seems that there is no problem, but actually there is a problem. The reason for the problem is that null cannot be judged using =. Simply put, the value of the case expression in syntax 1 is compared with the value of the subsequent when using =, but in MySQL, is or is not must be used.

Summarize:

1: Syntax 1 calculates the expression value after case and then uses "=" to judge whether the value of the when condition is equal. If they are equal, enter the branch.

2: Syntax 2 does not require an expression after case. You can directly evaluate the condition value after when. If it is true, enter.

This is the end of this article about case when judgment on NULL values ​​in MySQL. For more relevant content about MySQL case when judgment on NULL values, please search previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!