|
|
Posted by Steve on 11/12/07 16:00
"Darko" <darko.maksimovic@gmail.com> wrote in message
news:1194882858.888294.110260@v2g2000hsf.googlegroups.com...
> On Nov 12, 3:13 pm, "Steve" <no....@example.com> wrote:
>> "Darko" <darko.maksimo...@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.
>
> Yes, I meant Cartesian. Cartesian is names after Descartes, so I said
> it that way.
makes sense.
Navigation:
[Reply to this message]
|