T-SQL – How to use where filter with column alias

by

Problem

If you try to use a column alias in a where statement, you receive an error message that reads

"Invalid column name 'myAlias'."

For example, this statement will give an error

SELECT id, CASE WHEN 1=1 THEN 1 END AS myAlias
FROM myTable
WHERE myAlias = 1

Solution

There are two common solutions to this problem – repeating the alias statement in the WHERE using a sub-query (prefered method).

Using a sub-query (prefered method)

SELECT id, myAlias
FROM
(
    SELECT id, CASE WHEN 1=1 THEN 1 END AS myAlias
    FROM myTable
) data
WHERE myAlias = 1

Repeating the alias statement

In this example, the CASE statement is extremely simplified (1=1) – however your example will most likely be much more complicated.

Repeating the statement is not recommended because of the performance overhead.

SELECT id, CASE WHEN 1=1 THEN 1 END AS myAlias
FROM myTable
WHERE 1=1