Thread: subselect and left join not working?
<div style="font-family:arial,helvetica,sans-serif;font-size:10pt;color:#000000;"><div style="font-family: Courier New,courier,monaco,monospace,sans-serif;">Hi,<br /><br />I am using postgres 8.4 and I am having problems doing a very simplequery:<br /><br />select zona_id from zonas where zona_id not in (select zona_id from usuarios where per_id =2)<br/><br /> but I get no records in return. <br /><br />The problem is that, if I I break down the quey, I get:<br /><br/>select zona_id from zonas <br /><br />"A"<br />"B"<br />"C"<br />"D"<br />"E"<br />"F"<br />"FCHIH"<br />"FCOAH"<br/>"DGO"<br />"T"<br />"FGTO"<br />"FGRO"<br />"FQRO"<br />"FHGO"<br />"JAL"<br />"MOR"<br />"FPUE"<br />"FQROO"<br/>"FSLP"<br />"F VER"<br />"FYUC"<br />""<br /><br />select zona_id from usuarios where per_id =2<br /><br />"E"<br/>"F"<br />"FCHIH"<br />"JAL"<br />"FPUE"<br />"F VER"<br />"C"<br />"D"<br />"A"<br />""<br /><br />but with <br/><br />select zona_id from zonas where zona_id not in (select zona_id from usuarios where per_id =2)<br /><br />I dont'get records when I am expecting to see this:<br /><br /> "B"<br /> "FCOAH"<br /> "DGO"<br /> "T"<br /> "FGTO"<br />"FGRO"<br /> "FQRO"<br /> "FHGO"<br /> "MOR"<br /> "FQROO"<br /> "FSLP"<br /> "FYUC"<br /> ""<br /><br />What's wrong?Please help. Any clues?<br /><br /><br />---<br />It also does not work a left join on 'zonas'. Some 'zonas' are designatedto some users but in my case is not wroking:<br /><br />t1<br />usr zone<br />1 A<br />2 C<br />3 D<br /><br />t2<br />zone<br />A<br />B<br />C<br />D<br />E<br /><br />I want to see<br />zone usr<br />A 1<br />B<br />C 2<br />D 3<br />E<br /></div></div>
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.
to avoid that confusion do it this way:
select zona_id as z from zonas where z not in (select zona_id fromusuarios where per_id =2)
or this way:
select zona_id from zonas where zona_id not in (select usuarios.zona_id fromusuarios where per_id =2)
or this way:
select zona_id from zonas where zona_id not in (select u.zona_id fromusuarios as u where per_id =2)
--
⚂⚃ 100% natural
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
<span style="font-family: courier new,monospace;">Tom,</span><br style="font-family: courier new,monospace;" /><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">The subselect worked whenI removed nulls. Thanks! Now I am facing a similar problem with the left join:</span><br style="font-family: couriernew,monospace;" /><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">selectzonas.zona_id from zonas order by zona_id</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"A"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"B"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"C"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"D"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"DGO"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"E"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"F"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"F VER"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"FCOAH"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"FCHIH"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"FGRO"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"FGTO"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"FHGO"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"FPUE"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"FQRO"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"FQROO"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"FSLP"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"FYUC"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"JAL"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"MOR"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"T"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"x"</span><br style="font-family: courier new,monospace;" /><br style="font-family:courier new,monospace;" /><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">select zona_id, usr_folio from usuarios where per_id = 2 order by usuarios.zona_id</span><brstyle="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"A" 1002</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"C" 1003</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"D" 1004</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"E" 1005</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"F" 1006</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"F VER" 1010</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"FCHIH" 1007</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"FPUE" 1009</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"JAL" 1008</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"x" 1000</span><br style="font-family: courier new,monospace;" /><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">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</span><brstyle="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"A" 1002</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"C"</span><span style="font-family: courier new,monospace;"> </span><spanstyle="font-family: courier new,monospace;">1003</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"D"</span><span style="font-family: courier new,monospace;"> </span><spanstyle="font-family: courier new,monospace;">1004</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"E"</span><span style="font-family: courier new,monospace;"> </span><spanstyle="font-family: courier new,monospace;">1005</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"F"</span><span style="font-family: courier new,monospace;"> </span><spanstyle="font-family: courier new,monospace;">1006</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"F VER"</span><span style="font-family: courier new,monospace;"></span><spanstyle="font-family: courier new,monospace;"> 1010</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">"FCHIH"</span><span style="font-family: courier new,monospace;"></span><spanstyle="font-family: courier new,monospace;"> 1007</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">"FPUE"</span><span style="font-family: courier new,monospace;"> </span><span style="font-family: courier new,monospace;">1009</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">"JAL"</span><span style="font-family: courier new,monospace;"> </span><span style="font-family: courier new,monospace;">1008</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">"x"</span><span style="font-family: courier new,monospace;"> </span><span style="font-family: courier new,monospace;">1000</span><br style="font-family: courier new,monospace;"/><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">butI am expecting those zonas that have no usr_folio assignated</span><br style="font-family: courier new,monospace;"/><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"A"</span><spanstyle="font-family: courier new,monospace;"> </span><span style="font-family: couriernew,monospace;">1002</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"B"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"C"</span><spanstyle="font-family: courier new,monospace;"> </span><span style="font-family: couriernew,monospace;">1003</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"D"</span><spanstyle="font-family: courier new,monospace;"> </span><span style="font-family: couriernew,monospace;">1004</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"DGO"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"E"</span><spanstyle="font-family: courier new,monospace;"> </span><span style="font-family: couriernew,monospace;">1005</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"F"</span><spanstyle="font-family: courier new,monospace;"> </span><span style="font-family: couriernew,monospace;">1006</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"FVER" 1010</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"FCOAH"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"FCHIH"1007</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"FGRO"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"FGTO"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"FHGO"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"FPUE" 1009</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"FQRO"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"FQROO"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"FSLP"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"FYUC"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"JAL" 1008</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"MOR"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"T"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"x" 1000</span><br style="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">which can be achieve by doing:</span><br style="font-family:courier new,monospace;" /><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">select zona_id, usr_folio from usuarios where per_id = 2 </span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">union</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">select zona_id,null from zonaswhere zona_id not in (select zona_id from usuarios where per_id = 2) </span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">order by zona_id</span><br style="font-family: couriernew,monospace;" /><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">butI would like to use the left join instead</span><br style="font-family: courier new,monospace;" /><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">thanks for your help</span><brstyle="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">Jorge.</span><br style="font-family: courier new,monospace;" /><br style="font-family:courier new,monospace;" /><br style="font-family: courier new,monospace;" /><br style="font-family: couriernew,monospace;" /><br style="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;"/><br style="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;" /><brstyle="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;" /><br style="font-family:courier new,monospace;" /><br style="font-family: courier new,monospace;" /><br style="font-family: couriernew,monospace;" /><br style="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;"/><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">On Mon,Nov 29, 2010 at 9:27 AM, Tom Lane <<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>> wrote:</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">></span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">> Jasen Betts <<a href="mailto:jasen@xnet.co.nz">jasen@xnet.co.nz</a>>writes:</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">> > On 2010-11-29, Jorge Arenas <<a href="mailto:jorge.arenas@kamarble.com">jorge.arenas@kamarble.com</a>>wrote:</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">> >> select zona_id from zonas where zona_idnot in (select zona_id from usuarios</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">> > ####### ####### #######</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">> >>where per_id =2)</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">></span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">>> select 'FRED' from from usuarios where per_id =2</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">></span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">> > what'shappening is your not in subquery is being'corrupted' by the</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">>> surrounding query, the expression zona_id is being replaced with the</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">> > value from the mainquery. so the inner query return multiple copies</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">> > of the value from the outer query and the not-in fails.</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">></span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">> That explanation is nonsense,and so is the proposed fix.</span><br style="font-family: courier new,monospace;" /><span style="font-family: couriernew,monospace;">></span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">>What I suspect is really going on is that the subselect yields one or</span><br style="font-family: couriernew,monospace;" /><span style="font-family: courier new,monospace;">> more NULL values. If there's a NULL thenNOT IN can never return TRUE,</span><br style="font-family: courier new,monospace;" /><span style="font-family: couriernew,monospace;">> only FALSE (if the tested value is definitely present) or NULL (meaning</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">> it might match one ofthe NULLs, because NULL means "unknown" in this</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">> context). Newbies get caught by that all the time :-( ... it's not one</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">> of SQL'sbetter features.</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">></span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">> regards, tom lane</span><br style="font-family: courier new,monospace;" /><brstyle="font-family: courier new,monospace;" />
Try it:<br /><br />select zonas.zona_id, usr_folio from zonas left join usuarios on (<b>per_id = 2 and </b>zonas.zona_id= usuarios.zona_id) order by zonas.zona_id;<br /><br />Or:<br /><br />select zonas.zona_id, usr_folio fromzonas left join usuarios on zonas.zona_id = usuarios.zona_id where per_id = 2 <b>or usuarios.zona_id is null</b> orderby zonas.zona_id;<br /><br />When you do a left join and a left table value does not match any value of the right table,the left table's column will have a value and all of the right table' columns will have NULL (inclusive per_id).<br/><br /><br />Carla O.<br /><br /><div class="gmail_quote">2010/11/30 Jorge Arenas <span dir="ltr"><<a href="mailto:jorge.arenas@kamarble.com">jorge.arenas@kamarble.com</a>></span><br/><blockquote class="gmail_quote" style="margin:0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><span style="font-family:courier new,monospace;">Tom,</span><br style="font-family: courier new,monospace;" /><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">The subselect worked whenI removed nulls. Thanks! Now I am facing a similar problem with the left join:</span><br style="font-family: couriernew,monospace;" /><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">selectzonas.zona_id from zonas order by zona_id</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"A"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"B"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"C"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"D"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"DGO"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"E"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"F"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"F VER"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"FCOAH"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"FCHIH"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"FGRO"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"FGTO"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"FHGO"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"FPUE"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"FQRO"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"FQROO"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"FSLP"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"FYUC"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"JAL"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"MOR"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"T"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">"x"</span><br style="font-family: courier new,monospace;" /><br style="font-family:courier new,monospace;" /><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">select zona_id, usr_folio from usuarios where per_id = 2 order by usuarios.zona_id</span><brstyle="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"A" 1002</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"C" 1003</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"D" 1004</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"E" 1005</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"F" 1006</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"F VER" 1010</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"FCHIH" 1007</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"FPUE" 1009</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"JAL" 1008</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"x" 1000</span><br style="font-family: courier new,monospace;" /><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">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</span><brstyle="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"A" 1002</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"C"</span><span style="font-family: courier new,monospace;"> </span><spanstyle="font-family: courier new,monospace;">1003</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"D"</span><span style="font-family: courier new,monospace;"> </span><spanstyle="font-family: courier new,monospace;">1004</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"E"</span><span style="font-family: courier new,monospace;"> </span><spanstyle="font-family: courier new,monospace;">1005</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"F"</span><span style="font-family: courier new,monospace;"> </span><spanstyle="font-family: courier new,monospace;">1006</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">"F VER"</span><span style="font-family: courier new,monospace;"></span><spanstyle="font-family: courier new,monospace;"> 1010</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">"FCHIH"</span><span style="font-family: courier new,monospace;"></span><spanstyle="font-family: courier new,monospace;"> 1007</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">"FPUE"</span><span style="font-family: courier new,monospace;"> </span><span style="font-family: courier new,monospace;">1009</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">"JAL"</span><span style="font-family: courier new,monospace;"> </span><span style="font-family: courier new,monospace;">1008</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">"x"</span><span style="font-family: courier new,monospace;"> </span><span style="font-family: courier new,monospace;">1000</span><br style="font-family: courier new,monospace;"/><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">butI am expecting those zonas that have no usr_folio assignated</span><br style="font-family: courier new,monospace;"/><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"A"</span><spanstyle="font-family: courier new,monospace;"> </span><span style="font-family: couriernew,monospace;">1002</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"B"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"C"</span><spanstyle="font-family: courier new,monospace;"> </span><span style="font-family: couriernew,monospace;">1003</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"D"</span><spanstyle="font-family: courier new,monospace;"> </span><span style="font-family: couriernew,monospace;">1004</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"DGO"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"E"</span><spanstyle="font-family: courier new,monospace;"> </span><span style="font-family: couriernew,monospace;">1005</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"F"</span><spanstyle="font-family: courier new,monospace;"> </span><span style="font-family: couriernew,monospace;">1006</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"FVER" 1010</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"FCOAH"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"FCHIH"1007</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"FGRO"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"FGTO"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"FHGO"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"FPUE" 1009</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"FQRO"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"FQROO"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"FSLP"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"FYUC"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"JAL" 1008</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"MOR"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"T"</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">"x" 1000</span><br style="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">which can be achieve by doing:</span><br style="font-family:courier new,monospace;" /><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">select zona_id, usr_folio from usuarios where per_id = 2 </span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">union</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">select zona_id,null from zonaswhere zona_id not in (select zona_id from usuarios where per_id = 2) </span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">order by zona_id</span><br style="font-family: couriernew,monospace;" /><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">butI would like to use the left join instead</span><br style="font-family: courier new,monospace;" /><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">thanks for your help</span><brstyle="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">Jorge.</span><br style="font-family: courier new,monospace;" /><br style="font-family:courier new,monospace;" /><br style="font-family: courier new,monospace;" /><br style="font-family: couriernew,monospace;" /><br style="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;"/><br style="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;" /><brstyle="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;" /><br style="font-family:courier new,monospace;" /><br style="font-family: courier new,monospace;" /><br style="font-family: couriernew,monospace;" /><br style="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;"/><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">On Mon,Nov 29, 2010 at 9:27 AM, Tom Lane <<a href="mailto:tgl@sss.pgh.pa.us" target="_blank">tgl@sss.pgh.pa.us</a>> wrote:</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">></span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">>Jasen Betts <<a href="mailto:jasen@xnet.co.nz" target="_blank">jasen@xnet.co.nz</a>> writes:</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">> >On 2010-11-29, Jorge Arenas <<a href="mailto:jorge.arenas@kamarble.com" target="_blank">jorge.arenas@kamarble.com</a>>wrote:</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">> >> select zona_id from zonas where zona_id not in (select zona_id fromusuarios</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">>> ####### ####### #######</span><br style="font-family: couriernew,monospace;" /><span style="font-family: courier new,monospace;">> >> where per_id =2)</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">></span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">> > select 'FRED' fromfrom usuarios where per_id =2</span><br style="font-family: courier new,monospace;" /><span style="font-family: couriernew,monospace;">></span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">>> what'shappening is your not in subquery is being 'corrupted' by the</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">> > surrounding query,the expression zona_id is being replaced with the</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">> > value from the main query. so the inner query return multiple copies</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">> >of the value from the outer query and the not-in fails.</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">></span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">> That explanation is nonsense, and so is the proposed fix.</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">></span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">> What I suspect is reallygoing on is that the subselect yields one or</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">> more NULL values. If there's a NULL then NOT IN can never return TRUE,</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">> onlyFALSE (if the tested value is definitely present) or NULL (meaning</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">> it might match one of the NULLs, because NULL means"unknown" in this</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">>context). Newbies get caught by that all the time :-( ... it's not one</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">> of SQL's better features.</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">></span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">> regards, tom lane</span><br style="font-family: courier new,monospace;" /><brstyle="font-family: courier new,monospace;" /></blockquote></div><br />