What executes the first WHERE clause or JOIN clause

Question

Which SELECT is executed first in the SELECT ?

I have a doubt about a SELECT query on this basis.

consider the example below

 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 

• Regardless, it first checks the WHERE , and then executes the INNER JOIN

• First JOIN and then check condition?

If it first executes a JOIN , then WHERE clause; how can it fulfill more when conditions for different JOIN s?

Answer 1

The conceptual procedure for processing requests:

 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 reorder offers. Here is the proof. Allows you to make 2 tables of 1,000,000 rows:

 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 

The notification will first filter most of the lines in the join state, the second in where . Look at the prepared 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 optimized query, it was first decided to evaluate the join condition for filtering strings, and secondly, it first evaluated the where clause.

Answer 2

The logical order of the phases of request processing:

• FROM - Including JOIN s
• WHERE
• GROUP BY
• HAVING
• SELECT
• ORDER BY

---

You can have as many conditions as even in your JOIN or WHERE clauses. How:

 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 .... 

Answer 3

You can link MSDN

The rows selected by the query are first filtered according to the FROM clause of the join condition, then the WHERE clause search terms, and then the HAVING search conditions. Internal joins can be specified in either the FROM clause or the WHERE clause without affecting the final result.

You can also use SET SHOWPLAN_ALL ON before executing your query to display the execution plan of your query so that you can measure the difference in performance between the two.

Answer 4

you can reference this combine optimization

 SELECT * FROM T1 INNER JOIN T2 ON P1(T1,T2) INNER JOIN T3 ON P2(T2,T3) WHERE P(T1,T2,T3) 

The nested loop combining algorithm would execute this query as follows:

 FOR each row t1 in T1 { FOR each row t2 in T2 such that P1(t1,t2) { FOR each row t3 in T3 such that P2(t2,t3) { IF P(t1,t2,t3) { t:=t1||t2||t3; OUTPUT t; } } } }