How does 'in' clause works in oracle

Question

select 'true' from dual where 1 not in (null,1);

when we execute this which will result nothing

what my question is:

is the above query is logically equivalent to

select 'true' from dual where 1 != null and 1 != 1;

which will result nothing just as above statement

Please clarify?

Answer 1

Correct (but note that IN is an operator, not a clause and it works like this in SQL in general, not only for Oracle).

where 1 not in (null,1)

is equivalent to:

where 1 != null and 1 != 1

which should really be written as:

WHERE 1 NOT IN (NULL, 1)

and

WHERE 1 <> NULL AND 1 <> 1

which is the same as:

WHERE (1 <> NULL) AND (1 <> 1)

which evaluates to:

WHERE UNKNOWN AND FALSE

and further as:

WHERE FALSE

So, it correctly returns no rows.

---

Notice that if you had WHERE 1 NOT IN (NULL, 2), it would evaluate to WHERE UNKNOWN (left as an exercise) and no rows would be returned either.

Answer 2

The issue of your script in comparing with NULL value. You should use

column is null and column = 1

Actually NULL is an undefined value. Any comparation with NULL gives neither True nor False but NULL. Even NULL = NULL

That's why your 1 not in (null,1) doesn't work.

Answer 3

Yes they are.

select something from table where column not in (1,2,3);

is equivalent to

select something from table where column != 1 and column != 2 and column != 3;

Answer 4

The IN statement is a collection of OR statements, while NOT IN is a collection of AND statements - but it is also not equal to.

So the NOT IN is equivalent to:

1 <> NULL
AND 1 <> 1
AND ...

While the IN would be equivalent to:

1 = NULL
OR 1 = 1
OR ...

Note that having NULL in the collection will not work, due to the quirky nature of NULL.