SQL 哪个先执行 WHERE 子句或 JOIN 子句

Which performs first WHERE clause or JOIN clause

提问by MohanKrishnaRS

Which clause performs first in a SELECTstatement?

哪个子句在SELECT语句中首先执行？

I have a doubt in selectquery on this basis.

我对此有疑问select。

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 WHEREclause and then performs INNER JOIN

2. First JOINand then checks condition?

1. 是否，首先检查WHERE子句然后执行INNER JOIN

2. 先JOIN检查条件？

If it first performs JOINand then WHEREcondition; how can it perform more where conditions for different JOINs?

如果它先执行JOIN然后WHERE条件；它如何为不同的JOINs执行更多 where 条件？

回答by Giorgi Nakeuri

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. Lets make 2 tables with 1000000 rows each:

但这只是一个概念上的顺序。事实上，引擎可能会决定重新排列子句。这里有证据。让我们创建 2 个表，每个表有 1000000 行：

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:

现在运行 2 个查询：

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 first will filter most rows out in joincondition, second in wherecondition. Look at produced plans:

注意首先将过滤join条件中的大多数行，其次是where条件中。查看生成的计划：

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

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

1 TableScan - 谓词：[Test].[dbo].[test2].[id] as [t2].[id]=(100)

2 TableScan - 谓词：[Test].[dbo].[test2].[id] as [t2].[id]=(1)

Which means that in first query optimized decided first to evaluate joincondition to filter out rows, in second it evaluated whereclause first.

这意味着在第一个优化的查询中决定首先评估join条件以过滤掉行，在第二个它where首先评估子句。

回答by sqluser

Logical order of query processing phases is:

查询处理阶段的逻辑顺序是：

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

1. FROM- 包括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 WHEREclauses. Like:

即使在您的JOINs 或WHERE子句上，您也可以有尽可能多的条件。喜欢：

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

回答by Rahul Tripathi

You can refer MSDN

你可以参考MSDN

The rows selected by a query are filtered first by the FROM clause join conditions, then the WHERE clause search conditions, and then the HAVING clause search conditions. Inner joins can be specified in either the FROM or WHERE clause without affecting the final result.

查询选择的行首先由FROM 子句连接条件过滤，然后是WHERE 子句搜索条件，然后是HAVING 子句搜索条件。可以在 FROM 或 WHERE 子句中指定内连接，而不会影响最终结果。

You can also use the SET SHOWPLAN_ALL ONbefore executing your query to show the execution plan of your query so that you can measure the performance difference in the two.

您还可以使用SET SHOWPLAN_ALL ONbefore 执行您的查询来显示您的查询的执行计划，以便您可以衡量两者的性能差异。

回答by Hymany

you can refer to this join optimization

你可以参考这个join优化

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 join algorithm would execute this query in the following manner:

嵌套循环连接算法将以下列方式执行此查询：

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;
      }
    }
  }
}

回答by Weihui Guo

If you come to this site for the question about logical query processing, you really need to read this article on ITProToday by Itzik Ben-Gan.

如果您来到本网站询问有关 的问题logical query processing，您真的需要阅读Itzik Ben-Gan 撰写的 ITProToday 上的这篇文章。

Figure 3: Logical query processing order of query clauses

1 FROM 
2 WHERE 
3 GROUP BY 
4 HAVING 
5 SELECT
    5.1 SELECT list
    5.2 DISTINCT
6 ORDER BY 
7 TOP / OFFSET-FETCH