CODEWITHSUNDEEP
sqlsql-server
Deviprasad Das
Deviprasad Das

Reputation: 4363

Performance of ON Clause over WHERE Clause

Can anyone please tell me Which of the following queries will have better performance?

SELECT 
      * 
FROM 
      [TABLE1] T1 
      INNER JOIN [TABLE2] T2 
      ON T2.[FK_ID] = T1.[PK_ID] 
WHERE 
      T2.[ACTIVE] = 1; 

SELECT 
      * 
FROM 
      [TABLE1] T1 
      INNER JOIN [TABLE2] T2 
      ON T2.[FK_ID] = T1.[PK_ID] AND 
      T2.[ACTIVE] = 1;

Upvotes: 4

Views: 212

Answers (2)

gbn
gbn

Reputation: 432471

They are identical: the optimizer is clever enough to work this out.

Not least, SQL is declarative so you're asking the optimizer for something but not telling it how to do it.

However, the first one is "correct" in that you are separating JOIN and WHERE.

Upvotes: 3

Pranay Rana
Pranay Rana

Reputation: 176936

hpe this will give you answer : http://bytes.com/topic/sql-server/answers/850159-performance-conditions-where-clause-vs-conditions-inner-join

Upvotes: 3

Related Questions