MySQL comparison with null value

Question

I have a column called CODE in a MySQL table which can be NULL. Say I have some rows with CODE='C' which I want to ignore in my select result set. I can have either CODE=NULL or CODE!='C' in my result set.

The following query does not return a row with CODE as NULL:

SELECT * from TABLE where CODE!='C' 

But this query works as expected and I know it is the right way to do it.

SELECT * from TABLE where CODE IS NULL OR CODE!='C' 

My question is why does having only CODE!='C' does not return rows where CODE=NULL? Definitely 'C' is not NULL. We are comparing no value to a character here. Can someone throw some light as why it doesn't work that way?

Answer 1

In MySQL, NULL is considered as a 'missing, unknown value', as opposed to no value. Take a look at this MySQL Reference on NULL.

Any arithmetic comparison with NULL does not return true or false, but returns NULL instead., So, NULL != 'C' returns NULL, as opposed to returning true.

Any arithmetic comparison with 'NULL' will return false. To check this in SQL:

SELECT IF(NULL=123,'true','false')  

To check NULL values we need to use IS NULL & IS NOT NULL operator.