On Sat, 5 Sep 2015 09:07:11 -0700
Darko Volaric <lists at darko.org> wrote:
> I'm asking why the SQL standard restricts the use of aliases in this
> way and what the benefit of this restriction is.
Rationales in SQL are hard to come by. The language was promulgated by
a private firm, and the standard evolved under the aegis of what was,
for all intents and purposes, a private club. Rationales for most
warts in the language boil down to "because we say so".
Nonetheless, there is a good reason!
There are no aliases in SQL, Horatio.
In every SQL database, column names are unique and unambiguous. If you
know the name of a table and a column, you've identified it. A query
can name *new* columns, but it can't create aliases for existing
ones. Consider,
> SELECT a+b AS x FROM t1 WHERE x=99;
Here, "x" is a new column, the product of the SELECT. By the rules of
SQL, it's *not* a macro for a+b, and it's not an alias. It's the name
of the column formed by computing a+b, a new column of a new table.
Now consider,
SELECT x from (
SELECT a+b AS x FROM t1
) as TEETH_GNASHER
WHERE x=99;
The outer query does not refer to t1, and thus not to "a" nor "b". It
sees only the new table, with its sole column, "x". And, although it's
a bit verbose, it also satisfies the sacred DRY criterion.
Is that good? The "no aliases" rule has one thing going for it: it's
consistent. It's easy to understand and remember, and it reduces
opportunities for ambiguity. SQL is a logic-based language, and
ambiguity in logic is anathema because it's too easy to form
syntactically valid constructions that produce incorrect (and
unintended) results.
Nearly every SQL programmer uses some other programming language as the
"real" language for his application. There's a temptation to make
informal, sometimes unwitting assumptions about the rules of SQL drawn
from that other language. The best way to understand any language
though, including SQL, is on its own terms. So double-quotes denote
identifiers, single-quotes strings, "||" contatenation, and there are
no aliases. It's not easy to slough off unwarranted associations with
other languages, but once that's done, SQL is impossible to
misconstrue.
--jkl
