Re: subselect and left join not working? - Mailing list pgsql-sql
| From | Jorge Arenas |
|---|---|
| Subject | Re: subselect and left join not working? |
| Date | |
| Msg-id | AANLkTi=85LOHz+fU1s-4-mjJw=nrzaWkd6hdwW7b7BYv@mail.gmail.com Whole thread Raw |
| In response to | Re: subselect and left join not working? (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: subselect and left join not working?
(Carla <cgourofino@hotmail.com>)
|
| List | pgsql-sql |
<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;" />