What is the better way of writing this query?

Question

I have a simple join query as follows.

select * 
from user u left join 
     user_roles ur 
     on  ur.user_id = u.user_id 
     and ur.created_by = 'Mike'
where u.division = 'some division'

OR

select * 
from user u left join 
     user_roles ur 
     on  ur.user_id = u.user_id 
where u.division = 'some division' 
and   ur.created_by = 'Mike'

The point is here is I have moved the additional filter clause condition from left join to where clause.

Does it make any difference if I join two tables on more than column or put it in where clause?

Answer 1

Yes it makes a big difference.

You are basicalling nullifying the left join and making it an inner join, hiding user roles not created by Mike

Bell Mike
Toe Mike
Bob Jerry

first query returns

Bell Mike
Toe Mike
Bob NULL

second Query returns

Bell Mike
Toe Mike