What is the best way to write this query?

Question

I have a simple connection request 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' 

In this case, I have moved the condition of the additional filter condition from the left join to where clause.

It doesn't matter if I join two tables more than a column or put it in a where clause?

Answer 1

Yes, it matters a lot.

You have a base value that nullifies the left join and makes it the inner join, hiding user roles not created by Mike

 Bell Mike Toe Mike Bob Jerry 

first request returns

 Bell Mike Toe Mike Bob NULL 

the second query returns

 Bell Mike Toe Mike 

Answer 2

Yes - it is important.

Any filter on a joined table must be in a join for proper operation — that is, the first will work, the second will not.

Try the following (SQL Server syntax) to see how the results differ.

 declare @u table (user_id int, division varchar(20)) declare @ur table (user_id int, created_by varchar(10)) insert @u values (1,'sales'), (2,'marketing'), (3,'engineering') insert @ur values (1, 'mike'), (3,'james'), (3,'mike') select * from @uu left join @ur ur on ur.user_id = u.user_id and ur.created_by = 'Mike' select * from @uu left join @ur ur on ur.user_id = u.user_id where ur.created_by = 'Mike'