How to handle NULL values and the NOT IN clause

[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!

[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

[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

[scutter]

Fyi - there’s a discussion on why you get that result here:

http://stackoverflow.com/questions/1290 ... ull-values

[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!

[scutter]

While I did watch General Hospital as a teenager, I am far from an expert on that subject