Why can't a WHERE clause reference a column alias defined in the SELECT list?

## The error you actually see ```sql SELECT price * 1.2 AS gross FROM products WHERE gross > 100; -- ERROR: column "gross" does not exist ``` The message is literal, not pedantic: at the moment `WHERE` is evaluated, there is no column called `gross` anywhere in scope. ## Why the pipeline forbids it SQL's clauses are evaluated in a defined logical order — `FROM`, `WHERE`, `GROUP BY`, `HAVING`, `SELECT`, `DISTINCT`, `ORDER BY`, row limit — and each stage may reference only what earlier stages produced. `WHERE` is stage two. Its input is the row set built by `FROM`, whose columns are the columns of the underlying tables. The select list, where output expressions are computed and given names with `AS`, is stage five. An alias is therefore *created after the filter has already run*: allowing `WHERE` to use it would require the query to know its own output before it has decided which rows are in that output. The same rule produces the sibling error: ```sql SELECT dept_id, COUNT(*) AS n FROM employees WHERE COUNT(*) > 5 -- illegal GROUP BY dept_id; ``` `WHERE` still runs before `GROUP BY`, so there are no groups and no aggregate values to compare — a group-level predicate has to wait for `HAVING`, which is evaluated after grouping. ## Where the alias *is* visible `ORDER BY` is the clause the standard explicitly lets you sort by output name, because it operates on the finished select list: ```sql SELECT price * 1.2 AS gross FROM products ORDER BY gross DESC; -- legal everywhere ``` Beyond that, engines differ. PostgreSQL documents that an output column name may be used in `GROUP BY` and `ORDER BY` but not in `WHERE` or `HAVING`; other engines are more or less permissive in `GROUP BY` and `HAVING`. Treat anything beyond `ORDER BY` as an extension you should not lean on in portable SQL, and never expect an alias to work in `WHERE` on any engine. One more scoping rule bites at the same time: an alias is not visible to *other expressions in the same select list* either. `SELECT price * 1.2 AS gross, gross * 0.1 AS fee` is not portable SQL, for the same reason — the whole select list is one stage. ## The portable fixes **Repeat the expression.** The bluntest fix and often the right one for a short expression: ```sql SELECT price * 1.2 AS gross FROM products WHERE price * 1.2 > 100; ``` **Filter one stage later, in a derived table.** The inner query's select list is a completed stage from the outer query's point of view, so the alias is a real column there: ```sql SELECT gross FROM (SELECT price * 1.2 AS gross FROM products) AS p WHERE gross > 100; ``` **Use a `WITH` clause.** Same mechanism, better readability when the expression is long or reused: ```sql WITH priced AS ( SELECT id, price * 1.2 AS gross FROM products ) SELECT id, gross FROM priced WHERE gross > 100; ``` **Move a group predicate to `HAVING`** when the thing you wanted to filter on was an aggregate rather than a scalar expression. ## Which fix to choose Repeating a short arithmetic expression costs nothing and keeps the query flat; engines evaluate the duplicated expression once in practice, and duplicating it does not by itself change what indexes are usable — that depends on the shape of the expression, not on how many times you wrote it. Prefer the derived table or `WITH` form when the expression is long, is derived from several columns, appears in more than one predicate, or encodes a business rule you do not want to see maintained in two places, since the copies drift. A fourth option people reach for — wrapping the expression in a scalar function or a view — is the same trick in different clothing: it moves the computation into an earlier stage so the name exists by the time you filter. ## What interviewers listen for A weak answer is "SQL just doesn't allow it" or "it's a syntax rule". A strong answer names the stage that mints the alias and the stage that wanted to read it, then shows both fixes and says which one is appropriate when. Being able to add that `ORDER BY` *can* use the alias — and explain why that is consistent rather than an exception — is the tell that you understand the pipeline rather than having memorised a prohibition.