SQL Logical Query Order: Why WHERE Can't See Your Alias

Written order vs evaluated order

You write a query starting with SELECT, but the database evaluates it in a different logical order:

This is the logical order — the optimizer may physically reorder work (for example pushing filters into a scan), but the result must be as if this order were followed.

Consequence 1 — WHERE cannot use a SELECT alias

-- fails in most engines: net_amount does not exist yet when WHERE runs
SELECT amount - discount AS net_amount
FROM orders
WHERE net_amount > 100;
 
-- repeat the expression, or wrap it
SELECT * FROM (
  SELECT amount - discount AS net_amount FROM orders
) t
WHERE net_amount > 100;

Some warehouses (Snowflake, Databricks, BigQuery in some cases) allow aliases in WHERE as an extension. Don't rely on it in portable SQL.

Consequence 2 — WHERE filters rows, HAVING filters groups

SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE status = 'PAID'          -- row filter: runs before grouping
GROUP BY customer_id
HAVING SUM(amount) > 1000;     -- group filter: runs after aggregation

Consequence 3 — window functions run after GROUP BY

Window functions are computed after grouping and HAVING, just before SELECT finishes. So you can rank aggregated values in one query:

SELECT customer_id,
       SUM(amount) AS total,
       RANK() OVER (ORDER BY SUM(amount) DESC) AS spend_rank
FROM orders
GROUP BY customer_id;

But you cannot filter on a window function in WHERE — use a subquery or QUALIFY where supported.

Consequence 4 — ORDER BY can use aliases

ORDER BY runs after SELECT, so aliases are visible there:

SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
ORDER BY total DESC;

Interview answer in one breath

SQL is evaluated FROM, WHERE, GROUP BY, HAVING, window functions, SELECT, DISTINCT, ORDER BY, LIMIT. That is why WHERE cannot see a SELECT alias, why aggregates belong in HAVING, and why you filter window results in an outer query or with QUALIFY.