Is condition in the JOIN clause evil SQL

Question

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

score 7 · Accepted Answer · edited May 23 '17 at 12:23

7

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

edited May 23 '17 at 12:23

Community

1
1

answered May 05 '13 at 05:46

Mudassir Hasan

28,083
20
99
133

1

assuming op is not eliminating NULLs in the where clause then you are correct about left/right which is a good assumption, but i like to state assumptions. Also note for clarity that mysql does not support FULL OUTER JOINS but sql-server (op chose both tags) does. – gillyspy May 05 '13 at 05:51
this is just what i was searching on google , how NULL values is handled in JOIN and WHERE..thanks for the input.. – Mudassir Hasan May 05 '13 at 05:52

Bohemian · Answer 2 · 2013-05-05T05:59:51.000

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.

score 1 · Answer 3 · answered May 05 '13 at 07:51

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

Is condition in the JOIN clause evil SQL

3 Answers3