Postgres Pro
Downloads
Home   >   mailing lists

Re: Why do the two queries below return different results? - Mailing list pgsql-sql

From Tom Lane
Subject Re: Why do the two queries below return different results?
Date
Msg-id 20000.1058676866@sss.pgh.pa.us
Whole thread Raw
In response to Why do the two queries below return different results?  (Warren Little <wlittle@securitylending.com>)
List pgsql-sql
Tree view 
Warren Little <wlittle@securitylending.com> writes:
> Look at the following two queries.
> ...
> and not exists (select pid from casecombo where casepid = secondpid)
> ...
> and casepid not in (select secondpid from casecombo)

> The second query is broken and I don't understand why.

I'll bet there are some NULL values for secondpid in casecombo.
The behavior of NOT IN with NULLs is fairly unintuitive :-(
        regards, tom lane

pgsql-sql by date:

Previous
From: Chris Travers
Date:
Subject: SECURITY DEFINER changes CURRENT_USER?
Next
From: Tom Lane
Date:
Subject: Re: column doesn't get calculated - updated