Oracle SQL clause evaluation order

sql oracle clause operator-precedence
jon.johnson picture jon.johnson · May 16, 2010 · Viewed 14.3k times · Source

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.

Answer

Mark Byers picture Mark Byers · May 16, 2010

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.

Related questions

Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More
Oracle SQL: Update a table with data from another table

Table 1: id name desc ----------------------- 1 a abc 2 b def 3 c adf Table 2: id name desc ----------------------- 1 x 123 2 y 345 In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2's name and desc using …

Read More