Use column aliases directly in SELECT clause
It would be nice if queries like this were possible:
A AS B
,C AS D
,D-B AS E
In other words, use column directly in the SELECT clause where they are defined. Other database vendors have implemented this, so I assume it's not rocket science.
Right now, we have to use subqueries just to define an extra calculation.
Tony Marston commented
Both Oracle and MySQL allow the result of an expression in the SELECT list to be used in the same SELECT list without having to repeat the expression. This is extremely beneficial when the expression is long, such as a subselect. The method used is to put the reference to the result of an expression in its own (SELECT ...) statement, such as in the following example:
SELECT foo, bar, (SELECT .....) AS alias
, (SELECT ... WHERE id=(SELECT alias))
@Maurice Pelchat. That looks cool. I'll give it a try the next time I'm in this neck of the SQL woods.
Maurice Pelchat commented
I do use CROSS APPLY, and I do it extensively.
For this example this is as simple as this.
Computed columns through CROSS APPLY can be reused in subsequent CROSS APPLY to produce other computed columns and so on. Lot of fun!
Select A, B, E, F
CROSS APPLY (Select E=C-A) as E
CROSS APPLY (Select F=E*2+C) as F
Mark Yudkin commented
Not only is this in blunt contradiction to the SQL standard, but if implemented as suggested it would break existing functioning code. It hasn't been implemented by anybody else - for very good reasons.
Koen Verbeeck commented
Not implementing something because "major" database vendors haven't, is not a good excuse. It leads to much more compact code.
I agree with Erland and I don't see any other *major* database vendors (Oracle, Postgres, MySQL...) that have implemented this.
Rozenbaum Alexander commented
@Petr Pařík sorry. I have ment the WHERE clause.
Petr Pařík commented
@Rozenbaum Alexander: using column aliases in the ORDER BY clause is already supported.
Rozenbaum Alexander commented
Using column aliases in the ORDER clause also needed a lot.
I understand this may be a bit of a stretch, but being able to use those aliases in WHERE and GROUP BY clauses would be totally awesome!
Brian Boodman commented
Erland Sommarskog's concerns could be mitigated with an alternative syntax.
A AS B
,C AS D
,currentquery.D-currentquery.B AS E
Where currentquery is a new contextual keyword. That said, I'm not sure this feature is compelling enough to justify a new contextual keyword.
Jeffrey Yao commented
I think this will cause more harms than benefits just as Erland Sommarskog already mentioned in his comment.
Andrew Stanton commented
This would also be nice to use in group by and order by.
My workaround to this is to embrace the query with another SQL * FROM (original query) to get the aliases. Maybe something in this spirit would work?
This would be really useful, and I can't see how it causes problems. Column values are known when the SELECT list is being returned, so as long as the calculated column values were based on previous columns in the same row, and *assuming the expression syntax is limited* the overhead is minimal. We should address Erland's comments on Dec 16th 2017: basically we must know whether a value used in a SELECT expression refers to an earlier column or to a column from a table in the FROM clause. There are several options here: (I) default to using columns from tables in the FROM clause which would cause existing code to not be broken or (ii) introduce a pseudo function COLUMN_VALUE() which can only be used in the SELECT clause and must refer to a previously used column.
I should add that I was originally strongly against this idea. But having recently converted reports from Access and seen how this could work, I can see no downside and I can see the advantages.
Brian Tkatch commented
I strongly agree with Erland and Steven.
Steven Hibble commented
I agree with Erland. This will break some existing code and creates problems when columns and aliases conflict.
The CROSS APPLY method is best. When that doesn't work (e.g. window functions), a CTE or subquery suffices.
@Thomas, CROSS APPLY does not always help, for instance with windowed functions. Also it makes you constantly scroll up and down in code around the FROM statement. Vote up, Koen
Thomas Franz commented
just use a CROSS APPLY:
SELECT calc.B, calc.D, calc.D - calc.B AS E
CROSS APPLY (SELECT A AS B, C AS D) AS calc
This would also prevent you from writing the same code / formular multiple times, as you can join, filter or order e.g. by calc.D
Erland Sommarskog commented
There are products that have this behaviour, for instance Access. However, it is in direct conflict with how the SQL standard is defined, which is based on the principle as "all-at-once". Thus, all aliases in the SELECT list are defined at once. Note also that it would be a breaking change. Consider a table which has the columns a, b, c. Consider next the query:
SELECT a AS b, c + b FROM tbl