List:General Discussion« Previous MessageNext Message »
From:SGreen Date:May 11 2005 1:39pm
Subject:Re: possible join
View as plain text  
news <news@stripped> wrote on 05/11/2005 09:09:36 AM:

> In article <4281B3C1.9070708@stripped>,
> Peter Brawley <peter.brawley@stripped> writes:
> 
> > Scott, sorry, my mistake,
> >   SELECT price
> >   FROM fedex_zones z
> >   INNER JOIN fedex_rates r ON z.zone=r.zone AND z.zip=94947
> >   WHERE r.weight = 25;
> > PB
> 
> Although correct, many people consider this bad style - the ON clause
> of the JOIN should contain only the join condition(s). So it would be
> better to say
> 
>   SELECT price
>   FROM fedex_zones z
>   JOIN fedex_rates r ON z.zone = r.zone
>   WHERE r.weight = 25
>     AND z.zip = 94947
> 
> 

I agree, in principle, with the comment about preferred style. However, 
certain combinations of ON conditions can trigger the use of different 
indexes by the optimizer based on the conditions being matched. The final 
query results may be the same but if you want the best possible 
performance out of your JOIN queries, sometimes you need to break the 
style rule.

I list two other "correct" if not properly styled ways of writing the same 
query below. Based on the optimizer and its choice of indexes used during 
the JOIN processing phase of their executions, these may have (slightly) 
different performance characteristics.

SELECT price
FROM fedex_zones z
JOIN fedex_rates r 
        ON z.zone = r.zone
        AND r.weight = 25
WHERE z.zip = 94947

SELECT price
FROM fedex_zones z
JOIN fedex_rates r 
        ON z.zone = r.zone
        AND r.weight = 25
        AND z.zip = 94947

As I stress, these should all produce the same results. The one that will 
perform "best" depends on both the data composition and the indexes on the 
tables. The EXPLAIN command will expose any differences in the execution 
plans for each of these queries. If none of them show a difference in 
their execution plans, then each of them is interchangeable for the other. 
That means that you cannot make a decision of which form to use based on 
performance. The form with the constant conditions in the WHERE clause 
(the one from the last post) would then be the preferred way of writing 
this query when performance is not an issue.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Thread
possible joinScott Haneda11 May
  • Re: possible joinPeter Brawley11 May
    • Re: possible joinScott Haneda11 May
      • Re: possible joinmfatene11 May
      • Re: possible joinPeter Brawley11 May
      • just hiUnknown Sender11 May
        • Re: just hiJohan Höök11 May
      • Re: possible joinHarald Fuchs11 May
        • Re: possible joinSGreen11 May
        • Re: possible joinPeter Brawley11 May
Re: just hiNestor Florez11 May