To: Jasen Betts <jasen@xnet.co.nz>
cc: Jorge Arenas <jorge.arenas@kamarble.com>, pgsql-sql@postgresql.org
Subject: Re: subselect and left join not working? 
In-reply-to: <icvsmc$6p1$1@reversiblemaps.ath.cx> 
References: <749730.36457.qm@web113802.mail.gq1.yahoo.com>
	<icvsmc$6p1$1@reversiblemaps.ath.cx>
Comments: In-reply-to Jasen Betts <jasen@xnet.co.nz>
	message dated "29 Nov 2010 09:44:44 +0000"
Date: Mon, 29 Nov 2010 10:27:00 -0500
Message-ID: <25301.1291044420@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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