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

Question

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..

Please EXPLAIN?????? what does that mean ??? – The Hungry Dictator Nov 26 '13 at 06:16 — The Hungry Dictator, Nov 26 '13 at 06:16
I mean to say why the output is no rows returned. – Nikhil Nov 26 '13 at 06:20 — Nikhil, Nov 26 '13 at 06:20
this is not the way to ask question buddy.... – The Hungry Dictator Nov 26 '13 at 06:30 — The Hungry Dictator, Nov 26 '13 at 06:30

score 4 · Accepted Answer · answered Nov 26 '13 at 06:27

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);

score 2 · Answer 2 · edited May 23 '17 at 12:05

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:

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

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

But I got an ouput when I executed this query select * from customers where customer_id in (2,3,5,NULL) — Nikhil, Nov 26 '13 at 06:23

score 0 · Answer 3 · answered Nov 26 '13 at 06:13

0

try this

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

answered Nov 26 '13 at 06:13

Mustafa

85
1
2
8

score 0 · Answer 4 · answered Nov 26 '13 at 07:53

0

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

answered Nov 26 '13 at 07:53

Wernfried Domscheit

54,457
9
76
110

`CustomerId = NULL` always returns null/unknown, NOT false. – dan04 Nov 26 '13 at 23:21

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

4 Answers4