CODEWITHSUNDEEP
sqlsql-serveroracle-databaseoracle10g
Maddy
Maddy

Reputation: 3816

How does 'in' clause works in oracle

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?

Upvotes: 9

Views: 29906

Answers (5)

Maksym Polshcha
Maksym Polshcha

Reputation: 18358

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.

Upvotes: 4

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

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.

Upvotes: 15

manurajhada
manurajhada

Reputation: 5380

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;

Upvotes: 2

Madhivanan
Madhivanan

Reputation: 13700

Yes. It is correct. Also NULL values should be compared with IS NULL

Upvotes: 0

John D
John D

Reputation: 2365

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.

Upvotes: 1

Related Questions