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

Answer 1

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