where clause and on clause

Question

when we join two tables

1) select t1.id,t2.id from t1 join t2 on t1.id=t2.id where t1.id>5

which condition is checked first? where t1.id>5 ? or join tables first then filter

 

2)select t1.id,t2.id from t1 join t2 on t1.id=t2.id and t1.id>5

which is better in the above 2?

Answer 1

Hi Student,

 

Logically, the FROM clause is evaluated first, including the join predicates.

Practically, for INNER JOINS, the order of evaluation does not matter and the query optimizer may choose to filter whichever predicate it thinks will result in a more efficient plan.

I suggest you write the queries both ways and look at the execution plan. I am quite sure you will find that the optimizer will disregard this syntax difference and generate the same plan for both.

Note that for OUTER JOINs, the story is different as the evaluation order may affect the query results.

 HTH

---

Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--

Answer 2

The following steps show the processing order for a SELECT statement.

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

 

---

Jon Royales

Answer 3

In addition to the above replies, you may find this article http://pratchev.blogspot.com/2007/05/anatomy-of-query.html very helpful.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog