Difference between using condition in where clause and on in joins in sql server?

Question

what is the difference between using condition in where clause and on in joins in sql server?

Senthilkumar · Answer

Hi, The condition usage in the "on" will improve the performance of the query result. usually the joins will happen first and the filters happens next. The joining of the columns based on the mapping fields will happen. Suppose you have 1000 records in the first table and joining another 1000 records in another table. It will map based on the condition specified. Once mapping done, it will have the table where it needs to do filter in the where clause. It is bit costly process and it needs extra filter. when you specify the same thing in the on then it will filter the results when the column mapping happens. It does not require addition step to filter. For example, SELECT P.ProductID, P.ProductName,P.Rate,S.Qty,S.SoldDate,(P.Rate*S.Qty) AS [Total] FROM Products P INNER JOIN Sales S ON S.ProductID = P.ProductID WHERE DATEPART(Year,S.SoldDate) = 2012 This can be done effectively in the following way. SELECT P.ProductID, P.ProductName,P.Rate,S.Qty,S.SoldDate,(P.Rate*S.Qty) AS [Total] FROM Products P INNER JOIN Sales S ON S.ProductID = P.ProductID AND DATEPART(Year,S.SoldDate) = 2012 This will do the filter when the column mapping happens between product and sales table. Hope this will help you.

MuthuMari M · Answer

Hi, ON clauses of a SQL statement will create a faster query when compared to the same SELECT statement if the same criteria was placed in the WHERE clause. The ON clause is executed before the WHERE clause so I figured criteria there would perform better. When you join two tables together it creates a cross-product then it applies the WHERE clause to the resultant table join. pls refer this url for more info : http://www.bennadel.com/blog/284-SQL-Optimization-And-ON-Clause-vs-WHERE-Clause.htm thanks. If this post is useful then mark it as " Accepted Answer "