Difference Between AND Operator with ON Clause and Where while Using with Left Joins in SQL

 Difference Between AND Operator with ON Clause and Where   while Using  Left Joins

Many People have a believe that wriitng filter condition in where clause or  after left join on  Clause are same
There is a big difference between them
I will Explain with an Example
First I am Creating two table to explain difference between them
Script 1

create table table1
(empid int ,empname nvarchar(50),orderid int)


create table table2
(orderid int,orderdate  datetime)

insert into table1 values(1,'Test',10),(2,'Test1',9),(3,'Test2',8),(4,'Test3',7),(5,'Test4',6),(6,'Test5',5),(7,'Test6',4),(8,'Test7',null),(9,'Test8',null)
insert into table2 values(4,getdate()),(5,getutcdate()),(6,getdate()-1),(7,getdate()+2)

Now I will run  3 more script to clear the difference between them.
Script 2

select t1.empid ,t1.empname,t2.orderid from table1 t1 left outer join table2 t2 on t1.orderid=t2.orderid

Output
empid empname orderid
1 Test          NULL
2 Test1 NULL
3 Test2 NULL
4 Test3 7
5 Test4 6
6 Test5 5
7 Test6 4
8 Test7 NULL

9 Test8 NULL

It's  a simple left join where field are matched it's showing result rest Null values

Let's run below script 3


Script 3

select t1.empid ,t1.empname,t2.orderid from table1 t1 left outer join table2 t2 on t1.orderid=t2.orderid and t2.orderid is null

Output
empid empname orderid
1 Test   NULL
2 Test1 NULL
3 Test2 NULL
4 Test3 NULL
5 Test4 NULL
6 Test5 NULL
7 Test6 NULL
8 Test7 NULL

9 Test8 NULL

If you will notice that i have added a and condition after on clause and it changed all order id values to Null
The reason behind this is that on joining the two tables at that time only it is checking that wheather there is any null value in table 2 or not and if you will check in table 2 there is no null 
values so from where null values are coming in order id column
The reason is simple we have performed Left Join wheather codition matches or not it will show the result of left table so as it is showing data of left table completely and as there is null order id in table 2 the condition is not matching so it's showing all null in order id

Now let's run script 4

Script 4

select t1.empid ,t1.empname,t2.orderid from table1 t1 left outer join table2 t2 on t1.orderid=t2.orderid 

where t2.orderid is null

Output

empid empname orderid
1 Test    NULL
2 Test1 NULL
3 Test2 NULL
8 Test7 NULL

9 Test8 NULL

Now in script 4 i have added the same condition in where clause in script 3 the same condition was after on clause
If you will compare output of script 3 and script 4 the output are different
the reason is that where clause is filtering the whole recordset after joining the tables
and showing only those record which matches the where criteria after joining the tables

Hope that you understand the difference between them 

NOTE:There is no difference between the "Where" clause and "On" clause when used with inner join.