How to refer to a variable create in the course of executing a query in T-SQL, in the WHERE clause?

Question

What the title says, really.

If I SELECT [statement] AS whatever, why can't I refer to the whatever column in the body of the WHERE clause? Is there some kind of a workaround? It's driving me crazy.

Answer 1

As far as I'm aware, you can't directly do this in SQL Server.

If you REALLY have to use your column alias in the WHERE clause, you can do this, but it seems like overkill to use a subquery just for the alias:

SELECT *
FROM 
( 
   SELECT [YourColumn] AS YourAlias, etc...
   FROM Whatever
) YourSubquery
WHERE YourAlias > 2

You're almost certainly better off just using the contents of the original column in your WHERE clause.

Answer 2

It has to do with the way a SELECT statement gets translated into an abstract query tree: the 'whatever' only appears in the query result projection part of the tree, which is above the filtering part of the tree, so the WHERE clause cannot understand the 'whatever'. This is not some internal implementation detail, it is a fundamental behavior of relational queries: the projection of the result occurs after the evaluation of the joins and filters.

IS really trivial to work around the 'problem' by making the hierarchy of the query explicit:

select ...
  from (
    select [something] as whatever
    from ...
 ) as subquery
 WHERE whatever = ...;

A common table expression can also server the same purpose:

with cte as (
    select [something] as whatever
    from ...)
select ... from cte
 WHERE whatever = ...;

Answer 3

It's to do with the order of operations in the select statement. The WHERE clause is evaluated before the SELECT clause so this information isn't available. Although it is available in the ORDER BY clause as this is processed last.

As others have mentioned, a sub-query will get around this problem.