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

2

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;

Comments

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

2

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

answered Oct 19, 2012 at 3:18

John Woo

265k70 gold badges509 silver badges500 bronze badges

Comments

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

1

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.

edited Oct 25, 2012 at 16:35

answered Oct 21, 2012 at 2:06

Walter Mitty

19k2 gold badges32 silver badges60 bronze badges

Comments

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

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related