From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jasen Betts <jasen@xnet.co.nz>
Cc: Jorge Arenas <jorge.arenas@kamarble.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: subselect and left join not working?
Date: Mon, 29 Nov 2010 10:27:00 -0500
Message-ID: <25301.1291044420@sss.pgh.pa.us> (raw)
In-Reply-To: <icvsmc$6p1$1@reversiblemaps.ath.cx>
References: <749730.36457.qm@web113802.mail.gq1.yahoo.com>
<icvsmc$6p1$1@reversiblemaps.ath.cx>
Jasen Betts <jasen@xnet.co.nz> writes:
> On 2010-11-29, Jorge Arenas <jorge.arenas@kamarble.com> wrote:
>> select zona_id from zonas where zona_id not in (select zona_id from usuarios
> ####### ####### #######
>> where per_id =2)
> select 'FRED' from from usuarios where per_id =2
> what'shappening is your not in subquery is being 'corrupted' by the
> surrounding query, the expression zona_id is being replaced with the
> value from the main query. so the inner query return multiple copies
> of the value from the outer query and the not-in fails.
That explanation is nonsense, and so is the proposed fix.
What I suspect is really going on is that the subselect yields one or
more NULL values. If there's a NULL then NOT IN can never return TRUE,
only FALSE (if the tested value is definitely present) or NULL (meaning
it might match one of the NULLs, because NULL means "unknown" in this
context). Newbies get caught by that all the time :-( ... it's not one
of SQL's better features.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, jasen@xnet.co.nz, jorge.arenas@kamarble.com
Subject: Re: subselect and left join not working?
In-Reply-To: <25301.1291044420@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox