Oracle SQL 子句求值顺序

Oracle SQL clause evaluation order

提问by jon.johnson

In Oracle, which clause types get evaluated first? If I had the following ( pretend .... represent valid expressions and relation names ), what would the order of evaluation be?

在 Oracle 中，首先评估哪些子句类型？如果我有以下内容（假装 .... 代表有效的表达式和关系名称），评估的顺序是什么？

SELECT   ...
FROM     .....
WHERE    ........
GROUP BY ...........
HAVING   .............
ORDER BY ................

I am under the impression that the SELECT clause is evaluated last, but other than that I'm clueless.

我的印象是 SELECT 子句是最后评估的，但除此之外我一无所知。

采纳答案by Mark Byers

The select list cannot always be evaluated last because the ORDER BY can use aliases that are defined in the select list so they must be executed afterwards. For example:

选择列表不能总是最后评估，因为 ORDER BY 可以使用选择列表中定义的别名，因此它们必须在之后执行。例如：

SELECT foo+bar foobar FROM table1 ORDER BY foobar

I'd say that in general the order of execution could be something like this:

我想说，一般来说，执行顺序可能是这样的：

• FROM
• WHERE
• GROUP BY
• SELECT
• HAVING
• ORDER BY

• 从
• 在哪里
• 通过...分组
• 选择
• 有
• 订购者

The GROUP BY and the WHERE clauses could be swapped without changing the result, as could the HAVING and ORDER BY.

GROUP BY 和 WHERE 子句可以交换而不改变结果，就像 HAVING 和 ORDER BY 一样。

In reality things are more complex because the database can reorder the execution according to different execution plans. As long as the result remains the same it doesn't matter in what order it is executed.

实际上事情更复杂，因为数据库可以根据不同的执行计划重新排序执行。只要结果保持不变，它的执行顺序无关紧要。

Note also that if an index is chosen for the ORDER BY clause the rows could already be in the correct order when they are read from disk. In this case the ORDER BY clause isn't really executed at all.

另请注意，如果为 ORDER BY 子句选择了索引，则当从磁盘读取行时，这些行可能已经处于正确的顺序。在这种情况下，ORDER BY 子句根本没有真正执行。

回答by Mark Brackett

That's what execution plans are for. But, generally, there's only 1 way to do it. I'll ignore optimizations for the moment:

这就是执行计划的用途。但是，通常只有一种方法可以做到。我暂时忽略优化：

• FROM to get the table involved
• Start scanning the table in FROM, keeping those that pass WHERE clause
• SELECT unaggregated columns
• Calculate aggregated columns with GROUP BY
• Keep those grouped results that pass HAVING clause
• order results with ORDER BY

• FROM 来让表参与进来
• 开始扫描 FROM 中的表，保留那些通过 WHERE 子句的
• SELECT 未聚合的列
• 使用 GROUP BY 计算聚合列
• 保留那些通过 HAVING 子句的分组结果
• 使用 ORDER BY 对结果进行排序

Optimizations could cause some "peeking" to make better decisions (eg., it'd be a good idea to check the WHERE clause before scanning the table - an index may be available).

优化可能会导致一些“窥视”以做出更好的决定（例如，在扫描表之前检查 WHERE 子句是个好主意 - 索引可能可用）。

I believe most RDBMS solve this with a pre-pass through an optimizer which will basically rewrite the query to take advantage of indexes, remove redundant expressions, etc. This optimized query is then used to actually build the execution plan. There's also parallelism that could change the specifics - but the basics are the same.

我相信大多数 RDBMS 通过预先通过优化器来解决这个问题，优化器基本上会重写查询以利用索引、删除冗余表达式等。然后使用这个优化的查询来实际构建执行计划。还有可能会改变细节的并行性 - 但基本原理是相同的。

回答by Hemanth

Oracle Query Processing Order

Oracle 查询处理顺序

• FROM clause
• WHERE clause
• GROUP BY clause
• HAVING clause
• SELECT clause
• ORDER BY clause

• FROM 子句
• WHERE 子句
• GROUP BY 子句
• HAVING 子句
• SELECT 子句
• ORDER BY 子句

回答by gupta79ratnesh

Below is SQL Query Processing Order:

以下是 SQL 查询处理顺序：

1. FROM
2. CONNECT BY
3. WHERE
4. GROUP BY
5. HAVING
6. SELECT
7. ORDER BY

1. FROM
2. CONNECT BY
3. WHERE
4. GROUP BY
5. HAVING
6. SELECT
7. ORDER BY

回答by Pankil Shah

Logical Processing Order of the SELECTstatement

SELECT语句的逻辑处理顺序

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY

1. 从
2. 在
3. 加入
4. 在哪里
5. 通过...分组
6. WITH CUBE 或 WITH ROLLUP
7. 有
8. 选择
9. 清楚的
10. 订购者

This is the logicalorder to be used in writing/(logically thinking out) the query. The database may optimize the query in different ways during actual execution for sake of efficiency, as long as the returned results are the same as if it followed this execution order.

这是用于编写/（逻辑思考）查询的逻辑顺序。数据库在实际执行过程中为了效率可能会以不同的方式优化查询，只要返回的结果是一样的，就好像按照这个执行顺序一样。

References

参考

Microsoft T-SQL
Oracle Blog

微软 T-SQL
甲骨文博客