SQL SQL引擎执行查询和子查询的顺序是什么？

In which sequence are queries and sub-queries executed by the SQL engine?

提问by Igor

Hello I made a SQL test and dubious/curious about one question:

您好，我做了一个 SQL 测试并且对一个问题感到怀疑/好奇：

In which sequence are queries and sub-queries executed by the SQL engine?

SQL引擎执行查询和子查询的顺序是什么？

the answers was

答案是

1. primary query -> sub query -> sub sub query and so on
2. sub sub query -> sub query -> prime query
3. the whole query is interpreted at one time
4. There is no fixed sequence of interpretation, the query parser takes a decision on fly

1. 主查询 -> 子查询 -> 子子查询等等
2. 子子查询 -> 子查询 -> 素数查询
3. 一次解释整个查询
4. 没有固定的解释顺序，查询解析器会即时做出决定

I choosed the last answer (just supposing that it is most reliable w.r.t. others). Now the curiosity:

我选择了最后一个答案（只是假设它是其他人最可靠的）。现在好奇心：

where can i read about this and briefly what is the mechanism under all of that?

我在哪里可以阅读有关此内容的信息，并简要说明所有这些内容下的机制是什么？

Thank you.

谢谢你。

采纳答案by gbn

Option 4 is close.

选项 4 很接近。

SQL is declarative: you tell the query optimiser what you want and it works out the best (subject to time/"cost" etc) way of doing it. This may vary for outwardly identical queries and tables depending on statistics, data distribution, row counts, parallelism and god knows what else.

SQL 是声明性的：你告诉查询优化器你想要什么，它会以最好的方式（取决于时间/“成本”等）做到这一点。对于外观相同的查询和表，这可能会有所不同，具体取决于统计数据、数据分布、行数、并行度和天知道还有什么。

This means there is no fixed order. But it's not quite "on the fly"

这意味着没有固定的顺序。但它并不完全“即时”

Even with identical servers, schema, queries, and data I've seen execution plans differ

即使使用相同的服务器、模式、查询和数据，我也看到执行计划有所不同

回答by Roland Bouman

I think answer 4 is correct. There are a few considerations:

我认为答案 4 是正确的。有几个考虑：

type of subquery - is it corrrelated, or not. Consider:

子查询的类型 - 是否相关。考虑：

SELECT *
FROM   t1
WHERE  id IN (
             SELECT id
             FROM   t2
            )

Here, the subquery is not correlated to the outer query. If the number of values in t2.id is small in comparison to t1.id, it is probably most efficient to first execute the subquery, and keep the result in memory, and then scan t1 or an index on t1.id, matching against the cached values.

在这里，子查询与外部查询不相关。如果与 t1.id 相比，t2.id 中的值数量很少，那么首先执行子查询，并将结果保存在内存中，然后扫描 t1 或 t1.id 上的索引，与缓存的值。

But if the query is:

但如果查询是：

SELECT *
FROM   t1
WHERE  id IN (
             SELECT id
             FROM   t2
             WHERE  t2.type = t1.type
            )

here the subquery is correlated - there is no way to compute the subquery unless t1.type is known. Since the value for t1.type may vary for each row of the outer query, this subquery could be executed once for each row of the outer query.

这里子查询是相关的 - 除非 t1.type 已知，否则无法计算子查询。由于 t1.type 的值可能因外部查询的每一行而异，因此该子查询可以为外部查询的每一行执行一次。

Then again, the RDBMS may be really smart and realize there are only a few possible values for t2.type. In that case, it may still use the approach used for the uncorrelated subquery if it can guess that the cost of executing the subquery once will be cheaper that doing it for each row.

再说一次，RDBMS 可能真的很聪明，并且意识到 t2.type 只有几个可能的值。在这种情况下，如果它可以猜测执行一次子查询的成本比为每一行执行一次的成本更低，它仍然可以使用用于不相关子查询的方法。

回答by Frank Kalis

If you want something to read up on these topics, get a copy of Inside SQL Server 2008: T-SQL Querying. It has two dedicated chapters on how queries are processed logically and physically in SQL Server.

如果您想阅读有关这些主题的内容，请获取 Inside SQL Server 2008：T-SQL 查询的副本。它有两个专门的章节介绍如何在 SQL Server 中逻辑和物理地处理查询。

回答by Stephan Eggermont

The SQL engine tries to optimise the order in which (sub)queries are executed. The part deciding about that is called a query optimizer. The query optimizer knows how many rows are in each table, which tables have indexes and on what fields. It uses that information to decide what part to execute first.

SQL 引擎尝试优化（子）查询的执行顺序。决定这一点的部分称为查询优化器。查询优化器知道每个表中有多少行，哪些表有索引以及哪些字段。它使用该信息来决定首先执行哪个部分。

回答by Adelf

It's usually depends from your DBMS, but ... I think second answer is more plausible. Prime query usually can't be calculated without sub query results.

这通常取决于您的 DBMS，但是......我认为第二个答案更合理。如果没有子查询结果，通常无法计算主查询。