IntroductionThe purpose of this article is to show the difference between the On clause and Where clause when using with left join in SQL Server.BackgroundI have been working on SQL Server databases for more than 5 years and I was not aware of the difference between the On clause and Where clause when used with left join. Once I asked this question to our DBA and he said that there is some difference but he was not be able to explain the difference. Then I started exploring this topic myself and got some interesting observations of these two clauses when used with left join. So today I decided to share my knowledge so that everyone could benefit from my work.
Using the codeToday I will explain the difference between the On clause and Where clause when used with left join in SQL Server. When the On clause is used in an outer join, the outer table will have no effect on this On clause and all rows from the outer table will be returned and the On clause determines which rows of the subordinate table joins to the outer table. Rows of the outer table that do not meet the condition specified in the On clause in the join are extended with null values for subordinate columns (columns of the subordinate table), whereas the Where clause filters the rows that actually were returned to the final output. It's difficult to understand this from the above definition, so let's try to understand this difference with an example. Suppose we have two tables Departments (deptId, deptName) and Employees (empID, DeptID, EmpName, Salary) and deptiD is the foreign key of the department table. An employee can have only one department where as a department can have many employees.
Read more: C# Corner
QR:
Using the codeToday I will explain the difference between the On clause and Where clause when used with left join in SQL Server. When the On clause is used in an outer join, the outer table will have no effect on this On clause and all rows from the outer table will be returned and the On clause determines which rows of the subordinate table joins to the outer table. Rows of the outer table that do not meet the condition specified in the On clause in the join are extended with null values for subordinate columns (columns of the subordinate table), whereas the Where clause filters the rows that actually were returned to the final output. It's difficult to understand this from the above definition, so let's try to understand this difference with an example. Suppose we have two tables Departments (deptId, deptName) and Employees (empID, DeptID, EmpName, Salary) and deptiD is the foreign key of the department table. An employee can have only one department where as a department can have many employees.
Read more: C# Corner
QR: