Page 1 of 1
How to handle NULL values and the NOT IN clause
Posted: Thu Apr 02, 2015 9:29 pm
by usli06
Hello,
I have a quick question. What is the best way to handle the situation where there may be NULL values in the list of values used in a NOT IN statement? Here is an example:
Code: Select all
dbadmin=> select * from names;
name
-------
Laura
(2 rows)
dbadmin=> select name from names where 'Luke' not in (select name from names);
name
------
(0 rows)
I was expecting the get the name "Laura" as a result. If I delete the NULL row from the names table, it works.
Code: Select all
dbadmin=> delete from names where name is null;
OUTPUT
--------
1
(1 row)
dbadmin=> select name from names where 'Luke' not in (select name from names);
name
-------
Laura
(1 row)
So how should I handle NULL values in the NOT IN value list?
Thank you!
Re: How to handle NULL values and the NOT IN clause
Posted: Thu Apr 02, 2015 9:46 pm
by JimKnicely
You can use the NVL function. Something like this:
Code: Select all
dbadmin=> SELECT * FROM names;
name
-------
Laura
(2 rows)
dbadmin=> SELECT name FROM names WHERE 'Luke' NOT IN (SELECT NVL(name, 'NULL') FROM names);
name
-------
Laura
(2 rows)
Or you can re-write your query to use the NOT EXISTS logic. Something like this:
Code: Select all
dbadmin=> SELECT name FROM names WHERE NOT EXISTS (SELECT name FROM names WHERE name = 'Luke');
name
-------
Laura
(2 rows)
Notice that in both cases, you get back that NULL row in your result set. You can exclude it by tacking on a "name IS NOT NULL" in the main WHERE clause. Something like this:
Code: Select all
dbadmin=> SELECT name FROM names WHERE 'Luke' NOT IN (SELECT NVL(name, 'NULL') FROM names) AND name IS NOT NULL;
name
-------
Laura
(1 row)
dbadmin=> SELECT name FROM names WHERE NOT EXISTS (SELECT name FROM names WHERE name = 'Luke') AND name IS NOT NULL;
name
-------
Laura
(1 row)
P.S. Are you a "General Hospital" fan
Re: How to handle NULL values and the NOT IN clause
Posted: Thu Apr 02, 2015 9:53 pm
by usli06
Wow, fast response! Thanks! Hmm. Which method do you think is best?
And yes, I was the biggest fan of GH. I guess my data elements gave me away
This was my favorite episode(s):
https://www.youtube.com/watch?v=HhHu-sJzMdg
Re: How to handle NULL values and the NOT IN clause
Posted: Thu Apr 02, 2015 10:17 pm
by scutter
Fyi - there’s a discussion on why you get that result here:
http://stackoverflow.com/questions/1290 ... ull-values
Re: How to handle NULL values and the NOT IN clause
Posted: Fri Apr 03, 2015 11:20 am
by usli06
scutter, I got excited when I thought you were pointing me to a discussion about "General Hospital"
But after reading through the discussion on why I am seeing the unexpected result with NOT IN and NULL, now makes sense to me. Thanks for letting me know about link!
Re: How to handle NULL values and the NOT IN clause
Posted: Fri Apr 03, 2015 3:33 pm
by scutter
While I did watch General Hospital as a teenager, I am far from an expert on that subject