On 5 Sep 2015, at 5:07pm, Darko Volaric <lists at darko.org> wrote: > That's not what I said, why don't you read/quote the whole sentence: > > "Besides being part of the standard (I assume), what's the rationale for > this restriction?" > > I'm asking why the SQL standard restricts the use of aliases in this way > and what the benefit of this restriction is.
I'm sorry. I did not understand your grammar. Please put it down to me being more used to British English than American English. I would have expected "Apart from being part of the standard ...". The reason is that the SQL engine has to select the correct rows before it has to work out the value of each column in the row. Consider these statements: SELECT s FROM myTable WHERE l/z < 456; SELECT * FROM myTable WHERE l/z < 456; SELECT (s-70)*l/z, myfunction(l/z) FROM myTable WHERE l/z < 456; There is no point in SQL evaluating c-70*l/z or myfunction(l/z) for the whole table if only two or three are going to satisfy b < 456. So it does the WHERE clause first. Only once it has picked the right rows does it need to pay attention to the bit between SELECT and FROM. Maybe no rows will satisfy the WHERE clause and it won't have to bother at all. For a column alias to be useful in both parts of the clause the syntax might be more like SELECT c-70*fract, myfunction(fract) FROM myTable WHERE (l/z AS fract) < 456; which is, of course, not valid SQL. You might also be interested to see whether this works: SELECT l/z AS fract, c-70*fract, myfunction(fract) FROM myTable WHERE l/z < 456; Simon.
