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?

Answer 1

Yes it is.

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

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

Coalesce: https://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce

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

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

https://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_equal-to

Related SO resource: MySQL comparison with null value

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.