The Order of SELECT Statement Clause Processing

As stated previously, the optimiser investigates the FROM and WhERE clauses of a SELECT statement to analyse the requirements for optimisation. However, the SELECT statement consists of more than these two clauses. Also, there are more than considerations for optimisation required:

• The actual existence of all tables, views, synonyms must be identified within the database - or databases - referred to.
• The tables referred to by synonyms must be identified.
• The query upon which each view is based must be extracted and merged with the present SELECT statement.
• All columns mentioned within the resulting SELECT statement must be verified as existing, attached to the tables mentioned and be non-ambiguous.

The order in which the SELECT statement's clauses are processed is as follows:

Clause	Order
SELECT	5
FROM	1
WHERE	2
GROUP BY	3
HAVING	4
ORDER BY	6
INTO TEMP	7

Why in this order?

Table aliases are identified immediately and must be used in preference to the table names throughout the rest of the SELECT statement.

Column, expression and function aliases stated in the SELECT clause can only be referred to in the ORDER BY and INTO TEMP clauses.

Expressions and functions cannot be used directly in the ORDER BY clause. They have been evaluated prior to and are meaningless within the clause. Only their a liases and position numbers within the SELECT clause may be used.

It is more efficient to filter out rows that are not required by placing conditions in the WHERE clause, rather than allow them to be present during the GROUP BY clause, only to be filtered out by the HAVING clause. The less rows that the GROUP BY clause has to process, the faster the grouping will be.