| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | jd(at)commandprompt(dot)com |
| Cc: | Scott Whitney <swhitney(at)journyx(dot)com>, "'Hoover, Jeffrey'" <jhoover(at)jcvi(dot)org>, "'Kevin Grittner'" <Kevin(dot)Grittner(at)wicourts(dot)gov>, pgsql-admin(at)postgresql(dot)org |
| Subject: | Re: Strange query problem... |
| Date: | 2009-01-28 19:49:52 |
| Message-ID: | 21337.1233172192@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-admin |
"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> This looks like a NULL vs '' issue. Am I wrong?
No, it's a NULL vs NOT IN issue. Specifically, if the subquery yields
any NULLs and the comparison operator is strict (which nearly all are)
then it's impossible to get a TRUE result from the NOT IN --- the only
possibilities are FALSE (if a match is found among the non-nulls)
or NULL (if not). Standard gotcha for newbie SQL coders.
My recommendation is to use NOT EXISTS instead; it's got less surprising
semantics (and, as of 8.4, it'll get optimized significantly better than
NOT IN).
regards, tom lane
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Moshe Ben-Shoham | 2009-01-29 11:50:11 | Encoding problem using pg_dumpall |
| Previous Message | Kevin Grittner | 2009-01-28 19:20:35 | Re: Strange query problem... |