question about NOT IN

[ashley75]

Let say I have two table named ONE and TWO:

tables one one column TEST with 4 rows: A,B,C,null
table two had one column TEST with 4 rows: A,B,C,D

If I query :

select test from TWO
where test IN (select test from ONE)

which returns A,B,C

If I query

select test from TWO
where test NOT IN (select test from ONE)

and I got the null value, why ???? I thought I will get the D value instead

[SANJAY_G]

Expected. It's because of NULL.

sanjay@ORAM> select table_name from user_tables;

TABLE_NAME
------------------------------
PLAN_TABLE
T1
T2
TEST
TEST1

sanjay@ORAM> select table_name from user_tables where table_name not in (null);

no rows selected

sanjay@ORAM>

[shailendra]

You can use NVL function or minus operator to get the correct result


Shailendra

[abhaysk]

....or "IS NOT NULL" clause

[ashley75]

I agree that I can use some other function to get the result I want, I was curious how does SQL work and the logic if someone can give the explanation.

thanks

[DaPi]

I agree it's far from intuitive - I suppose the test for the row with D beomes:

'D'<>'A' AND 'D'<>'B' AND 'D'<>'C' AND 'D'<>NULL

'D'<>NULL is false (so is 'D'=NULL, and so is NOT 'D'=NULL etc etc) . . . .


I always start by coding these as NOT EXISTS, which behaves as I expect - 9 times out of 10 it's faster too.

[TomazZ]

Originally posted by DaPi
I always start by coding these as NOT EXISTS, which behaves as I expect - 9 times out of 10 it's faster too.

Yep, me too!