Why can't you use a column alias in the WHERE clause?
WHERE runs before SELECT, so the alias does not exist yet when the filter is evaluated. Repeat the expression, or put the query in a subquery or CTE and filter outside.
Explain it simply
An alias is a nickname given while writing the final result. WHERE runs earlier, while rows are still being picked from the table, so the nickname has not been invented yet. It is like using a student's roll-call nickname before the teacher has assigned it.
Example
This fails in standard SQL:
SELECT salary * 12 AS annual
FROM employees
WHERE annual > 500000; -- error: unknown column "annual"Fix 1, repeat the expression:
SELECT salary * 12 AS annual
FROM employees
WHERE salary * 12 > 500000;Fix 2, wrap it:
SELECT *
FROM (SELECT salary * 12 AS annual FROM employees) t
WHERE annual > 500000;ORDER BY can use the alias because it runs after SELECT. MySQL also lets GROUP BY and HAVING use aliases as an extension, but that is not portable.
What to say in the interview
"The logical order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. WHERE comes before SELECT, so aliases are not visible there. I repeat the expression or use a derived table or CTE."
Common mistakes
- Assuming MySQL's relaxed alias rules work in SQL Server or Oracle.