[Solved] Using a Case Statement With IS NULL and IS NOT NULL

Question

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.

Accepted Answer