How does 'in' clause works in oracle
oracle, oracle10g, sql, sql-server
Solution
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.
Problem
``` 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?