Take the 2-minute tour ×

While trying to write a query, I found out (the hard way) that SQL Server parses WHEREs in a query long before parsing the SELECTs when executing a query.

The MSDN docs say that the general logical parsing order is such that SELECT is parsed nearly last (thus resulting in "no such object [alias]" errors when trying to use a column alias in other clauses). There was even a suggestion to allow for aliases to be used anywhere, which was shot down by the Microsoft team, citing ANSI standards compliance issues (which suggests that this behavior is part of the ANSI standard).

As a programmer (not a DBA), I found this behavior somewhat confusing, since it seems to me that it largely defeats the purpose of having column aliases (or, at the very least, column aliases could be made significantly more powerful if they were parsed earlier in the query execution), since the only place you can actually use the aliases is in ORDER BY. As a programmer, it seems like it's missing a huge opportunity for making queries more powerful, convenient, and DRY.

It looks like it's such a glaring issue that it stands to reason, then, that there are other reasons for deciding that column aliases shouldn't be allowed in anything other than SELECT and ORDER BY, but what are those reasons?

share|improve this question
add comment

3 Answers 3

up vote 18 down vote accepted


There's no logical reason it couldn't be done, but the benefit is small and there are some pitfalls that may not be immediately apparent.

Research Results

I did some research and found some good information. The following is a direct quote from a reliable primary source (that wishes to remain anonymous) at 2012-08-09 17:49 GMT:

When SQL was first invented, it had no aliases in the SELECT clause. This was a serious shortcoming that was corrected when the language was standardized by ANSI in about 1986.

The language was intended to be "non-procedural"--in other words, to describe the data that you want without specifying how to find it. So, as far as I know, there's no reason why an SQL implementation couldn't parse the whole query before processing it, and allow aliases to be defined anywhere and used everywhere. For example, I don't see any reason why the following query shouldn't be valid:

select name, salary + bonus as pay
from employee
where pay > 100000

Although I think this is a reasonable query, some SQL-based systems may introduce restrictions on the use of aliases for some implementation-related reason. I'm not surprised to hear that SQL Server does this.

I am interested in further research into the SQL-86 standard and why modern DBMSes don't support alias reuse, but haven't had the time to get very far with it yet. For starters, I don't know where to get the documentation or how to find out who exactly made up the committee. Can anyone help out? I also would like to know more about the original Sybase product that SQL Server came from.

From this research and some further thought, I have come to suspect that using aliases in other clauses, while quite possible, simply has never been that high a priority for DBMS manufacturers compared to other language features. Since it is not that much of an obstacle, being easily worked around by the query writer, putting effort into it over other advancements is not optimal. Additionally, it would be proprietary as it is obviously not part of the SQL standard (though I'm waiting to find out more on that for sure) and thus would be a minor improvement, breaking SQL compatibility between DBMSes. By comparison, CROSS APPLY (which is really nothing more than a derived table allowing outer references) is a huge change, that while proprietary offers incredible expressive power not easily performed in other ways.

Problems With Using Aliases Everywhere

If you allow SELECT items to be put in the WHERE clause, you can not only explode the complexity of the query (and thus the complexity of finding a good execution plan) it is possible to come up with completely illogical stuff. Try:


What if MyTable already has a column Y, which one is the WHERE clause referring to? The solution is to use a CTE or a derived table, which in most cases should cost no extra but achieves the same final end result. CTEs and derived tables at least enforce the resolution of ambiguity by allowing an alias to be used only once.

Also, not using aliases in the FROM clause makes eminent sense. You can't do this:

   T3.ID + (SELECT Min(Interval) FROM Intervals WHERE IntName = 'T') CalcID
   Table1 T
   INNER JOIN Table2 T2
      ON T2.ID = CalcID
   INNER JOIN Table3 T3
      ON T2.ID = T3.ID

That's a circular reference (in the sense that T2 is secretly referring to a value from T3, before that table has been presented in the JOIN list), and darn hard to see. How about this one:

INSERT dbo.FinalTransaction
   newid() FinalTransactionGUID,
   'GUID is: ' + Convert(varchar(50), FinalTransactionGUID) TextGUID,
   dbo.MyTable T

How much do you want to bet that the newid() function is going to be put into the execution plan twice, completely unexpectedly making the two columns show different values? What about when the above query is used N levels deep in CTEs or derived tables. I guarantee that the problem is worse than you can imagine. There are already serious inconsistency problems about when things are evaluated only once or at what point in a query plan, and Microsoft has said it will not fix some of them because they are expressing query algebra properly--if one gets unexpected results, break the query up into parts. Allowing chained references, detecting circular references through potentially very long such chains–these are quite tricky problems. Introduce parallelism and you've got a nightmare in the making.

Note: Using the alias in WHERE or GROUP BY isn't going to make a difference to the problems with functions like newid() or rand().

A SQL Server way to create reusable expressions

CROSS APPLY/OUTER APPLY is one way in SQL Server to create expressions that can be used anywhere else in the query (just not earlier in the FROM clause):

   Table1 T
   INNER JOIN Table3 T3
      ON T.ID = T3.ID
   ) X
   INNER JOIN Table2 T2
      ON T2.ID = X.CalcID

