CODEWITHSUNDEEP
sqloracle-databaseoracle11gnull
Nikhil
Nikhil

Reputation: 151

NOT IN returns false if a value in the list is null

CUSTOMERS TABLE

CUSTOMER_ID FIRST_NAME     LAST_NAME         DOB        PHONE

1           John            Brown         1/1/1965  800-555-1211
2           Cynthia         Orange        2/5/1968  800-555-1212
3           Steve           White         3/16/1971 800-555-1213
4           Gail            Black                   800-555-1214
5           Doreen          Blue          5/20/1970 
6           Fred            Brown         1/1/1970  800-555-1215

If a run a query 

select * from customers where customer_id not in (2,3,5,NULL);

I get an output no rows returned.....Please help me to sort this problem out..

Upvotes: 2

Views: 301

Answers (4)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59543

Your statement 

select * from customers where customer_id in (2,3,5,NULL)

is equal to 

    select * where CustomerID = 2 or CustomerID = 3 or CustomerID = 5 or CustomerID = NULL

The last expression "CustomerID = NULL" returns always FALSE, but due to OR condition this does influence the result.

You have to write it like this:

select * from customers where customer_id in (2,3,5) or customer_id IS NULL

Upvotes: 0

dan04
dan04

Reputation: 91209

You got bitten by SQL 3-valued logic.

For the rows with a customer_id of 2, 3, or 5, the WHERE clause evaluates to false, as you expected.

For the other rows, it evaluates to UNKNOWN (or NULL; I don't know if Oracle distinguishes them), not to true.

This may be more clear if the IN expression were expanded to (customer_id != 2) AND (customer_id != 3) AND (customer_id != 5) AND (customer_id != NULL). For a customer_id of 1, 4, or 6, the first three subexpressions evaluated to true, as you'd expected. But the last one evaluates to unknown, because the NULL (a marker for an unknown value) might "really" be 1, 4, or 6. Thus, the entire expression has an unknown truth value. The SELECT statement will only return rows for which the WHERE clause is definitely true, not unknown.

You can get the results you wanted by making the query.

select * from customers where customer_id not in (2, 3, 5) and customer_id is not null;

However, it seems that your customer_id is an autoincrement column and can't really be null anyway. If so, just write:

select * from customers where customer_id not in (2, 3, 5);

Upvotes: 4

Dylan Corriveau
Dylan Corriveau

Reputation: 2557

In this particular instance, you are looking for

select * from customers where customer_id not in (2,3,5);

The null would be omitted in this case.

Why?

As explained here, A Not In statment does, in this case the following:

select * where CustomerID <> 2 and CustomerID <> 3 and CustomerID <> 5 and CustomerID <> NULL

Using the default ansi_nulls notation on, customerID <> NULL would result in an UNKNOWN. When SQL has an UNKNOWN, it will return no rows. When this is off, it would return true.

You have two options at this point:

  1. Change the statement to not have a null in it
  2. Change your Database Engine to have ansi_nulls off

I think 1 would be the much easier choice in this case...

Upvotes: 2

Mustafa
Mustafa

Reputation: 85

try this

select * from customers where customer_id not in (2,3,5);

Upvotes: 0

Related Questions