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
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.
Comments
Join the conversation! Your thoughts help the community grow.