blocks|key|319116|text|这通常取决于你的数据库管理系统，但是...我认为第二个答案更合理。如果没有子查询结果，主查询通常是无法计算的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|319117|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|3274740|text|SQL引擎试图优化(子)查询的执行顺序。决定这一点的部分称为查询优化器。查询优化器知道每个表中有多少行，哪些表有索引，以及哪些字段。它使用该信息来决定首先执行哪个部分。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3274741|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|317226|text|我认为答案4是正确的。有几个考虑因素：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|317227|子查询的类型-它是相关的还是无关的。考虑一下：|317228|SELECT+*
FROM+++t1
WHERE++id+IN+(
+++++++++++++SELECT+id
+++++++++++++FROM+++t2
++++++++++++)|code-block|syntax|javascript|317229|在这里，子查询与外部查询不相关。如果与t1.id相比，t2.id中的值的数量较小，则可能最有效的方法是首先执行子查询，并将结果保存在内存中，然后扫描t1或t1.id上的索引，与缓存值进行匹配。|317230|但是如果查询是：|317231|SELECT+*
FROM+++t1
WHERE++id+IN+(
+++++++++++++SELECT+id
+++++++++++++FROM+++t2
+++++++++++++WHERE++t2.type+=+t1.type
++++++++++++)|317232|这里的子查询是相关的-除非t1.type已知，否则无法计算子查询。由于t1.type的值对于外部查询的每一行可能不同，因此该子查询可以对外部查询的每一行执行一次。|317233|然而，RDBMS可能真的很聪明，并且意识到t2.type只有几个可能的值。在这种情况下，如果它可以猜测执行一次子查询的成本将比为每行执行一次子查询的成本更低，那么它仍然可以使用用于不相关的子查询的方法。|317234|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|Z|8|@]|9|@]|A|$G|H]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

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

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

<pre><code>SELECT *
FROM t1
WHERE id IN (
 SELECT id
 FROM t2
 )
</code></pre>

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. 

But if the query is:

<pre><code>SELECT *
FROM t1
WHERE id IN (
 SELECT id
 FROM t2
 WHERE t2.type = t1.type
 )
</code></pre>

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.

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.

blocks|key|3061593|text|选项4是close。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3061594|SQL是declarative：你告诉查询优化器你想要什么，它就会找出最好的方式(取决于时间/“成本”等)。对于外观相同的查询和表，这可能会有所不同，这取决于统计数据、数据分布、行数、并行度和天知道还有什么。|offset|length|3061595|这意味着没有固定的顺序。但这并不完全是“在飞行中”|3061596|即使具有相同的服务器、模式、查询和数据，我也看到执行计划不同|3061597|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/Declarative_programming^0|0|4|B|0|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@$D|T|E|U|1|V]]|A|$]]|$1|F|3|G|5|6|7|W|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|X|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|Q]]]]

Option 4 is close.

SQL is <a href="http://en.wikipedia.org/wiki/Declarative_programming" rel="noreferrer">declarative</a>: 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.

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

blocks|key|3061626|text|如果您想阅读有关这些主题的内容，请参阅SQL+Server2008内部:+T-SQL查询。它有两个专门的章节，介绍在SQL+Server中如何在逻辑和物理上处理查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3061627|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

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

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

the answers was

<ol>
<li>primary query -> sub query -> sub sub query and so on</li>
<li>sub sub query -> sub query -> prime query</li>
<li>the whole query is interpreted at one time</li>
<li>There is no fixed sequence of interpretation, the query parser takes a decision on fly</li>
</ol>

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.

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

数据库管理系统

数据库管理

SQL Server

数据库

服务器

你好，我做了一个SQL测试，对一个问题感到怀疑/好奇：引擎执行查询和子查询的顺序是什么？答案是主查询->子查询->子查询如子查询->子查询->主查询整个查询一次解释没有固定的解释顺序，查询解析器在飞行时做出决定<>G211</代码>我选择了最后一个答案(只是假设它是最可靠的w.r.t.其他)。现在让我好奇的是：我可以在...

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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