Posted by Darko on 11/12/07 03:08

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.

[Back to original message]