Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-24-2006
07:10 AM

03-24-2006
07:10 AM

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.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-24-2006
11:18 AM

03-24-2006
11:18 AM

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.

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
##
##### Re: some problems about TD KEYWORD 'NOT IN'

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-24-2006
12:25 PM

03-24-2006
12:25 PM

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

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