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
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".
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments