mysql - Using a Case Statement With IS NULL and IS NOT NULL

Question

SELECT 
    userid, 
    userName,
    CASE userName
    WHEN (userName IS NULL) THEN 'was null'
        WHEN (userName IS NOT NULL) THEN 'was not null'
    END AS caseExpressionTest
FROM
    top_users

This does not give me the results I want. When the value is not null, I get 'was null', and when actually is null, I get an actual MYSQL NULL value. What I want is that when userName is null, return 'was null' and when userName is not null, return 'was not null'. What am I doing wrong here?

I am purposefully trying to do this with CASE expression, not an IF or IFNULL expression.
Using MYSQL 8.0.

Answer 1

You are misusing the case expression. There are two forms. The form you want is:

(CASE WHEN userName IS NULL THEN 'was null'
      WHEN userName IS NOT NULL THEN 'was not null'
 END) AS caseExpressionTest

Note: There is no userName after the CASE.

This checks each condition stopping at the first.

MySQL interprets booleans as a valid value. So your version is either:

-- when userName is NULL
(CASE userName
    WHEN 0 THEN 'was null'
    WHEN 1 THEN 'was not null'
END AS caseExpressionTest

This will return NULL.

Or:

-- when userName is not NULL
(CASE userName
    WHEN 1 THEN 'was null'
    WHEN 0 THEN 'was not null'
END AS caseExpressionTest

Presumably, userName is a string. This will convert userName to an integer based on leading digits. If there are no leading digits, you get 0, which is why there is a match.