CODEWITHSUNDEEP
mysqlsqlsql-serverjoin
kiriloff
kiriloff

Reputation: 26333

Is condition in the JOIN clause evil SQL

Is it better to have SQL condition in the JOIN clause or in the WHERE clause ? Is SQL engine optimized for either way ? Does it depend on the engine ?

Is it always possible to replace condition in the JOIN clause by a condition in the WHERE clause ?

Example here to illustrate what i mean with condition

SELECT      role_.name
FROM        user_role
INNER JOIN  user ON user_role.user_id_ = user.id AND
                    user_role.user_id_ = @user_id
INNER JOIN  role ON user_role.role_id = role_.id

vs.

SELECT      role_.name
FROM        user_role
INNER JOIN  user ON user_role.user_id_ = user.id
INNER JOIN  role ON user_role.role_id = role_.id
WHERE       user.id = @user_id

Upvotes: 4

Views: 219

Answers (3)

Ahmed
Ahmed

Reputation: 101

By looking at the plan generated for both the queries we can see that having the condition in the INNER JOIN or WHERE clause generates the same plan.

But the problem with using the condition in the WHERE clause you'll not be able to handle OUTER JOINs

Upvotes: 1

Bohemian
Bohemian

Reputation: 425003

Having the non-key condition in the join clause is not only OK, it is preferable especially in this query, because you can avoid some joins to other tables that are further joined to the table to which the on clause belongs.

Where clause is evaluated after all joins have been made - it's a filter on the result set. But by putting the condition in the join clause, you can stop the rows being joined at he time they're bing joined.

In your case it makes no difference, because you don't have any following tables, but I use his technique often to gain performance in my queries.

Upvotes: 4

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

SQL condition in JOIN clause and in WHERE condition are equivalent if INNER JOIN is used.

Otherwise if any other JOIN is used like LEFT/RIGHT than after matching rows based on condition , another step occurs which is addition of OUTER ROWS , ie non matching rows .

WHERE condition simply filters out all non matching rows.

See this thread

Upvotes: 7

Related Questions