Is there any difference between these two sql queries?

Question

Is there any difference between these two queries? Is it better or faster to put the condition in the JOIN or the WHERE part?

SELECT id
FROM fields
INNER JOIN auth
ON a_id = f_a_id
AND a_u_id = $u_id
WHERE id = $id
LIMIT 1

or

SELECT id
FROM fields
INNER JOIN auth
ON a_id = f_a_id
WHERE id = $id
AND a_u_id = $u_id
LIMIT 1

Answer 1

The answer to that question is technically DBMS-dependent, though the answer should be that they are equivalent.

More generally, an SQL query describes the data you want back, but it does not tell the database how to assemble the data. People will sometimes describe the parts of a query by spelling out an algorithm they say the query represents, but this is at best a "reference algorithm"; the database only needs to somehow provide the same results that such an algorithm would produce. This is in contrast to the "procedural mindset"[1] underlying typical code in the application tier (e.g. Java, C#, ...).

In fact it's not merely that the database might choose the exact same steps for both of those queries; the optimizer would be within its rights to rewrite the query from one representation to the other as part of its "reasoning" to assemble an execution plan.

In that light, you should use the syntax that best describes the query. So if a predicate describes the relationship between rows from two tables (or subqueries) - such as TABLE_1.ID = TABLE_2.FK_ID or the like - it should be in the ON clause of the join; but if logically it's just a filter on the joined data, it should be in the WHERE clause.

As several people have noted, the semantics of an outer join's ON clause are noticeably different; so it is often necessary (or at least prudent) to put a predicate in the ON clause even though it applies only to the optional table (rather than the relationship between the tables).

[1] - I say "procedural mindset", meaning the idea that your program is a list of steps to be executed. In a modern environment with good optimization, this is still not perfectly true; and recognizing the imperfection of DBMS optimizers, it is also not perfectly true that one can always ignore the steps that will be used to implement a query. But those are the respective ideals.

Answer 2

To all intents and purposes, they're the same. There's no particular advantage to putting predicates in the WHERE or the ON

This advice only applies to INNER JOINs