Which performs first WHERE clause or JOIN clause

Question

Which clause performs first in a SELECT statement?

I have a doubt in select query on this basis.

consider the below example

SELECT *  FROM #temp A  INNER JOIN #temp B ON A.id = B.id  INNER JOIN #temp C ON B.id = C.id  WHERE A.Name = 'Acb' AND B.Name = C.Name 

1. Whether, First it checks WHERE clause and then performs INNER JOIN

2. First JOIN and then checks condition?

If it first performs JOIN and then WHERE condition; how can it perform more where conditions for different JOINs?

Answer 1

The conceptual order of query processing is:

1. FROM 2. WHERE 3. GROUP BY 4. HAVING 5. SELECT 6. ORDER BY 

But this is just a conceptual order. In fact the engine may decide to rearrange clauses. Here is proof. Let's make 2 tables with 1000000 rows each:

CREATE TABLE test1 (id INT IDENTITY(1, 1), name VARCHAR(10)) CREATE TABLE test2 (id INT IDENTITY(1, 1), name VARCHAR(10))   ;WITH cte AS(SELECT -1 + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) d FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t1(n) CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t2(n) CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t3(n) CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t4(n) CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t5(n) CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t6(n))  INSERT INTO test1(name) SELECT 'a' FROM cte 

Now run 2 queries:

SELECT * FROM dbo.test1 t1 JOIN dbo.test2 t2 ON t2.id = t1.id AND t2.id = 100 WHERE t1.id > 1   SELECT * FROM dbo.test1 t1 JOIN dbo.test2 t2 ON t2.id = t1.id WHERE t1.id = 1 

Notice that the first query will filter most rows out in the join condition, but the second query filters in the where condition. Look at the produced plans:

1 TableScan - Predicate:[Test].[dbo].[test2].[id] as [t2].[id]=(100)

2 TableScan - Predicate:[Test].[dbo].[test2].[id] as [t2].[id]=(1)

This means that in the first query optimized, the engine decided first to evaluate the join condition to filter out rows. In the second query, it evaluated the where clause first.

Answer 2

Logical order of query processing phases is:

1. FROM - Including JOINs
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

---

You can have as many as conditions even on your JOINs or WHERE clauses. Like:

Select * from #temp A  INNER JOIN #temp B ON A.id = B.id AND .... AND ...  INNER JOIN #temp C ON B.id = C.id AND .... AND ... Where A.Name = 'Acb' AND B.Name = C.Name AND ....