|
|
Posted by Darko on 11/12/07 15:54
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.
Navigation:
[Reply to this message]
|