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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Warren Little <wlittle(at)securitylending(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Why do the two queries below return different results?
Date: 2003-07-20 04:54:26
Message-ID: 20000.1058676866@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Warren Little <wlittle(at)securitylending(dot)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

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Chris Travers 2003-07-20 05:07:34 SECURITY DEFINER changes CURRENT_USER?
Previous Message Tom Lane 2003-07-20 04:42:17 Re: column doesn't get calculated - updated