1

I need to know which it is the best option in performance. For example i have a multiple joins in a query:

Option 1: WHERE Clause

SELECT * 
FROM TableA A
  INNER JOIN TableB B ON B.IdA = A.Id
  INNER JOIN TableC C ON C.IdB = B.Id
  INNER JOIN TableD D ON D.IdC = C.Id
  INNER JOIN TableE E ON E.IdD = D.Id
WHERE A.Colum1 = 'SOME'
  AND B.Colum2 = 1
  AND C.Colum3 IS NOT NULL

Option 2: JOIN Condition

SELECT * 
FROM TableA A
  INNER JOIN TableB B ON B.IdA = A.Id AND A.Colum1 = 'SOME' AND B.Colum2 = 1
  INNER JOIN TableC C ON C.IdB = B.Id AND C.Colum3 IS NOT NULL
  INNER JOIN TableD D ON D.IdC = C.Id
  INNER JOIN TableE E ON E.IdD = D.Id

Sorry for my english. Regards

Israel Pina
  • 17
  • 3
  • 1
    Depends on the indexes on the tables. You should be able to look at the query plans for both of these and see which one is better suited for your current schema. – TTeeple Aug 02 '16 at 20:12
  • 6
    These will optimize the same (assuming you're only talking about **inner** joins - **outer** joins are different) - SQL Server does not short circuit join criteria depending on where you place your filters, internally both of these will be re-written to the same basic form. However for readability etc. you should get in the habit of separating your *join* criteria from your *filter* criteria. So for me, personally, I much prefer the first query, because if I have a problem with my joins, I can look at the `ON` clauses, and if I have a problem with my filters, I can look at the `WHERE` clauses. – Aaron Bertrand Aug 02 '16 at 20:15
  • 1
    @techspider Can you show an example where the second form optimizes **better** than the first form? – Aaron Bertrand Aug 02 '16 at 20:18
  • @techspider You imply some type of advantage to "don't need to extract all the rows from the next set of joins" - could you elaborate? It certainly *reads* like you think the second form is more efficient. – Aaron Bertrand Aug 02 '16 at 20:54

0 Answers0