SQL Server Performance Forum – Threads Archive

columns in join and where

can anybody tell me the difference (performance difference) between the below mentioned queries… select * from Tab1 a
join Tab2 b on a.Col1 = b.Col1
where a.col3 = ‘abc’ and b.col2 = 20 select * from Tab1 a
join Tab2 b on a.Col1 = b.Col1
and b.col2 = 20
where a.col3 = ‘abc’ Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

---

In QA, create a single script with the query statement that you want to compare. Now make sure QA is returning results in a grid, then press Ctrl-L to ask SQL Server for an Estimated Execution Plan. You will see a graphical representation of the two statements. Before each graphic, there’s a small header saying "Query 1: Query cost (relative to the batch): xx %" Your two queries should both take up 50% of the batch cost, as SQL Server is smart enough to tell that it’s really the same query, so it will execute the two in exactly the same way. For the sake of repeatability, ease of maintenance and clarity, please keep the filtering stuff in the WHERE clause.

---

It doesnt matter if you use Joins but matters if you use Left or Right outer joins. Always filter data using Where clause Madhivanan Failing to plan is Planning to fail

---

Oh yes, and last week we found a ‘bug’ in someone’s script, where they were using a filter expression in an OUTER join. This caused the query to behave unexpectedly.

---

]]>