|
|
Posted by Steve on 11/12/07 14:13
"Darko" <darko.maksimovic@gmail.com> wrote in message
news:1194836894.792496.171840@c30g2000hsa.googlegroups.com...
> On Nov 11, 2:53 am, "Shelly" <sheldonlg.n...@asap-consult.com> wrote:
>> ZeldorBlat wrote:
>> > On Nov 10, 8:29 pm, "Shelly" <sheldonlg.n...@asap-consult.com> wrote:
>> >> I know [Jerry and co.] that this is off-topic, but the sql group
>> >> that I could find online doesn't have much traffic, so here goes.
>>
>> >> I cam across this code:
>>
>> >> SELECT AnnoID, AnnoTitle,
>> >> (SELECT EventTitle FROM mEVENTS WHERE tblANNOUNCE.AnnoLink =
>> >> mEVENTS.EventID)
>> >> as AnnoLink,
>> >> date_format(AnnoDate,'%M %D %Y') as date
>> >> FROM tblANNOUNCE
>> >> ORDER BY AnnoDate DESC, AnnoLink, AnnoTitle
>>
>> >> I am having trouble reading this. Is this the same as:
>>
>> >> SELECT a.AnnoID, AnnoTitle, e.EventTitle as AnnoLink,
>> >> date_format(AnnoDate,'%M %D %Y') as date
>> >> FROM tblANNOUNCE AS a, mEVENTS AS e
>> >> WHERE a.AnnoLink = e,EventID
>> >> ORDER BY date DESC, AnnoLink, AnnoTitle
>>
>> >> In other words, do they do exactly the same thing? I can't get the
>> >> former to run on the current version of MySQL. Apparantly (I was
>> >> told) it ran on a later version of MySQL.
>>
>> > They should be the same. I generally see the first type of query when
>> > someone doesn't really understand joins. It also typically results in
>> > horrible performance.
>>
>> Hmmm. I learned the second type about 25 years ago and is always how I
>> do
>> those joins. My client's customer changed servers and the new server has
>> an
>> older version of MySQL. They ran into problems with many queries. I
>> started looking at the code, and the first one I came accross was the
>> first
>> version (longer in length, I stripped it down for the posting). I copied
>> that code and went to phpmyadmin and did a sql query. It barfed and
>> complained mightily on the sub-select. I tried to analyze what the
>> original
>> coder was intending to do, and came up with the second one (I wrote that
>> one). That one worked. I just wanted some verification from other
>> professionals that I was reading the original correctly in what the coder
>> was intending to do -- before I proceeded on my merry way modifying code
>> in
>> some twenty modules.
>>
>> --
>> Shelly
>
> Theoretically, the two give the same results, because they're both
> Descartes' product
> of all rows. However, as Steve pointed out, SQL server might make a
> different execution
> plan if it counters a normal join. There are, however, more problems
> with this - what happens
> if the subquery returns more than one result? Also, I think sql server
> will do "jumps" on each
> row, instead of multiplying them all and then remove the ones not
> meeting the given criteria.
do you mean a 'cartesian' product? all rows in a * all rows in b == total
rows joined? i don't think that's what's going on here. it's a 'good' join
as a sub-select because it there is criteria on the sub-select that will
match rows with equality.
you are correct though re. multiple rows retuned in the sub-select. that's
another reason to do a formal join.
Navigation:
[Reply to this message]
|