1

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.

PRIYA M
  • 181
  • 2
  • 3
  • 19
  • What's the result when the query is `select name from samp where name not in ('alki','pri')` ? – Murph Dec 05 '18 at 09:24
  • 1
    FYI, you could refer to this: https://stackoverflow.com/questions/129077/not-in-clause-and-null-values – Pham X. Bach Dec 05 '18 at 09:28
  • am getting my expected result. But what if the inner query returns a result that includes 'NULL'? – PRIYA M Dec 05 '18 at 09:28
  • 1
    Take a look at the link that @Pham X. Bach provided. It gives an excellent explanation of why you're getting the results you're getting. This is a common problem, and comes from not understanding how NULL works. – BobRodes Dec 05 '18 at 09:37

4 Answers4

3

You can try below

select name from samp where name not in ('alki','pri') and name is not null
Fahmi
  • 37,315
  • 5
  • 22
  • 31
  • yes. It will work. But why including just `NULL` makes my result Empty. I need to know what's happening there. – PRIYA M Dec 05 '18 at 09:27
  • First of all NULL is not a value it's marker for showing empty and that's why whenever you need to compare with NULL value you need to use is null or is not null @PRIYAM – Fahmi Dec 05 '18 at 09:29
  • 1
    As fa06 says, `[name] = NULL` check will always be false, as will `[name]<>NULL` check, because you can't compare with NULL, so it is messing up your logic. What if you did `SELECT name FROM samp where NAME not in (sub query) OR (0 = (SELECT count(1) FROM (subquery))` (Hopefully that makes sense, without the detail of your subquery it is hard to be more specific) – SazooCat Dec 05 '18 at 09:32
  • 1
    @PRIYAM I repeat, the link that was given you above will explain the answer to your question in detail. So, if you need to know what's happening there, please go and read it. The actual value that expressions involving null (besides `IS NULL`) will return is `UNKNOWN` which usually evaluates to false. – BobRodes Dec 05 '18 at 14:07
  • @BobRodes yes, the link helped me in detail.. I understood what's exactly happening. – PRIYA M Dec 06 '18 at 07:00
3

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
D-Shih
  • 44,943
  • 6
  • 31
  • 51
3

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

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.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360