I was asked couple days ago about logical processing order of the SELECT statement and more specifically about aliases and where clause and I'm not sure about one issue. If we have a query like:
SELECT name AS first_name FROM people WHERE first_name = 'Alan';
The reason why usage of aliases in WHERE clause will generate error is really logical processing order of the SELECT statement, or rather syntax parsing issue, or maybe is the rule from SQL standard?Answer1:
It is the rule from the SQL standard (which is rather complicated because it goes into lots of details that users of SQL probably don't think about).
There are two principles behind the rule. The first is that the standard does not impose an ordering of operations, except when logically necessary (a
having clause, for instance, has to logically processed after a
group by). This is the basis of the notion at SQL is a <em>descriptive</em> language, where the results are described. Any particular database engine can determine its own execution paths.
The second principle is to avoid ambiguity. This is where scoping rules come in, that define what a SQL compiler knows when.
Consider the following statement:
select a as b, b as a, a + 1 as d -----------------------^ from t
The question is: which
a+1 refer to, the column
a in the table or the column
b (which is aliased as
a) in the
select. According to the standard this is unambiguous. Column aliases are not known in the
select clause where they are defined.
This extends to the
where clause as well, which is evaluated in the same scope. Consider the same example:
select a as b, b as a, a + 1 as d from t where a > 100
a does the
where condition refer to? The standard is unambiguous. The
where clause does not understand column aliases in the
select. This is because the
select is (logically) evaluated after the
where. So, when you say:
select row_number() over (order by a) as seqnum from t where a > 100
The value returned starts with the first
a <em>after</em> 100. The enumeration does not happen first, with filtered rows getting sequence numbers that are filtered out.