SQL JOIN – WHERE clause vs. ON clause

After reading it, this is not a duplicate of Explicit vs Implicit SQL Joins.
The answer may be related (or even the same) but the question is different.

What is the difference and what should go in each?

If I understand the theory correctly, the query optimizer should be able to use both interchangeably.

• Does not matter for inner joins

• Matters for outer joins

  a. WHERE clause: After joining. Records will be filtered after join has taken place.

  b. ON clause – Before joining. Records (from right table) will be filtered before joining. This may end up as null in the result (since OUTER join).

Example: Consider the below tables:

1. documents:

   id	name
   1	Document1
   2	Document2
   3	Document3
   4	Document4
   5	Document5

2. downloads:

   id	document_id	username
   1	1	sandeep
   2	1	simi
   3	2	sandeep
   4	2	reya
   5	3	simi

a) Inside WHERE clause:

   SELECT documents.name, downloads.id
     FROM documents
     LEFT OUTER JOIN downloads
       ON documents.id = downloads.document_id
     WHERE username="sandeep"

For above query the intermediate join table will look like this.

After applying the WHERE clause and selecting the listed attributes, the result will be:

b) Inside JOIN clause

   SELECT documents.name, downloads.id
   FROM documents
     LEFT OUTER JOIN downloads
       ON documents.id = downloads.document_id
         AND username="sandeep"

For above query the intermediate join table will look like this.

Notice how the rows in documents that did not match both the conditions are populated with NULL values.

After Selecting the listed attributes, the result will be: