When returning a result that consists of data from multiple tables is it better to put the WHERE criteria under the WHERE statement or in the FROM statement when specifying the table join.

ex:

SELECT a,b,c
FROM table1 INNER JOIN table2 INNER JOIN table3
WHERE
b=1 AND c IS NOT NULL

OR

SELECT a,b,c
FROM table1 INNER JOIN table2 AND b=1
INNER JOIN table3 AND c IS NOT NULL

Thanks

neither will run efficiently. YOu have not stated what columns you are running your inner joins on. that is your first step.

Oops lol, mind that mistake

Is there a difference how MySQL will perform each query? Which method is faster?

SELECT a,b,c
FROM table1 INNER JOIN table2 ON y1=y2 INNER JOIN table3 ON y3=y4
WHERE
b=1 AND c IS NOT NULL

OR

SELECT a,b,c
FROM table1 INNER JOIN table2 ON y1=y2 AND b=1
INNER JOIN table3 ON y3=y4 AND c IS NOT NULL

I'm going to go out on a limb here and say that in general, I'd assume that having the limitations in the join on clause would be better than all conditions in the where clause. My understanding is that something like your first query will join the tables and then from those joins use the where clause. Whereas the second query will be more selective in the join and will search through less records.

Hard to come up with an elegant way to try to explain that...sorry. Of course, that is just my assumption. http://dev.mysql.com/doc/refman/4.1/en/query-speed.html goes through the optimization processes.

Of course, it's all going to depend on the data in your tables, table size, indices, locking, etc. The best way is to run both and compare!