some problems about TD KEYWORD 'NOT IN'

Database

some problems about TD KEYWORD 'NOT IN'

image one col named 'Num' contains value domain (null, 1 ,2 , 3)

if we write

sel *
from table_name
where Num not in (2,3)

we just can get answer set containing rows with (Num=1), losing rows having null value

It's really a problem.
2 REPLIES
Enthusiast

Re: some problems about TD KEYWORD 'NOT IN'

Matty,
I dont think it is problem with the NOT IN clause.

NOT IN is nothing but
where value NOT EQUAL TO.
so sel * from table
where num NOT IN (2,3)

will take each value and see it is not equal to 2 and not equal to 3

for the value 1 it is not equal to 2 and it is not equal to 3
so TRUE and TRUE will result in TRUE and this row or num 1 is retrieved in the answerset

for 2
it goes thru same steps and we get FALSE (2 not equal to 2) and TRUE (2 not equal to 3)
false and true result in false and this row or num 2 is not retreived in answerset

in case of NULL
NULL not equal to 2 will result in INVALID or NOT KNOWN and NULL not equal to 3 is INVALID
so the result INVALID and INVALID will result in INVALID or something that is not TRUE so this row will not be in answerset.

so if you want to get the NULL add in where clause.

You should be very carefull when you do some left or right outer joins and you are using columns that have NULL values. You will get some funny results if you forget the NULLs.

Highlighted
Enthusiast

Re: some problems about TD KEYWORD 'NOT IN'

If you want rows that have NULL in the Num column, you could also use COALESCE to do this:

sel *
from table_name
where COALESCE(Num,-99999) not in (2,3)

You just have to pick a value that you're not looking for in your "NOT IN" clause.

Hope this helps.

Barry