stack stack - 7 months ago 13
SQL Question

MySQL: What happens when something is equal with 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?




EDIT:

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
?

Answer

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