Michael Stassen wrote:
Chris wrote:
<snip>
> select *
> from customers c, issues i, customer_issues ci
> where c.customerid=ci.customerid
> AND ci.issueid=i.issueid;
Chris wrote:
<snip>
> That query should be the same as this one:
> select *
> from customers c
> inner join customer_issues ci on (c.customerid=ci.customerid)
> inner join issues i on (ci.issueid=i.issueid);
>
> I don't like writing my queries that way because the order of the joins
> needs to be very specific and as you add more tables, getting the order
> wrong can make things slow (and hard to track down).
I think you have that precisely backwards. Putting the join conditions
in the WHERE clause makes it easy to accidentally forget one. Many,
many "Why isn't this query working?" questions on this list have been
the result of missing join conditions that became obvious when the query
was rewritten to explicitly give the join conditions in ON clauses. As
you add more tables, the likelihood of making that mistake and the
difficulty of detecting it increase.
Whether you put it in the WHERE or ON clause, the correct join condition
is the same. Like you, I used to write all my joins as implicit joins
with the join conditions in the WHERE clause (largely becuase most of
the examples in the manual were written that way). It worked fine for
the relatively simple queries I was writing at the time, but as my
queries became more complex, that format became more and more unwieldy.
With join conditions for 4 or 5 tables and 3 or 4 row restrictions in
the WHERE clause, it gets to be a mess. Faced with that, and seeing the
problems others have had, I've gradually switched to writing all of my
joins with explicit join conditions in ON clauses.
I also have no idea what you mean by "getting the order wrong can make
things slow." The order in which you write inner joins should have no
effect on how your query is performed. The optimizer will, in fact,
consider each of the possible orderings that produce correct results and
choose the one it calculates to be the most efficient. That is,
SELECT *
FROM customers c
JOIN customer_issues ci ON c.customerid = ci.customerid
JOIN issues i ON ci.issueid = i.issueid
WHERE c.customerid = 13;
is identical to
SELECT *
FROM issues i
JOIN customer_issues ci ON ci.issueid = i.issueid
JOIN customers c ON c.customerid = ci.customerid
WHERE c.customerid = 13;
in that mysql will do the same thing to satisfy either one. This can be
verified through the use of EXPLAIN.
In short, there is absolutely no speed benefit in putting your join
conditions in the WHERE clause. In fact, there is a small inefficiency
in putting them there, as mysql must then parse the WHERE clause into
join conditions and row restrictions. This is very fast, of course, but
doesn't have to be done if the join conditions are explicitly given in
ON clauses.
Finally, you will discover that some of your queries which use the
"comma as implicit join operator" will break when you upgrade to mysql
5, because starting with mysql 5, implicit joins are given lower
precedence than explicit joins, as specified by the SQL standard. See
the manual for the details:
<http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html>
<http://dev.mysql.com/doc/refman/5.0/en/join.html>.
Hmm. Must be a recent change, I've had problems with that in the past.
Thanks for the pointers.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
