Join vs Where Clause

What is difference in having a condition in Join vs having the condition in where clause?

Short answer:

1. Does not matter for inner joins.

2. Matters for outer joins since outer join (say left) would join with all value from left table and then filter values. Depending on where the condition is mentioned, the filtration would happen differently

a. In Join: Before joining.Thus the is possibility of having null values of right table.

b. in where: After joining. Thus, the whole records (after join) would be filtered.

Long answer:

Consider the below tables:

1. Document:

2. HasDownloaded:

Now, the query (with where clause):

select doc.doc_name, hasd.downlaodID from Document doc left outer join HasDownloaded hasd on doc.doc_id = hasd.doc where member_name='sandeep'

Output:

Other query (with join clause):

select doc.doc_name, hasd.downlaodID from Document doc left outer join HasDownloaded hasd on doc.doc_id = hasd.doc and member_name='sandeep'

Output:

References:

1. http://stackoverflow.com/questions/219046/help-with-a-where-on-a-left-join-sql-query#219053