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>.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
