Tip: APPLY and Reuse of Column Aliases

SQL is a language with many unique aspects. One of those is the fact that the logical order in which the various query clauses are evaluated is different than the keyed-in order. The keyed-in order of a query’s clauses is:

a.    SELECT
b.    FROM
c.    WHERE
d.    GROUP BY
e.    HAVING
f.     ORDER BY

But the logical query processing order is:

1.    FROM
2.    WHERE
3.    GROUP BY
4.    HAVING
5.    SELECT -- column aliases created here
6.    ORDER BY

The reasoning behind this design is very interesting but not really the focus of this entry. What I wanted to focus on this time is that due to this special design a column alias assigned in the SELECT phase is not visible to preceding logical query processing phases. For example, consider the following query:

SET NOCOUNT ON;
USE AdventureWorks; -- or AdventureWorks2008 or AdventureWorks2008R2

SELECT SalesOrderID, OrderDate,
  DATEPART(weekday, DATEADD(day, @@DATEFIRST - 7, OrderDate)) AS Week_Day
FROM Sales.SalesOrderHeader
WHERE Week_Day NOT IN (1, 7);

The purpose of the expression aliased as Week_Day is to return the week day number corresponding to OrderDate, using Sunday as the first day of the week (controlled by the constant being subtracted; 1 = Monday, 7 = Sunday). If the logic of the expression is unclear to you, don’t worry about it. The point is that you have an expression in the SELECT list that you assign with an alias, and you want to refer to that alias elsewhere in the query.

If you try running this code it fails with the following error:

Msg 207, Level 16, State 1, Line 7
Invalid column name 'Week_Day'.

The reason for the error is that the query attempts to use the alias before it was defined. If you look at the logical query processing order of the clauses you will find that the WHERE clause is evaluated logically prior to the SELECT. The only clause where you can refer to column aliases assigned in the SELECT is ORDER BY, since it’s the only clause that is evaluated after the SELECT clause. You can’t even refer to an alias within the same SELECT clause where it was defined.

There are several commonly used workarounds, but they’re all lengthy and awkward. One is not to reuse the alias but rather to repeat the entire expression, like so:

SELECT SalesOrderID, OrderDate,
  DATEPART(weekday, DATEADD(day, @@DATEFIRST - 7, OrderDate)) AS Week_Day
FROM Sales.SalesOrderHeader
WHERE DATEPART(weekday, DATEADD(day, @@DATEFIRST - 7, OrderDate)) NOT IN (1, 7);

Needless to say, whenever you need to apply revisions to the expression, you need to duplicate the effort and there are more chances for errors.

Another option is to use a table expression like a CTE:

WITH C AS
(
  SELECT SalesOrderID, OrderDate,
    DATEPART(weekday, DATEADD(day, @@DATEFIRST - 7, OrderDate)) AS Week_Day
  FROM Sales.SalesOrderHeader
)
SELECT *
FROM C
WHERE Week_Day NOT IN (1, 7);

Or a derived table:

SELECT *
FROM ( SELECT SalesOrderID, OrderDate,
         DATEPART(weekday, DATEADD(day, @@DATEFIRST - 7, OrderDate)) AS Week_Day
       FROM Sales.SalesOrderHeader ) AS D
WHERE Week_Day NOT IN (1, 7);

But this approach adds a layer of complexity to the code.

This leads me to the tip which is the focus of this entry. Since the introduction of the APPLY operator in SQL Server 2005 I found myself using it in numerous cases, often in ways that initially seemed surprising. The APPLY operator allows you to APPLY the table expression that is provided to it as the right input table to each row from the table provided to it as the left input table.

Besides the APPLY operator I’m going to rely on another capability in T-SQL allowing you to write a SELECT query without a FROM clause, like so:

SELECT DATEPART(weekday, DATEADD(day, @@DATEFIRST - 7, '20110212')) AS Week_Day

This query returns a table result with a single row, as if you queried an imaginary table that has only one row. You can combine the use of the APPLY operator and this SELECT without FROM to circumvent the restriction concerning column alias reuse, like so:

SELECT SalesOrderID, OrderDate, Week_Day
FROM Sales.SalesOrderHeader
  CROSS APPLY (SELECT DATEPART(weekday, DATEADD(day, @@DATEFIRST - 7, OrderDate)) AS Week_Day) AS A
WHERE Week_Day NOT IN (1, 7);

The trick here is that the column alias isn’t generated in the outer query’s SELECT clause but rather in the inner query’s SELECT clause. And the inner query is evaluated in the FROM clause of the outer query. Since the FROM clause of the outer query is evaluated prior to all other clauses of the outer query, the column alias Week_Day is already available to all those clauses.

Even though there is a certain form of nesting in this technique, it’s far less awkward and verbose than all other workarounds.

Unfortunately, this technique fails to deliver the goods when it comes to aliases assigned to expressions that use window functions. Consider the following example:

SELECT SalesOrderID, OrderDate, RowNum
FROM Sales.SalesOrderHeader
  CROSS APPLY (SELECT ROW_NUMBER() OVER(ORDER BY SalesOrderID) AS RowNum) AS A
WHERE RowNum BETWEEN 51 AND 100;

Normally window functions are allowed only in the SELECT and ORDER BY clauses of a query, so here the attempt is to circumvent the fact that such functions are disallowed in the WHERE clause. However, once you place the window function in the inner query’s SELECT clause, the initial window context it is working with is always made of just that one row that the inner query represents—not the result set of the outer query. So I’m afraid that for now the only workaround is to use a table expression, like so:

WITH C AS
(
  SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER(ORDER BY SalesOrderID) AS RowNum
  FROM Sales.SalesOrderHeader
)
SELECT *
FROM C
WHERE RowNum BETWEEN 51 AND 100;

You can find more info about reuse of column aliases when it comes to window functions here.

Cheers,

BG