CODEWITHSUNDEEP
mysqlsql
PRIYA M
PRIYA M

Reputation: 181

Why result set is EMPTY when the NOT IN inner query contains value 'NULL'?

This is my mysql table named samp

+--------+--------+
| name   | roleid |
+--------+--------+
| alki   |      2 |
| karthi |      3 |
| aadhil |      2 |
| pri    |      2 |
| pri    |      2 |
+--------+--------+

when I used query like select name from samp where name not in ('alki','pri',NULL)

I expected the result to be,

+--------+
| name   |
+--------+
| karthi |
| aadhil |
+--------+

But my result is Empty set. I have an alternative to do this. But I need to know the reason behind this.

Upvotes: 1

Views: 141

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

You can simply do:

select name
from samp
where name not in ('alki', 'pri');

NULL fails not in, just as it fails most other comparisons.

If you explicitly wanted to include NULL, you would need to include it as:

select name
from samp
where name not in ('alki', 'pri') or name is null;

Upvotes: 3

D-Shih
D-Shih

Reputation: 46239

NULL means unknown it isn't a value, you need to use not null instead of use in NULL

You can try this.

select name from samp where name not in ('alki','pri') and name is not null

Upvotes: 3

Álvaro González
Álvaro González

Reputation: 146578

That's how NULL is meant to behave. It doesn't compare with anything by design. Your query is interpreted like:

select name
from samp
where name <> 'alki'
and name <> 'pri'
and name <> NULL

Since name neither equals not "not" equal NULL the condition is not met.

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can try below

select name from samp where name not in ('alki','pri') and name is not null

Upvotes: 3

Related Questions