RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Avoid Transact-SQL's Column Alias Limitations

A column alias seems like a great tool for referencing complex expressions, but SQL Server doesn't work that way. Learn a simple workaround.

ou might expect all SQL Server's clauses to support a column alias. As reasonable as that assumption seems, SQL Server can't honor a column alias in that way. If you refer to an aliased column in another clause, SQL Server returns an error—surprise! Figure 1 shows the result of running such a query; SQL Server can't find the aliased column TotalCost. That's not a bug; SQL Server requires the alias—it has to call the column something!

SQL Server returns the error in Figure 1 because its engine works logically through the calls as follows:

  1. SQL Server evaluates the JOIN (and any UNION operator) in the FROM clause. Imagine SQL Server combining the referenced tables into one table. SQL Server's not retrieving data, just combining data sources.
  2. SQL Server excludes whole records by applying a WHERE clause.
  3. SQL Server applies the GROUP BY clause, which combines records when necessary, further limiting the results.
  4. SQL Server applies a HAVING clause, in another attempt to limit the records the query retrieves.
  5. At this point, SQL Server has identified the records to retrieve, and now uses the query's SELECT clause to limit the columns.

Figure 1. SQL Server Engine Can't Find TotalCost: This error occurs because SQL Server can't find the aliased column.
Figure 2. The WHERE Clause Supports a Column Alias: ORDER BY is the only place SQL Server supports a column alias.

The same thing will happen with a GROUP BY or HAVING clause that refers to a column alias. SQL Server can't apply an alias that it hasn't defined yet. Outside of SELECT's column list, ORDER BY is the only place SQL Server supports a column alias (see Figure 2).

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date