An alias created in the SELECT list cannot be referenced in the WHERE clause of the same query. How does SQL's logical execution order explain this?
Interview preparation resource from Gate Smashers.
Because SQL evaluates WHERE before SELECT, a SELECT-list alias does not exist yet when WHERE is processed. Repeat the expression or compute it earlier (subquery or CTE) and filter in the outer query; some databases may permit aliases elsewhere but you should not rely on that behavior.
WITH employee_salary AS (
SELECT employee_name, salary * 12 AS annual_salary
FROM Employees
)
SELECT employee_name, annual_salary
FROM employee_salary
WHERE annual_salary > 600000;Direct answer
WHERE is logically evaluated before SELECT, so any alias defined in the SELECT list does not exist when the WHERE condition is evaluated.
Logical execution order (simplified)
To see why this happens, consider the simplified logical order of SQL query processing; clauses are evaluated in this sequence:
- Step 1: FROM / JOIN
- Step 2: WHERE
- Step 3: GROUP BY
- Step 4: HAVING
- Step 5: SELECT
- Step 6: DISTINCT
- Step 7: ORDER BY
Workarounds
Because the alias is not available in WHERE, you have these options to apply the same filter:
- Repeat the expression: Use the full expression in the WHERE clause instead of the alias.
- Subquery or CTE: Compute the expression in a subquery or CTE and then filter that result in the outer query using the alias.
- DB-specific allowance: Some database systems allow aliases in additional clauses, but WHERE should not rely on that behavior for portable SQL.
Example
The following shows the CTE approach: compute annual_salary in a CTE, then filter on that alias in the outer query. See the code block below for the exact SQL.