Thread: Why do the two queries below return different results?

Look at the following two queries.

select casepid, origpid
from virbcase
where date_trunc('day', origdt) >= '2003-07-01'
and date_trunc('day', origdt) <= '2003-07-31'
and origpid in (select pid from party where partyid in ('00339',
'00310', 
'00320'))
and not exists (select pid from casecombo where casepid = secondpid)


select casepid, origpid
from virbcase
where date_trunc('day', origdt) >= '2003-07-01'
and date_trunc('day', origdt) <= '2003-07-31'
and origpid in (select pid from party where partyid in ('00339',
'00310', 
'00320'))
and casepid not in (select secondpid from casecombo)

The second query is broken and I don't understand why.
Correlating the subquery as is done in the first query fixes it.

-- 
Warren Little
Senior Vice President
Secondary Marketing
Security Lending Wholesale, LC
www.securitylending.com
Tel: 866-369-7763
Fax: 866-849-8082

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