Home Dashboard Directory Help
Search

Column Alias Usage Everywhere in a Query by Kalman Toth


Status: 

Closed
 as Won't Fix Help for as Won't Fix


3
25
Sign in
to vote
Type: Suggestion
ID: 610120
Opened: 10/4/2010 6:42:47 AM
Access Restriction: Public
3
Workaround(s)
view

Description

Currently column alias can only be used in the ORDER BY clause.

It would increase readability and programmer's productivity if it can be used everywhere like in the WHERE, GROUP BY clauses.

In other words, once a column alias defined, there would be no need to use the source expression. For example: in the case of [Year]=year(OrderDate), [Year] could be used in WHERE, GROUP BY and even in the SELECT list for a second reference.
Details
Sign in to post a comment.
Posted by HiredMind on 9/24/2012 at 4:55 PM
I know others have given a multitude of reasons why this cannot be done, and I don't want to argue that point, except to say this: MySQL supports it.

And it, along with other more logical syntax differences, makes MySQL syntax a joy to use, relative to T-SQL.
Posted by Microsoft on 11/23/2010 at 7:15 PM
Hi,
Thanks for your feedback. Your suggestion will break backward compatibility and also has other implications. SQL Server conforms to ANSI SQL for the core SQL syntax/behavior. This is something every relational database engine in the market tries to do also. Extending the SQL syntax/binding behavior to allows column alias in places other than SELECT/ORDER BY clause will be a serious undertaking that will provide little value.
Additionally, extending the core SQL grammar in non-standard way means that we (as in Microsoft) have to re-define the binding behavior and rules for SELECT statement. We also have to provide a mode to retain the default behavior. The overall experience will be poor for such enhancement.
So I hope you can use existing features that allow you to avoid repeating the expressions like CTE, derived table, computed column, view, and apply syntax for this particular problem.

--
Umachandar, SQL Programmablity Team
Posted by Kalman Toth on 10/8/2010 at 12:40 AM
Neither the available workarounds (I did not ask for those, there are always workarounds), nor the retrofit issue explain the extreme negative reaction received for this suggestion.

There must be some REAL reason for the extreme negativism. What is it? It's like the HOUSE OF SQL will collapse if this is implemented. Every computer language have similar simple aliasing feature, most even have macro feature.
Posted by Kalman Toth on 10/8/2010 at 12:32 AM
What happens when using CROSS APPLY you want to add

HAVING TotalSales > 2000000

?
Thanks.
Posted by Brad_Schulz on 10/5/2010 at 1:47 PM
You can already bring about broad column alias usage via CROSS APPLY... Taking SQLUSA's query, you could write it like so, using the functions only once and aliasing them for the SELECT, WHERE, GROUP BY, and ORDER BY, and it doesn't cost a thing... in fact this will bring about the EXACT SAME query plan as the query that SQLUSA typed out:

SELECT    [Year], [Month], TotalSales=SUM(TotalDue)
FROM AdventureWorks2008.Purchasing.PurchaseOrderHeader
CROSS APPLY (SELECT [Year]=YEAR(OrderDate),
                                 [Month]=MONTH(OrderDate)) F
WHERE [Year] > 2002
GROUP BY [Year],[Month]
ORDER BY [Year],[Month]

As others have said, trying to add an enhancement to T-SQL to accomodate this request is (1) unnecessary because of the above, and (2) impossible because of backward compatibility.

--Brad
Posted by AaronBertrand on 10/5/2010 at 1:21 PM
Nope, will cause more problems than it solves, sorry.
Posted by Hugo Kornelis on 10/5/2010 at 12:32 AM
Such a feature would completely break backwards compatibility. Since it is currently allowed to give a column an alias that is the same as the name of another column, references in the WHERE clause would suddenly change meaning.
Simplified (though absurd) example: SELECT Column1 AS Column2, Column2 AS Column1 FROM MyTable WHERE Column1 > Column2

More realistic example:
SELECT o.OrderID, o.OrderTotal, COALESCE(SUM(d.Total),0) AS OrderTotal
FROM Orders AS o LEFT OUTER JOIN OrderDetails AS d ON d.OrderID = o.OrderID
GROUP BY o.OrderID, o.OrderTotal
HAVING o.OrderTotal <> COALESCE(SUM(d.Total),0);

With this suggestion implemented, the last line could be written as HAVING OrderTotal <> OrderTotal, and then we can only hope that the parser is able to read our minds.
Posted by Tony Rogerson SQL on 10/4/2010 at 11:54 PM
In order to understand why you are wrong in thinking the SELECT is the start of the query you need to understand relational theory and then ANSI SQL which is built around relational theory.

The FROM clause (the set from which the restriction (the WHERE) and projection (the SELECT) are taken) happens first, without the FROM which forms a single set from which we then restrict and project there cannot be a SELECT not WHERE!

Column aliases are formed after both the FROM and the WHERE, projection is the last relational operation to be performed, after that we then have the non-relational ORDER BY.

IF you are really that bothered, and frankly this has never occurred to me in the 20 years I've been writing SQL then use a derived table to provide the final "set" that you can then use column aliases from.

Eg.,

SELECT colalias1, colalias2
FROM (
SELECT fullname AS colalias1, sid_amount AS colalias2
FROM tbl
) AS d ( colalias1, colalias2 )
WHERE colalias1 = 'sid'
Posted by Kalman Toth on 10/4/2010 at 5:16 PM
Here is a T-SQL query which would benefit from broad column alias usage:

SELECT    [Year]=YEAR(OrderDate),
        [Month]=MONTH(OrderDate),
        TotalSales=SUM(TotalDue)
FROM AdventureWorks2008.Purchasing.PurchaseOrderHeader
WHERE YEAR(OrderDate) > 2002
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY [Year],[Month]
Posted by Kent Waldrop on 10/4/2010 at 11:51 AM
What follows is syntax that is valid; such syntax might need to be deprecated:

select
1 as A,
2 as A
/* -------- Output: --------
A         A
----------- -----------
1         2

(1 row(s) affected)
*/

Kent Waldrop
Sign in to post a workaround.
Posted by Brad_Schulz on 10/5/2010 at 1:49 PM
As Kent already indicated, SQLUSA's sample query could be re-written with CROSS APPLY, accomplishing exactly what he's looking for... column aliasing in ALL clauses (SELECT, WHERE, GROUP BY, ORDER BY)... with no added cost whatsoever... in fact, it produces the EXACT SAME query plan as SQLUSA's original query:

SELECT    [Year], [Month], TotalSales=SUM(TotalDue)
FROM AdventureWorks2008.Purchasing.PurchaseOrderHeader
CROSS APPLY (SELECT [Year]=YEAR(OrderDate),
                                 [Month]=MONTH(OrderDate)) F
WHERE [Year] > 2002
GROUP BY [Year],[Month]
ORDER BY [Year],[Month]

--Brad
Posted by Kent Waldrop on 10/4/2010 at 11:58 AM
Another alternative based on Rob's workaround is to use the CROSS APPLY operator:

select AccountID, OrderDate, [Year]
from Orders
cross apply (select year(OrderDate) as [Year]) anAlias
WHERE [Year]>=2006
Posted by Rob Volk1 on 10/4/2010 at 8:15 AM
This can be easily accomplished using a common table expression:

;WITH CTE(Account, OrderDate, [Year]) AS (
SELECT AccountID, OrderDate, Year(OrderDate)
FROM Orders)
SELECT Account, [Year], Min(OrderDate) FirstOrder, Max(OrderDate) LastOrder, Count(*) TotalOrders
FROM CTE
WHERE [Year]>=2006
GROUP BY Account, [Year]