MySQL: Why is NULL ignored in MySQL?

Question

I have a table with the following possible values for the column field_value.

But when I try to select all values which are not 'CB', the query result also ignores all NULL values.

Why does this happen?

mysql> select distinct field_value from TableName; +--------------+ | field_value | +--------------+ | S | | NULL | | CA | | CB | +--------------+ 4 rows in set (6.32 sec) mysql> select distinct field_value from TableName where field_value!='CB'; +--------------+ | field_value | +--------------+ | S | | CA | +--------------+ 2 rows in set (0.15 sec) mysql>

you can not compare a field value to NULL with comparison operator , instead you have to use IS NULL operator. — Satya
– Satya, Commented Oct 19, 2012 at 3:18

Ja͢ck · Accepted Answer · 2012-10-19 03:59:07Z

That's because any comparisons with NULL also yield NULL (i.e. not truthy nor falsy).

The special operators IS NULL and IS NOT NULL are used to make useful comparisons against NULL.

Your query should therefore be:

... WHERE field_value!='CB' OR field_value IS NULL;

See also: Working with NULL values

John Woo · Accepted Answer · 2012-10-19 03:18:12Z

Because NULL is unknown and unknown doesn't mean that is it not equal to CB.

if you want to return null values add a condition (IS NULL) in your query,

SELECT ... FROM ... where field_value != 'CB' OR field_value IS NULL

Walter Mitty · Accepted Answer · 2012-10-25 16:35:51Z

NULL is not equal to any value. NULL is not unequal to any value. NULL is not even equal to NULL. Try it if you don't believe it.

select * from anytable where NULL = NULL or not NULL = NULL or NULL != NULL or not NULL != NULL

You should get zero rows retrieved. The result of all four conditions is UNKNOWN. unknown is not the same thing as TRUE. unknown is not the same thing as FALSE. If this sounds like a mess to you, then you are probably starting to understand.

It's best to avoid using nullable columns in WHERE conditions, unless you are prepared to think things through very carefully.

Vijaychandar · Accepted Answer · 2012-10-19 03:38:40Z

0

Actually NULL means “a missing unknown value”

Check this page

answered Oct 19, 2012 at 3:38

Vijaychandar

7145 silver badges21 bronze badges

Collectives™ on Stack Overflow

MySQL: Why is NULL ignored in MySQL?

4 Answers 4

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Linked

Related