Re: subselect and left join not working?

Tom,

The subselect worked when I removed nulls. Thanks! Now I am facing a similar
problem with the left join:

select zonas.zona_id from zonas order by zona_id
"A"
"B"
"C"
"D"
"DGO"
"E"
"F"
"F VER"
"FCOAH"
"FCHIH"
"FGRO"
"FGTO"
"FHGO"
"FPUE"
"FQRO"
"FQROO"
"FSLP"
"FYUC"
"JAL"
"MOR"
"T"
"x"

select zona_id, usr_folio from usuarios where per_id = 2 order by
usuarios.zona_id

"A" 1002
"C" 1003
"D" 1004
"E" 1005
"F" 1006
"F VER" 1010
"FCHIH" 1007
"FPUE" 1009
"JAL" 1008
"x" 1000

select zonas.zona_id, usr_folio from zonas left join usuarios on
zonas.zona_id = usuarios.zona_id where per_id = 2 order by zonas.zona_id

"A" 1002
"C" 1003
"D" 1004
"E" 1005
"F" 1006
"F VER" 1010
"FCHIH" 1007
"FPUE" 1009
"JAL" 1008
"x" 1000

but I am expecting those zonas that have no usr_folio assignated

"A" 1002
"B"
"C" 1003
"D" 1004
"DGO"
"E" 1005
"F" 1006
"F VER" 1010
"FCOAH"
"FCHIH" 1007
"FGRO"
"FGTO"
"FHGO"
"FPUE" 1009
"FQRO"
"FQROO"
"FSLP"
"FYUC"
"JAL" 1008
"MOR"
"T"
"x" 1000

which can be achieve by doing:

select zona_id, usr_folio from usuarios where per_id = 2
union
select zona_id,null from zonas where zona_id not in (select zona_id from
usuarios where per_id = 2)
order by zona_id

but I would like to use the left join instead

thanks for your help

Jorge.

On Mon, Nov 29, 2010 at 9:27 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Jasen Betts <jasen(at)xnet(dot)co(dot)nz> writes:
> > On 2010-11-29, Jorge Arenas <jorge(dot)arenas(at)kamarble(dot)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