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.

12 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    5 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • 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