Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Use column aliases directly in SELECT clause

It would be nice if queries like this were possible:

SELECT
A AS B
,C AS D
,D-B AS E
FROM table

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.

47 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Koen Verbeeck shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    10 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • Jeffrey Yao commented  ·   ·  Flag as inappropriate

        I think this will cause more harms than benefits just as Erland Sommarskog already mentioned in his comment.

      • Anon commented  ·   ·  Flag as inappropriate

        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?

      • Will commented  ·   ·  Flag as inappropriate

        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.

      • Steven Hibble commented  ·   ·  Flag as inappropriate

        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.

      • Constantine commented  ·   ·  Flag as inappropriate

        @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  ·   ·  Flag as inappropriate

        just use a CROSS APPLY:

        SELECT calc.B, calc.D, calc.D - calc.B AS E
        FROM tbl
        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  ·   ·  Flag as inappropriate

        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

      • JediSQL commented  ·   ·  Flag as inappropriate

        This would save me a lot of CTEs (common tables expressions) when I am doing string processing and having to repeatedly re-use CHARINDEX, etc., results.

      Feedback and Knowledge Base