Oracle SQL clause evaluation order

Question

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?

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.

Solution 1

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:

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.

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.

Solution 2

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

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

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.

Solution 3

Oracle Query Processing Order

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

Solution 4

Below is SQL Query Processing Order:

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

Solution 5

Logical Processing Order of the SELECT statement

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

This is the logical order 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

View more solutions

Comments

[E_the_Shy]

The two are two steps. 1) Parsing and coming up with a plan 2) Execution. Which step are you talking about? If you are talking about parsing, then it seems to make multiple passes. If you are talk about execution, then execution plans are many and various. It could vary depending on your indexes, etc.

[philipxy]

Does this answer your question? What is the execution sequence of Group By, Having and Where clause in SQL Server?

[jon.johnson]

I'm gonna say I agree with you. I'd be curious to see if anyone else has information on the contrary.

[akf]

group by is also dependent on the select items. also, in practice, it seems that order by is at the end, as evidenced by a select statement with a rownum < n facet in the where clause. in this case, the top n arbitrarily ordered items will be taken from the top of the result set, and then the ordering will be effected.

[Mark Brackett]

You can't GROUP without doing WHERE first. GROUP needs an aggregate function in SELECT, and you can't feed rows that don't satisfy WHERE into that aggregate.

[Gary Myers]

GROUP doesn't NEED an aggregate in the select. You can do SELECT cust_id FROM orders GROUP BY cust_id HAVING COUNT(*) > 10; You can even do it without the HAVING (making it, in effect, a DISTINCT)

[Gary Myers]

I'd say the FROM/WHERE is very grey. Assuming the FROM includes join conditions (ANSI style), the optimizer isn't consstrained as to whether it applies WHERE clause predicates before or after FROM clause predicates.

[symcbean]

Hints (which appear in the SELECT clause) would have to be evaluated early in the parsing operation, but I wouldn't expect anything else from the SELECT to be used in generating the plan. After that, the FROM and WHERE would both have to be evaluated in the same part of the plan generation to establish the most efficient plan.

[Mark Brackett]

@Gary - Fair enough, but my point stands: you have to do WHERE before GROUP BY. Not including the aggregate just lets you skip the calculated column, it still doesn't allow you to include rows not matching the predicate (WHERE clause) in the intermediate result that gets GROUPed. (Obviously, an optimizer is free to make whatever substitutions and rewrites it wants while guaranteeing an equivalent result. Talking about order of execution is almost always "logical order", not necessarily "actual order". Compilers and optimizers have a lot of free reign to rearrange things that we won't notice.)

[ypercubeᵀᴹ]

SELECT is logically processed after HAVING, not before.

[Martijn Pieters]

Do you have a source for this? It disagrees with the top-voted answer.

[ypercubeᵀᴹ]

@MartijnPieters I disagree with the top-voted answer. This answer has the correct (logical) order of evaluation. I can't find any Oracle docs, except this (not official Oracle) one: oracle.readthedocs.io/en/latest/sql/basics/…

[ypercubeᵀᴹ]

In general, the SELECT and HAVING could be evaluated in different order, if there are no window functions involved, the result would be the same. But if there are window functions in the select list, then the result could change depending on order of evaluation.

[ypercubeᵀᴹ]

A simple test reveals that SELECT is logically evaluated after HAVING: rextester.com If SELECT was evaluated first, then the RN column would have values 2 and 3. But it has 1 and 2.

[Pankil Shah]

agree with @ypercubeᵀᴹ.

[ypercubeᵀᴹ]

It would be better if you posted a link to Oracle documentation and not SQL Server's.

[Pankil Shah]

docs.oracle.com/database/121/ADFNS/adfns_sqlproc.htm#ADFNS99‌​995

[Pankil Shah]

Further reading - Oracle: About SQL Processing