哪个执行第一个WHERE子句或JOIN子句 [英] Which performs first WHERE clause or JOIN clause
问题描述
哪个子句在SELECT语句中首先执行?
Which clause performs first in a SELECT statement?
在此基础上，我对select查询有疑问.
I have a doubt in select query on this basis.
考虑以下示例
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
-
是否首先检查
WHERE子句，然后执行INNER JOIN
首先JOIN，然后检查条件?
如果先执行JOIN然后执行WHERE条件；在不同JOIN的条件下如何执行更多操作?
If it first performs JOIN and then WHERE condition; how can it perform more where conditions for different JOINs?
推荐答案
查询处理的概念顺序为:
The conceptual order of query processing is:
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
但这只是一个概念上的顺序.实际上，引擎可能决定重新安排条款.这是证明.让我们制作2个表，每个表具有1000000行:
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:
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
现在运行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
第一个通知将在join条件下过滤掉大多数行，第二个在where条件下过滤掉.查看产生的计划:
Notice first will filter most rows out in join condition, second in where condition. Look at produced plans:
1 TableScan-谓词:[Test].[dbo].[test2].[id]为[t2].[id] =(100)
1 TableScan - Predicate:[Test].[dbo].[test2].[id] as [t2].[id]=(100)
2 TableScan-谓词:[Test].[dbo].[test2].[id] as [t2].[id] =(1)
2 TableScan - Predicate:[Test].[dbo].[test2].[id] as [t2].[id]=(1)
这意味着在优化后的第一个查询中，首先决定对join条件进行评估以过滤出行，其次在对它的评估中首先对where子句进行了评估.
Which means that in first query optimized decided first to evaluate join condition to filter out rows, in second it evaluated where clause first.
这篇关于哪个执行第一个WHERE子句或JOIN子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