This does two things:

  1. Makes all expressions in the CROSS APPLY get a "namespace" (a table alias, here, X) and be unique within that namespace.
  2. Makes it obvious everywhere not only that CalcID is coming from X, but also makes it obvious why you can't use anything from X when joining table T1 and T3, because X hasn't been introduced yet.

I'm actually quite fond of CROSS APPLY. It has become my faithful friend, and I use it all the time. Need a partial UNPIVOT (which would require a PIVOT/UNPIVOT or UNPIVOT/PIVOT using native syntax)? Done with CROSS APPLY. Need a calculated value that will be reused many times? Done. Need to rigidly enforce execution order for calls over a linked server? Done-with a screaming improvement in speed. Need only one type of row split to 2 rows or with extra conditions? Done.

So at the very least, in DBMS SQL Server 2005 and up, you have no further cause for complaint: CROSS APPLY is how you DRY in the way you are wanting.

share|improve this answer
I tried to put this answer together earlier but couldn't "frame" it. You nailed it, good work! –  Mark Storey-Smith Aug 3 '12 at 23:34
Thanks @MarkStorey-Smith! –  ErikE Aug 4 '12 at 0:40
This excellent answer provided a lot of the motivation for trying to get the question re-opened. It would be great if you are willing to review the question as it is now and your answer to make sure it all still fits together! –  Jack Douglas Aug 8 '12 at 19:21
@JackDouglas I just started a historical inquiry to help substantiate anything I might say. I will report back here as soon as possible! I don't know what kind of time frame it might be, but suffice it to say it should be interesting, if things turn out as I hope! –  ErikE Aug 8 '12 at 19:38
@JackDouglas Updated. –  ErikE Aug 14 '12 at 18:30
add comment

I can't tell you the exact reasons, but I'll tell you that there are workarounds to repeating expressions, for example using CTEs, subqueries, derived tables etc. to avoid repetition.

If you show a query with a repeated expression, we can probably show you how to re-write it so that the expression is only listed once. However this just reduces the complexity in writing / reading the query, it is unlikely to change much about the efficiency. SQL Server is generally pretty good about recognizing that expressions are repeated, and it won't perform that work twice. There are exceptions that go the other way, but you should only be concerned about efficiency when you actually observe this happening. I suspect most repeated expressions you write are really collapsed into only one operation in the plan.

That all said, I'll also repeat part of my answer from this question:

Why is the SELECT clause listed first?













share|improve this answer
Also note that the ability to use or not use aliases in different parts of the query is enforced by the parser, not by the optimizer or the execution engine. How the engine actually executes the query does not necessarily reflect the restrictions that affect syntax. –  Aaron Bertrand Aug 3 '12 at 17:12
add comment

In Entity SQL, you CAN use aliases from expressions in other places in the query in some situations:

select k1, count(t.a), sum(t.a)
from T as t
group by t.b + t.c as k1

Note that here you MUST define the expression in the GROUP BY clause in order to use it in the SELECT clause.

It is obviously possible to allow some of this kind of alias-as-reusable-expression in SQL queries.

share|improve this answer
add comment

Your Answer


