null not in exhaustive list

Question

I have a row full of 1, 2, 3 and null. Why don't there two return the same result:

select * from foo where foobar not in (1, 2, 3);
select * from foo where foobar is not null;

The first one returns empty set, while second one works as advertised. Now I'm a bit confused :-D Is there some kind of "NULL in SQL for newbies" document anywhere? :-D

(I'm using Oracle, if that matters)

possible duplicate of [Is there any difference between IS NULL and =NULL](http://stackoverflow.com/questions/3777230/is-there-any-difference-between-is-null-and-null) — juergen d, Mar 30 '12 at 10:04
The comparison of NULLs and other values are treated different between database management systems(DBMS). In some DBMSs there is a system setting how to treat NULLS. (I have no experience in oracle, but the output you describe is what you'd get from Microsoft SQL Server) In many DBMSs you just have to specify IS NULL in the where clause for NULL values to show up in your result. It's designed around some philosophical principle that you can't compare anything to nothing (NULL).... — mortb, Mar 30 '12 at 10:09

score 5 · Accepted Answer · answered Mar 30 '12 at 10:06

5

It's because your first statement is being evaluated like this:

select * from foo
where foobar <> 1 and foobar <> 2 and foobar <> 3

"null <> 1" evaluates to null, not true/false, so nothing is returned.

answered Mar 30 '12 at 10:06

MartW

12,348
3
44
68

score 4 · Answer 2 · edited Mar 30 '12 at 10:42

4

Any boolean operation involving NULL always fails. If foobar is NULL then:
- The test = 1 fails.
- But <> 1 also fails.

This means that NULL not in (1, 2, 3) always fails.

It's a peculiarity of NULL Logic that takes getting used to at first. NULL can be taking to mean Unknown. Which means it might be 1, an might might not. It's indeterminate.

So when you ask is NULL in the list (1, 2, 3) the answer is I can't tell, it's indeterminate. So rather than being TRUE or FALSE the answer itself is NULL.

And because NULL isn't TRUE, the row has failed the test.

edited Mar 30 '12 at 10:42

Ben

34,935
6
74
113

answered Mar 30 '12 at 10:08

MatBailie

83,401
18
103
137

`CREATE TABLE T (c INTEGER CHECK (c = 1)); INSERT INTO T VALUES (NULL);` -- insert succeeds! Therefore, your assertion, "Any boolean operation involving `NULL` always fails" is false. – onedaywhen Mar 30 '12 at 10:49
@onedaywhen - That's because (afaik) the actual test being carried out is for breaches of the constraint : `Raise an error if NOT(c=1)`, but then `NOT(c=1)` fails. *[I use the term "fails" rather than "is false" to try to differentiate, but I appreciate I may not have been particularly clear in this case.]* – MatBailie Mar 30 '12 at 10:54
1

From the SQL standard, compare: "A table check constraint is satisfied if and only if the specified search condition is not false" ...with: "The result of the where clause is a table of those rows of T for which the result of the search condition is true." IMO using the term 'fails' is misleading, p.s. does the 'boolean operation' `NULL IS NULL` 'fail'? ;) – onedaywhen Mar 30 '12 at 11:12

score 0 · Answer 3 · edited Mar 31 '12 at 06:15

0

not in is not opposite of in

for detail check http://jonathanlewis.wordpress.com/2007/02/25/not-in/

edited Mar 31 '12 at 06:15

Jeffrey Kemp

59,135
14
106
158

answered Mar 30 '12 at 10:08

Amritpal Singh

1,765
1
13
20

null not in exhaustive list

3 Answers3