news <[EMAIL PROTECTED]> wrote on 05/11/2005 09:09:36 AM:
> In article <[EMAIL PROTECTED]>,
> Peter Brawley <[EMAIL PROTECTED]> 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
