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

ponveni

14y2.4k

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Senthilkumar14y ago
Accepted 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.
0
MuthuMari M14y ago
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"
0