Unveal Difference Between ON and WHERE Clause When Used With Joins In SQL Server

When you write sql queries, you may notice that sometimes query results are different when you use ON clause from using WHERE clause in Joins. Or you may encounter a question: is there any difference between ON and WHERE clause when used with Joins?

Now here is the answer: 
  1) For inner joins, there is no difference between the WHERE clause and ON clause. Even for performance consideration, they are the same.
  2) For left joins, there will be difference between the WHERE clause and ON clause sometimes.

This article on Codeproject has given very good examples to demonstrate the above conclusion. In that article, Case 5 uses ON clause with Left Joins while Case 6 uses WHERE clause with Left Joins. Case 5 returns three more rows than Case 6. Moreover all these extra three rows have DeptID as NULL.

So you may ask why will be difference for left joins sometimes?
Let us first take a look at definition of Where clause on MSDN: WHERE search_condition is to define the condition to be met for the rows to be returned. That is to say, WHERE clause returns rows for which the predicate/condition evaluates to TRUE. For those rows that have a NULL, WHERE clause evaluates to UNKNOWN. That's why in left outer joins, WHERE clause will exclude all rows related to DeptId IS NULL. Now you should tell why Case 5 have those three more rows, right?