Mysql "where" filtering out null values without explicit statement

Question

i've noticed that this statement for example

SELECT * FROM mammals where animals <> "Dog"

Filters out all the rows that contains Dog on the column animals but ALSO filters out all the rows where animals is null.

Is that normal?

Community · Accepted Answer · 2017-05-23 12:29:47Z

2

Yes it is.

You have to be null-safe when using nullable columns:

SELECT * FROM `mammals` WHERE COALESCE(`animals`, '-1') <> 'Dog'

If you were to use an equality comparison instead, there's a nullsafe operator for this:

SELECT * FROM `mammals` WHERE COALESCE(`animals`, '-1') <=> 'Dog'

Note: ISNULL (https://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_isnull) is another function similar to COALESCE. But the difference is that ISNULL is not ANSI standard and therefore should be avoided whenever possible.

CommunityBot

11 silver badge

answered Jul 23, 2015 at 17:39

Sebas

21.6k9 gold badges60 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Will this still use indexes efficiently? I think using OR field IS NULL would at least use an index, if this one can't.