How SELECT statement executes in ORACLE SQL?

A common source of confusion is the simple fact that SQL syntax elements are not ordered in the way they are executed. The lexical ordering is:

• SELECT [ DISTINCT ]
• FROM
• WHERE
• GROUP BY
• HAVING
• UNION
• ORDER BY

For simplicity, not all SQL clauses are listed. This lexical ordering differs fundamentally from the logical order, i.e. from the order of execution:

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

There are three things to note:

1. FROM is the first clause, not SELECT. The first thing that happens is loading data from the disk into memory, in order to operate on such data.
2. SELECT is executed after most other clauses. Most importantly, after FROM and GROUP BY. This is important to understand when you think you can reference stuff that you declare in the SELECT clause from the WHERE clause. The following is not possible:

   SELECT A.x + A.y AS z
   FROM A
   WHERE z = 10 -- z is not available here!

   If you wanted to reuse z, you have two options. Either repeat the expression:

   SELECT A.x + A.y AS z
   FROM A
   WHERE (A.x + A.y) = 10

   ... or you resort to derived tables, common table expressions, or views to avoid code repetition. See examples further down.
3. UNION is placed before ORDER BY in both lexical and logical ordering. Many people think that each UNION subselect can be ordered, but according to the SQL standard and most SQL dialects, that is not true. While some dialects allow for ordering sub queries or derived tables, there is no guarantee that such ordering will be retained after a UNION operation