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.

Which version of sql are you using? What does your underlying data look like and what does the output look like? — iamdave
– iamdave, Commented Jun 22, 2017 at 9:59

Zohar Peled · Accepted Answer · 2017-06-27 09:35:40Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related