Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

3 Answers 3

up vote 18 down vote accepted

Summary

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:

SELECT X + 5 Y FROM MyTable WHERE Y = X

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:

SELECT
   T3.ID + (SELECT Min(Interval) FROM Intervals WHERE IntName = 'T') CalcID
FROM
   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
SELECT
   newid() FinalTransactionGUID,
   'GUID is: ' + Convert(varchar(50), FinalTransactionGUID) TextGUID,
   T.*
FROM
   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):

SELECT
   X.CalcID
FROM
   Table1 T
   INNER JOIN Table3 T3
      ON T.ID = T3.ID
   CROSS APPLY (
      SELECT
         T3.ID + (SELECT Min(Interval) FROM Intervals WHERE IntName = 'T') CalcID
   ) 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
3  
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

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

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?


Here is Joe Celko's explanation of how a query is processed according to the standard (I stole this from my own aspfaq.com article, which stole the quote probably from a newsgroup post by Celko):

Here is how a SELECT works in SQL ... at least in theory. Real products will optimize things when they can.

Start in the FROM clause and build a working table from all of the joins, unions, intersections, and whatever other table constructors are there. The AS option allows you give a name to this working table which you then have to use for the rest of the containing query.

Go to the WHERE clause and remove rows that do not pass criteria; that is, that do not test to TRUE (reject UNKNOWN and FALSE). The WHERE clause is applied to the working in the FROM clause.

Go to the optional GROUP BY clause, make groups and reduce each group to a single row, replacing the original working table with the new grouped table. The rows of a grouped table must be group characteristics: (1) a grouping column (2) a statistic about the group (i.e. aggregate functions) (3) a function or (4) an expression made up of the those three items.

Go to the optional HAVING clause and apply it against the grouped working table; if there was no GROUP BY clause, treat the entire table as one group.

Go to the SELECT clause and construct the expressions in the list. This means that the scalar subqueries, function calls and expressions in the SELECT are done after all the other clauses are done. The AS operator can give a name to expressions in the SELECT list, too. These new names come into existence all at once, but after the WHERE clause has been executed; you cannot use them in the SELECT list or the WHERE cluase for that reason.

Nested query expressions follow the usual scoping rules you would expect from a block structured language like C, Pascal, Algol, etc. Namely, the innermost queries can reference columns and tables in the queries in which they are contained.

This means that a SELECT cannot have more columns than a GROUP BY; but it certainly can have fewer columns.

Now, Celko was one of the main contributors to the earlier versions of the standards. I don't know if you're ever going to get a definitive answer to the WHY? question, except for speculation. My guess is that listing the actual operation first makes it very easy for the parser to know exactly what the type of operation is going to be. Imagine a 20-table join that could end up being a SELECT or UPDATE or DELETE, and remember that the code for these engines was originally written back in the days when string parsing was quite costly.

Note that if the SQL standard dictated FROM to come first, vendors may have independently decided to parse the grammar in a different order, so it still may not make sense to expect the order of clauses as written to completely obey the order of processing 100% of the time.

The same is true for things like CASE. We've seen scenarios right here on this site, for example, where the previously believed myth that CASE always processes in order and short circuits, are false. And this extends to other common beliefs as well, such as SQL Server evaluating joins in the order they were written, short circuiting WHERE clauses from left to right, or processing CTEs once or in a certain order even if they are referenced multiple times. Products are free to optimize how they see fit even if it doesn't reflect exactly how you've stated the query should work declaratively.

share|improve this answer
2  
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.