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