1

Hello :) I've got a question on MySQL queries. Which one's faster and why? Is there any difference at all?

select tab1.something, tab2.smthelse
from tab1 inner join tab2 on tab1.pk=tab2.fk 
WHERE tab2.somevalue = 'value'

Or this one:

select tab1.something, tab2.smthelse
from tab1 inner join tab2 on tab1.pk=tab2.fk 
AND tab2.somevalue = 'value'
Dmitriy Kuznetsov
  • 861
  • 7
  • 10
  • Seems a different question to me. The original is asking about which JOIN syntax is better (ANSI vs theta). This one seems to be about whether to use the JOIN clause or the WHERE clause for filter conditions. – Phil Sandler Oct 25 '13 at 15:43
  • Yeah, I read a lot of answers on similar questions. Still I never found the one, that answers mine exactly – Dmitriy Kuznetsov Nov 06 '13 at 12:52
  • This is not a duplicate! – Arth Aug 12 '14 at 19:21

2 Answers2

1

As Simon noted, the difference in performance should be negligible. The main concern would be ensuring your query correctly expresses your intent, and (especially) you get the expected results.

Generally, you want to add filters to the JOIN clause only if the filter is a condition of the join. In most (not all) cases, a filter should be applied to the WHERE clause, as it is a filter of the overall query, not of the join itself.

AFAIK, the only instance where this really affects the outcome of the query is when using an OUTER JOIN.

Consider the following queries:

SELECT *
FROM Customer c
LEFT JOIN Orders o ON c.CustomerId = o.CustomerId
WHERE o.OrderType = "InternetOrder"

vs.

SELECT *
FROM Customer c
LEFT JOIN Orders o ON c.CustomerId = o.CustomerId AND o.OrderType = "InternetOrder"

The first will return one row for each customer order that has an order type of "Internet Order". In effect, your left join has become an inner join because of the filter that was applied to the whole query (i.e. customers who do not have an "InternetOrder" will not be returned at all).

The second will return at least one row for each customer. If the customer has no orders of order type "Internet Order", it will return null values for all order table fields. Otherwise it will return one row for each customer order of type "Internet Order".

Phil Sandler
  • 27,544
  • 21
  • 86
  • 147
0

If the constraint is based off the joined table (as yours is) then it makes sense to specify the constraint when you join.

This way MySQL is able to reduce the rows from the joined table at the time it joins, as otherwise it will need to be able to select all data that fulfills the basic JOIN criteria prior to applying the WHERE logic.

In reality you'll see little difference in performance until you get to more complex queries or larger datasets, however limiting the data at each JOIN will be more efficient overall if done well especially if there are good indexes on the joined table.

Simon at The Access Group
  • 6,932
  • 22
  • 45