List:General Discussion« Previous MessageNext Message »
From:Matt Monaco Date:November 29 2005 9:42pm
Subject:Re: comma-separated JOINS
View as plain text  
I just wanted to thank you for such a thorough response, it has helped me a 
lot as I've always ignored the existence of joins.

Thanks,
Matt


<SGreen@stripped> wrote in message 
news:OFAC95B5B1.97913632-ON852570C8.004D0376-852570C8.004EBE5F@ style="color:#666">stripped...
> The sequence you use to JOIN a set of tables is sometimes determined
> completely by the logic of the JOIN. You should perform all of your CROSS
> JOINs and INNER JOINs first (a comma is equivalent to a CROSS JOIN but
> starting with 5.0.12 the comma has a LOWER evaluation priority than an
> explicit JOIN so watch out!!) then list your LEFT or RIGHT JOINs. It is
> very difficult to have both LEFT and RIGHT joins in the same query and get
> a correct result. You can nearly always transform a query containing both
> LEFT JOIN and a RIGHT JOIN into a query using a CROSS JOIN and a LEFT
> JOIN.
>
> When given the opportunity in a query where several equivalent
> constructions are available (as in a query that uses nothing but INNER
> JOINs) I try to list the "smallest" table first. It may not have the least
> number of physical records but should have the fewest number of rows
> returned. JOINs are geometrically expensive operations and the fewer rows
> you need to evaluate between stages of your JOINs, the more likely you are
> to have better performance.
>
> Try to keep the columns that are references to or are referenced by other
> tables indexed. The MySQL query engine (at least until 5.0) will use just
> one index per table per query.
>
> Which tables the columns come from in your results does not matter so long
> as you construct your <table reference> correctly.
>
> Above all else, I strongly discourage the use of comma-joins. The explicit
> JOIN syntax is not only less ambiguous but is the only way to declare an
> outer JOIN with MySQL. When you use the explicit JOIN syntax, you are also
> less likely to form unintentional Cartesian products which can absolutely
> clobber a query's performance.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
> "Matt Monaco" <mmonaco3@stripped> wrote on 11/29/2005 12:22:45 AM:
>
>> What would be the most efficient order to join in?  Say I have one main
>> table with most columns (I assume this should be the main table of the
>> query) then each table relates to the next, is it as simple as putting
> them
>> in order?
>>
>>
>>
>> "Peter Brawley" <peter.brawley@stripped> wrote in message
>> news:438BAFCA.4030508@ style="color:#666">stripped...
>> > Matt,
>> >
>> > >When using JOINS by the simply supplying a comma separated list of
>> > tables in
>> > >the FROM clause, is the ON argument normally associated with a join
>> > intended
>> > >to be addressed in the WHERE clause, or should ON still be used?
>> >
>> > There's no ON clause for a join specified by a WHERE clause, and
> that's
>> > one reason specifiying joins with JOIN ... ON ..  is almost always
>> > preferable--it entirely disambiguates the join for the writer,
> readers,
>> > and those others who later will have to divine what you meant :-) .
>> >
>> > PB
>> >
>> > -----
>> >
>> > Matt Monaco wrote:
>> >
>> >>When using JOINS by the simply supplying a comma separated list of
> tables
>> >>in
>> >>the FROM clause, is the ON argument normally associated with a join
>> >>intended
>> >>to be addressed in the WHERE clause, or should ON still be used?
>> >>
>> >>// Comma separated join
>> >>SELECT u.*, a.city FROM users u, addresses a WHERE u.id=a.user_id;
>> >>
>> >>// Actual JOIN clause
>> >>SELECT u.*, a.city FROM users u INNER JOIN addresses a ON
> u.id=a.user_id;
>> >>
>> >>
>> >>// Query style in question
>> >>SELECT u.*, a.city FROM users u, addresses a ON u.id=a.user_id;
>> >>
>> >>If not ON, is there at least another viable argument?  The reason I'm
>> >>interested is for a query involving 5 or 6 tables and WHERE arguments
>> >>which
>> >>do not deal with the relationships.  I would like to assure the
> efficiency
>> >>of this query.
>> >>
>> >>
>> >>Thanks in advance,
>> >>Matt
>> >>
>> >>
>> >>
>> >
>> >
>> > -- 
>> > No virus found in this outgoing message.
>> > Checked by AVG Free Edition.
>> > Version: 7.1.362 / Virus Database: 267.13.8/184 - Release Date:
> 11/27/2005
>> >
>>
>>
>>
>> -- 
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>
> 


Thread
comma-separated JOINSMatt Monaco29 Nov
  • Re: comma-separated JOINSPeter Brawley29 Nov
  • Re: comma-separated JOINSMatt Monaco29 Nov
    • Re: comma-separated JOINSSGreen29 Nov
    • Re: comma-separated JOINSMatt Monaco29 Nov