Subquery or Left Join
in [Programming SQL]
|
Prev: records before 6 months
Next: Hash Match best?
From: tshad on 5 Apr 2010 13:28 "Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message news:qbednTHjT_KT_CzWnZ2dnUVZ_o88AAAA(a)speakeasy.net... > tshad wrote: >> "Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message >> news:qbednTbjT_It0yzWnZ2dnUVZ_o-hnZ2d(a)speakeasy.net... >>> These two queries will be different if there are multiple matches based >>> on VendorId (the first one will result in error; the second will return >>> multiple rows per vendor). >>> >> >> Why would the first one cause an error: >> > > Note that I specified you will get an error if there are multiple matches > (that is multiple rows returned in the subquery). If VendorId is unique in > table Vendor then it will not happen. > >> >> I am a little confused here. >> >> A LEFT OUTER JOIN says to give you all the rows in Customers - so in the >> second one, I should get 25,389 which is what I got. >> >> But if it is doing an OUTER JOIN in the subQuery and only passing back 1 >> record. >> >> This was why I was looking at changing the LEFT OUTER JOINS to subqueries >> thinking that was better, but I guess that isn't the case. >> > The correlated subquery has to be executed for each row and the > transformation to left join is natural, basically it does the same. Not > sure why you think using a subquery in this case would be better. > Not saying it is. Just questioning. Since Left JOINs return more rows (all left rows) then the question was is a subquery better. Thought it might be different with an INNER JOIN but wasn't sure. Thanks, Tom > -- > Plamen Ratchev > http://www.SQLStudio.com |