MySQL: What happens when something is equal to NULL?


I have a CASE WHEN function in my query. Something like this:

CASE WHEN h.user_id = :user_id THEN '1'
     ELSE '0'
END paid

And I pass :user_id like this:

$sth->bindValue(":user_id", $user_id, PDO::PARAM_INT);

Well, Sometimes $user_id is NULL. Already I heard when something is equal with NULL in the query, something bad will happen. Now I want to know, what happens when $user_id is NULL?

I tested it, but all fine, I don't see any problem. When $user_id is NULL, $result['paid'] is 1 or that CASE..WHEN breaks?


All I want to do it, when h.user_id = :user_id then paid = 1. But sometimes :user_id is null. How can I escape null ?

The problem you're encountering is because, according to the standard, comparing anything to null gives a result of null, which eventually gets coerced to false. Thus your expression, as written, will always return 0 when either :user_id or h.user_id is null.

Fortunately, this situation arises often enough that MySQL provides a bit of syntactic sugar called the "spaceship" or "NULL-safe equal" operator, which looks like <=>. It is used exactly like the normal = operator, except that it returns false (not null) when comparing a null to a non-null, and true when comparing two null.

The expression then becomes

CASE WHEN h.user_id <=> :user_id THEN '1'
     ELSE '0'
END paid