Null Value in NOT IN condition.

[shiva]

Hi Guys,

I have this following table testin:
SQL> desc testin
Name Null? Type
------------------------------- -------- ----
NAME VARCHAR2(20)
SEX NOT NULL CHAR(1)

These are the records available in testin table:
SQL> SELECT * FROM TESTIN;

NAME S
-------------------- -
GABBIE F
F
F
TINA F
GLADIA F
KEVIN M
GIVANI M
JOHN M
SHIVA M

9 rows selected.

There are 2 rows for which NAME column has NULL value. But when i give the following query which has a NULL value in it, no rows are selected.


SQL> SELECT * FROM TESTIN WHERE NAME NOT IN('GABBIE','GLADIA',NULL);

no rows selected

and if take the NULL from the NOT IN condition, i get the follwoing results.

SQL> SELECT * FROM TESTIN WHERE NAME NOT IN('GABBIE','GLADIA');

NAME S
-------------------- -
TINA F
KEVIN M
GIVANI M
JOHN M
SHIVA M

The records with Null Values are not Returned.

Somebody please explain me this.

Thanks in Advance,

Regards,
Shiva

[richasaraf]

:) Please type the query as follows:

SQL> SELECT * FROM TESTIN WHERE NAME NOT IN('GABBIE','GLADIA') and NAME NOT NULL;

NULL is the value which cannot be included as IN..... so always use it as given above.....


:)

[shiva]

Thanks Richa, I got it and understood now!!

[masha]

Hi,

pls read the following: http://technet.microsoft.com/en-us/library/ms177682.aspx

test_expression [ NOT ] IN
( subquery | expression [ ,...n ]
)
the result must be from the same data type as test_expression. if you getting NULL results - you must cust them to the expected data type.

The alternative is to use left outther join which will return you all results, including NULLs.

Masha