On vs Where Clause in SQL Server 2012

In this article we will see both of the join conditions, inner join and left outer join with On and Where clauses. When an inner join is used there is no difference between On and Where clauses. You get the same result from both. But with left joins you do get the difference between On and Where in SQL Server. So let's have a look at a practical example of how to get the difference between the On and Where clauses in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

Creating table in SQL Server

Now we create two tables named Employee table and Department table and insert data into both tables. The following is the sample data for the Employeetable and DepartmentTable:

Table: EmployeeTable

Table: DepartmentTable

Case 1:  Differences between On and Where clauses using inner join

When you use an inner join there is no difference between the on and where clauses. Both produce the same result as in the following.

Select * from employeeTable E Inner join departmenttable D on E.DeptId =D.DeptID and D.EmpName='Rohatash'

GO

Select * from employeeTable E Inner join departmenttable D on E.DeptId =D.DeptID Where D.EmpName='Rohatash'

Output

Case 2: Left Outer join without On and Where Clause

When you use a Left Outer join without an On or Where clause, there is no difference between the On and Where clause. Both produce the same result as in the following.

First we see the result of the left join using neither an On nor a Where clause. Both produce the same result as in the following:

Select * from employeeTable E Left Outer join departmenttable D on E.DeptId =D.DeptID

GO

Select * from employeeTable E Left Outer join departmenttable D on E.DeptId =D.DeptID

Output

 

 

Case 3: Left Outer join with On and Where Clause

 

But when you use a Left Outer join with an On or a Where clause, there is a difference between use of an On or a Where in SQL Server. They produce different results as in the following:

 

Select * from employeeTable E Left Outer join departmenttable D on E.DeptId =D.DeptID and D.EmpName='Rohatash'

GO

Select * from employeeTable E Left Outer join departmenttable D on E.DeptId =D.DeptID Where D.EmpName='Rohatash'

 

Output

 

 

You can see in the result, the first one returns all records from the left table and disregards the extra condition. The second works just fine and only returns records for EmpName='Rohatash'. The condition is applied before the join when the ON clause is applied to the table and after the join in the WHERE clause. 

 

I would be waiting for your feedback.