SELECT is not the last thing that is processed;
ORDER BY is. This is why the only place where you can refer to aliases you created within
SELECT is in the
ORDER BY. Here is Joe Celko's explanation of how a query is processed according to the standard (I stole this from my own aspfaq.com article, which stole the quote probably from a newsgroup post by Celko):
Here is how a SELECT works in SQL ... at least in theory. Real
products will optimize things when they can.
Start in the FROM
clause and build a working table from all of the joins, unions,
intersections, and whatever other table constructors are there. The
AS option allows you give a name
to this working table which you then have to use for the rest of the
Go to the WHERE clause and remove rows that do
not pass criteria; that is, that do not test to TRUE (reject UNKNOWN
and FALSE). The WHERE clause is applied to the working in the FROM
Go to the optional GROUP BY clause, make groups and
reduce each group to a single row, replacing the original working
table with the new grouped table. The rows of a grouped table must be
group characteristics: (1) a grouping column (2) a statistic about the
group (i.e. aggregate functions) (3) a function or (4) an expression
made up of the those three items.
Go to the optional HAVING
clause and apply it against the grouped working table; if there was no
GROUP BY clause, treat the entire table as one group.
Go to the
SELECT clause and construct the expressions in the list. This means
that the scalar subqueries, function calls and expressions in the
SELECT are done after all the other clauses are done. The AS operator
can give a name to expressions in the SELECT list, too. These new
names come into existence all at once, but after the WHERE clause has
been executed; you cannot use them in the SELECT list or the WHERE
cluase for that reason.
Nested query expressions follow the
usual scoping rules you would expect from a block structured language
like C, Pascal, Algol, etc. Namely, the innermost queries can
reference columns and tables in the queries in which they are
This means that a SELECT cannot have more columns than
a GROUP BY; but it certainly can have fewer columns.
Now, Celko was one of the main contributors to the earlier versions of the standards. I don't know if you're ever going to get a definitive answer to the
WHY? question, except for speculation. My guess is that listing the actual operation first makes it very easy for the parser to know exactly what the type of operation is going to be. Imagine a 20-table join that could end up being a
DELETE, and remember that the code for these engines was originally written back in the days when string parsing was quite costly.
Note that if the SQL standard dictated
FROM to come first, vendors may have independently decided to parse the grammar in a different order, so it still may not make sense to expect the order of clauses as written to completely obey the order of processing 100% of the time.
The same is true for things like
CASE. We've seen scenarios right here on this site, for example, where the previously believed myth that
CASE always processes in order and short circuits, are false. And this extends to other common beliefs as well, such as SQL Server evaluating joins in the order they were written, short circuiting
WHERE clauses from left to right, or processing CTEs once or in a certain order even if they are referenced multiple times. Products are free to optimize how they see fit even if it doesn't reflect exactly how you've stated the query should work declaratively.