SQL / SORTING, LIMITING, AND BRANCHING OUTPUT
Why aliases work in ORDER BY but not in WHERE
Explain why a select-list alias is visible to ORDER BY but not WHERE, and filter computed values by repeating the expression or wrapping it in a CTE.
What you will learn
- Reuse a select-list alias as an ORDER BY sort key instead of retyping the expression
- Filter a computed value by spelling out its expression in WHERE, never by its alias
- Recite the resolution order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY
- Wrap a query in a CTE or derived table to make an alias filterable one level up
Understanding Why aliases work in ORDER BY but not in WHERE
SQL clauses are not resolved in the order you type them. Conceptually the engine assembles rows in FROM, throws rows away in WHERE, groups, filters groups in HAVING, then computes the select list, and only after that sorts with ORDER BY. An alias comes into existence when the select list is computed, so while WHERE is being resolved the name total does not exist yet; the only names in scope are the columns coming out of FROM. ORDER BY is the one clause resolved after the select list, which is exactly why it may name total.
The restriction is not arbitrary bookkeeping, it prevents questions with no answer. If output names were in scope in WHERE, then SELECT quantity AS unit_price, unit_price AS quantity FROM orders WHERE unit_price > 5 would have two defensible meanings, and SELECT sum(amount) AS total FROM orders WHERE total > 100 would ask WHERE to consult a value that only exists after grouping. ORDER BY has no such problem because it sorts a finished result table, so the standard defines its sort keys against that table's columns, which is also why an ordinal position such as ORDER BY 2 is legal there and meaningless in WHERE.
The working model is that an alias labels output, it is not a variable you can reference sideways. To filter on a computed value you either repeat the expression in WHERE, where both copies resolve against the same FROM columns and therefore agree, or you name it in an inner query so it becomes a real column of an intermediate result that the outer WHERE can see. Engines differ at the edges: SQLite accepts aliases in WHERE, MySQL accepts them in GROUP BY and HAVING, PostgreSQL accepts them in GROUP BY, and SQL Server accepts them only in ORDER BY, so a query that runs on one engine can fail on the next.
-- PostgreSQL
WITH orders(customer, unit_price, quantity) AS (
VALUES ('Ada', 12.50, 4),
('Grace', 9.00, 10),
('Linus', 4.25, 3),
('Ada', 30.00, 1)
)
SELECT customer,
unit_price * quantity AS total
FROM orders
WHERE unit_price * quantity > 20 -- expression repeated: no alias in scope yet
ORDER BY total DESC; -- alias reused: the select list has been computedAn alias is created by the select list, so WHERE (resolved before that list) cannot see it while ORDER BY (resolved after it) can.
Worked examples
The alias in WHERE
Shows the error PostgreSQL raises when a filter names a select-list alias.
WITH orders(customer, unit_price, quantity) AS (
VALUES ('Ada', 12.50, 4),
('Grace', 9.00, 10)
)
SELECT customer, unit_price * quantity AS total
FROM orders
WHERE total > 20;Example explained
Line 1Line 5 introduces the name total, but the select list is not evaluated until after WHERE has decided which rows survive.
Line 2Line 7 is resolved against the columns of orders only, that is customer, unit_price and quantity, so total is an unknown identifier.
Line 3The message says the column does not exist rather than complaining about the alias, because to the analyzer this is simply a name with no source.
Line 4MySQL and SQL Server reject the same query with their own unknown-column errors.
Make the alias a real column first
Computing the value one level down turns the alias into a column the outer WHERE can name.
WITH priced AS (
SELECT customer, unit_price * quantity AS total
FROM (VALUES ('Ada', 12.50, 4),
('Grace', 9.00, 10),
('Linus', 4.25, 3),
('Ada', 30.00, 1)) AS o(customer, unit_price, quantity)
)
SELECT customer, total
FROM priced
WHERE total > 20
ORDER BY total;Example explained
Line 1Inside the CTE, total is still only an alias, and a WHERE placed there could not use it either.
Line 2By the time the outer query runs, priced is a table whose columns are customer and total, so WHERE total > 20 refers to a genuine column of the FROM output.
Line 3The expression is written once, which is the real gain over repeating unit_price * quantity in two clauses.
Line 4PostgreSQL 12 and later inlines a non-recursive CTE referenced once, so this rewrite is about naming rather than an extra pass over the data.
SQLite bends the rule
Demonstrates that alias-in-WHERE is accepted by SQLite as an engine extension, which hides portability bugs.
-- sqlite3 CLI, default list output
CREATE TABLE t(unit_price REAL, quantity INTEGER);
INSERT INTO t VALUES (12.5, 4), (9.0, 10), (4.25, 3);
SELECT unit_price * quantity AS total FROM t WHERE total > 20 ORDER BY total;Example explained
Line 1SQLite resolves a name in WHERE against the table's columns first and then falls back to the select-list aliases, so total is found.
Line 2That fallback is an extension, not standard behaviour: the identical statement fails on PostgreSQL, MySQL and SQL Server.
Line 3Because the fallback is second, an alias that collides with a column name still loses to the column, so the leniency is not even consistent.
Line 4The portable version of this query repeats unit_price * quantity in WHERE and keeps the alias for ORDER BY only.
Important notes
In PostgreSQL the alias must be the entire sort key: ORDER BY total DESC works, but ORDER BY total / 2 fails with the same does-not-exist error, because only a bare output name is looked up in the select list.
The same pipeline rule is why a window function, or an alias for one, cannot appear in WHERE; computing it in an inner query and filtering outside is the standard fix.
Common mistakes
Reading "column total does not exist" as a missing column and responding by adding a column or renaming the alias; the alias was never wrong, WHERE is simply resolved before the select list exists.
Naming an alias after an existing column, as in SELECT price * 0.9 AS price FROM products WHERE price < 50 ORDER BY price: nothing errors, but WHERE filters on the stored price while PostgreSQL's ORDER BY sorts by the discounted output, so one identifier quietly means two different things.
Assuming HAVING behaves like ORDER BY because MySQL accepts an alias there; PostgreSQL and standard SQL reject it, so the query breaks the moment it moves engines.
Try it yourself
Change, predict, then run
Run SELECT name, length(name) AS n FROM (VALUES ('ada'), ('grace'), ('linus')) AS t(name) WHERE n > 3 in a PostgreSQL sandbox and read the error. Then fix it twice, once by repeating length(name) in WHERE and once by moving the SELECT into a CTE and filtering on n outside, confirming both return grace and linus.
Open the SQL workspaceCheck your understanding
The table products has a column price. On PostgreSQL, SELECT price * 0.9 AS price FROM products WHERE price < 50 runs without error. Which rows does it return?
- Rows where price * 0.9 is under 50, because the alias takes effect before WHERE is evaluated
- No rows, because the alias shadows the column and leaves the comparison undefined
- Rows whose stored price is under 50, each shown as 90 percent of that price
- Every row, because a comparison against an aliased name is ignored
Show answer
WHERE is resolved against the columns coming out of FROM, so price there can only be the stored column; the alias merely labels the value in the output. Option 0 is tempting because the same identifier appears twice, but a select-list alias never influences which rows survive WHERE. To filter the discounted value you must repeat price * 0.9 in WHERE or compute it in an inner query and filter outside.