1

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?

Borys
  • 2,676
  • 2
  • 24
  • 37

1 Answers1

3

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 descriptive 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 does 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

Which 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 after 100. The enumeration does not happen first, with filtered rows getting sequence numbers that are filtered out.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    I agree with Gordon. Also evaluating aliases is made at parsing time, not running time. And one concept hard to get into developers used to imperative/procedural languages is the relational nature of a query. Order is not important and the engine ill ultimately give the final word about how it ill be processed, for example in case you are joining two tables and filtering both what ill happen first filterA, filterB or matchAB? Answer: engine ill decide. – jean Feb 20 '14 at 12:10