NOT IN clause and NULL value

Question

The NOT IN clause is omitting the NULL values when checking the condition.

INPUT  
ID  NAME  
1   A  
2   <null>
3   C

SELECT... FROM...
WHERE NAME NOT IN ('C')

is only returning ID value 1. I need both 1 & 2.

Could this be done?

http://stackoverflow.com/questions/12966873/mysql-why-is-null-ignored-in-mysql — asifsid88, Jul 09 '13 at 09:26
Basically you want to show table data except where name is C...?? — Vishal, Jul 09 '13 at 09:26
See this [Question](http://stackoverflow.com/questions/17442229/where-clause-rejecting-rows-if-null-occurred) — Romesh, Jul 09 '13 at 09:33

score 5 · Accepted Answer · answered Jul 09 '13 at 09:23

5

WHERE NAME NOT IN ('C') OR NAME IS NULL

answered Jul 09 '13 at 09:23

zerkms

score 3 · Answer 2 · answered Jul 09 '13 at 09:36

Either you check NULL values

select *
from not_in
where name not in ('C') or name is null;

or you can convert NULL values in any other character with coalesce. I use ' ' in the sample below.

select *
from not_in
where coalesce(name, ' ') not in ('C');

score 2 · Answer 3 · edited May 23 '17 at 11:44

2

Correct SQL would be

SELECT... FROM...
WHERE NAME NOT IN ('C')
or NAME is NULL

That's because any comparisons with NULL also yield NULL (i.e. not truthy nor falsy). (Credit to @Jack)

edited May 23 '17 at 11:44

Community

answered Jul 09 '13 at 09:23

asifsid88

@asifsid88 - The reason is correct but your condition is wrong. It should be `Name IS NULL` IMO. – Himanshu Jul 09 '13 at 09:30
1

@asifsid88 - Glad. +1 for the given reason now. – Himanshu Jul 09 '13 at 10:34

score 0 · Answer 4 · answered Jul 09 '13 at 09:23

0

SELECT... 
FROM...
WHERE NAME NOT IN ('C') 
    OR NAME IS NULL

SELECT... 
FROM...
WHERE ISNULL(NAME, '') NOT IN ('C')

answered Jul 09 '13 at 09:23

gareththegeek

1

`ISNULL(NAME, '') NOT IN ('C') ` will never be optimized. Should be never assumed as a solution. – zerkms Jul 09 '13 at 09:25
agreed - using any function on a column invalidates the index; this is why I cringe when I see code like "WHERE lower(username) = :param" then I'm asked why this query is so slow "there's an index on username!" – Trent Jul 09 '13 at 09:28
@zerkms - although `NAME NOT IN ('C') OR NAME IS NULL` will probably result in a full table scan, at least on Oracle. – APC Jul 09 '13 at 09:58
@APC: `NOT IN` would for any B-Tree based indexes. For oracle - depending on the index type. If you have bitmap there is a chance it won't. – zerkms Jul 09 '13 at 10:23

4 Answers4