Mysql select ignores null values on return results-CodePudding

I have a table where a column is mostly NULL except for one row.

ID STATUS VALUE POS
1  'BAD'   200   0 
2   NULL   200   0
3   NULL   300   1
4   'OK'    0    2

if I do

Select * from table where STATUS != 'OK'

I expect

ID STATUS VALUE POS 
1  'BAD'   200   0
2   NULL   200   0
3   NULL   300   1

But I get

ID STATUS VALUE POS 
1  'BAD'   200   0

I want to know why, I know I can do something else like Where ID = 4, but why the query above returns an empty result for NULL values?

Thanks

CodePudding user response：

Comparing with NULL is always NULL. Think about NULL like it is "unknown value". Does some unknown value is not equal to 'OK'? this is unknown... so the result is NULL.

In logical expressions NULL is treated as FALSE. So you do not receive the rows which you want to receive.

You must either apply additional check for NULL value (WHERE status != 'OK' OR status IS NULL) or convert NULL value to some definite constant value before compare (WHERE COALESCE(status, '') != 'OK').

Pay attention - this interpretation differs from one used in CHECK constraint (including FOREIGN KEY constraint) in which NULL value is treated as "matched".

CodePudding user response：

It isn't possible to use "equal" or "not equal" for NULL values

You MUST use IS NULL or IS NOT NULL e.g:

Select * from table where STATUS != 'OK' OR Status IS NULL