Re: unexpected results with NOT IN query

On Thu, 20 Mar 2008, Mason Hale wrote:

> Hello --
>
> I'm getting some unexpected results with a NOT IN query -- this is on 8.2.5.
>
> This is the query in question:
>
> prod_2=> select id from feed_download_task where id in (111102466,141701504)
> and id not in (select last_feed_download_task_id from subscription);

Is it possible for last_feed_download_task_id be NULL? If so, then then id
not in (...)  will not ever return true due to the way comparisons with
NULLs work -- basically, it can't tell if the id is in the other table
because id = NULL is unknown, so it thus can't tell that it's not in the
other table either, so you could end up with neither in nor not in
returning the row.