Post
Ask Question

On vs Where Clause in SQL Server 2012

In this article, we will see both 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
 
Employee-table-in-SQL-Server.jpg
 
Table: DepartmentTable
 
Department-table-in-SQL-Server.jpg
 

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.
  1. Select * from employeeTable E Inner join departmenttable D on E.DeptId =D.DeptID and D.EmpName='Rohatash'  
  2. GO  
  3. Select * from employeeTable E Inner join departmenttable D on E.DeptId =D.DeptID Where D.EmpName='Rohatash' 
Output
 
Differences between On and Where clauses
 

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:
  1. Select * from employeeTable E Left Outer join departmenttable D on E.DeptId =D.DeptID  
  2. GO  
  3. Select * from employeeTable E Left Outer join departmenttable D on E.DeptId =D.DeptID  
Output
 
Left Outer join without On and Where Clause 
 

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:
  1. Select * from employeeTable E Left Outer join departmenttable D on E.DeptId =D.DeptID and D.EmpName='Rohatash'  
  2. GO  
  3. Select * from employeeTable E Left Outer join departmenttable D on E.DeptId =D.DeptID Where D.EmpName='Rohatash'  
Output
 
Left Outer join with On and Where Clause 
 
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.


Similar Articles