SQL Different between Left join on... and Left Join on..where

Question

I have two sql to join two table together:

select top 100 a.XXX
              ,a.YYY
              ,a.ZZZ
              ,b.GGG
              ,b.JJJ
from table_01 a 
    left join table_02 b
        on a.XXX = b.GGG
            and b.JJJ = "abc"
            and a.YYY between '01/08/2009 13:18:00' and '12/08/2009 13:18:00'

---

select top 100 a.XXX
              ,a.YYY
              ,a.ZZZ
              ,b.GGG
              ,b.JJJ 
from table_01 a 
    left join table_02 b
        on a.XXX = b.GGG 
where b.JJJ = "abc"
    and a.YYY between '01/08/2009 13:18:00' and '12/08/2009 13:18:00'

The outcome of them is different but I don't understand the reason why.
I would be grateful if I can get some help here.

Answer 1

Whenever you are using LEFT JOIN, all the conditions about the content of the right table should be in the ON clause, otherwise you are effectively converting your LEFT JOIN to an INNER JOIN.

The reason for that is that when a LEFT JOIN is used, all the rows from the left table will be returned. If they are matched by the right table, the values of the matching row(s) will be returned as well, but if they are not matched with any row on the right table, then the right table will return a row of null values.
Since you can't compare anything to NULL (not even another NULL) (Read this answer to find out why), you are basically telling your database to return all rows that are matched in both tables.
However, when the condition is in the ON clause, Your database knows to treat it as a part of the join condition.