iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
A SELECT-list alias names an output expression, so it is not available to WHERE under the usual logical binding rules. ORDER BY can use it because the output name is available by then. This describes logical name visibility—not a promise that a database physically executes each clause in that written order.
Why can’t I use a SELECT alias in WHERE?
Consider a query that calculates each line item’s total:
SELECT price * quantity AS total
FROM line_items
WHERE total > 100;Here, total is an alias for the expression price * quantity in the SELECT list. In SQL Server’s documented logical processing model, WHERE is considered before SELECT, so the output alias has not yet become available to that clause. MySQL 8.4 likewise says a SELECT alias cannot be referenced in WHERE because its value might not yet be determined when the condition is evaluated. MySQL 8.4 Reference Manual: SELECT Statement
Recommended Free Tools
This is specifically about a column alias defined in SELECT. It is not a rule about table aliases introduced in FROM.
#1 Best Overall
Why does ORDER BY recognize a column alias?
ORDER BY sorts the query’s output, so an output-column name is useful there. In SQL Server’s logical binding order, ORDER BY follows SELECT; PostgreSQL also documents output column names as valid references in ORDER BY. For example, MySQL documents this use:
SELECT CONCAT(last_name, ', ', first_name) AS full_name
FROM people
ORDER BY full_name;In that query, full_name refers to the value of the SELECT expression for sorting. MySQL 8.4 Reference Manual: SELECT Statement · PostgreSQL 18 Documentation: SELECT
What “execution order” means—and what it does not
SQL Server documents a logical processing order for a SELECT statement that places WHERE before SELECT and ORDER BY after it. This order helps explain which names are visible to which clauses. It does not mean the database must physically perform every operation in that sequence. Microsoft says the query processor determines physical execution, which can vary from the logical list. Microsoft Learn: SELECT (Transact-SQL), Logical processing order
Keep the distinction clear: logical order explains alias availability; the optimizer chooses a physical plan. Do not infer how many times an expression is physically evaluated from the logical order alone.
How to filter on a calculated value
Repeat the expression in WHERE
For a row-level condition, write the underlying expression in WHERE:
SELECT price * quantity AS total
FROM line_items
WHERE price * quantity > 100;This makes the row filter independent of the SELECT-list alias. It does not, by itself, establish how many times the database evaluates the expression.
Put the expression in a subquery
A query boundary makes the computed output an input column to the outer query:
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →SELECT total
FROM (
SELECT price * quantity AS total
FROM line_items
) AS priced_items
WHERE total > 100;The outer WHERE now refers to a column exposed by its FROM source, rather than to an alias defined in that same query block’s SELECT list.
How alias rules differ by clause and database
Alias visibility is not one universal rule for every clause. These documented dialect behaviors illustrate why the database and clause both matter:
| Database documentation | Alias behavior | Name-resolution detail |
|---|---|---|
| SQL Server (Microsoft Learn page for SQL Server 2016) | SELECT aliases are unavailable to preceding clauses and available to subsequent clauses such as ORDER BY. |
The page presents this as logical processing order and cautions that physical execution can differ. |
| MySQL 8.4 | SELECT aliases may be used in GROUP BY, ORDER BY, and HAVING, but not WHERE. |
For ORDER BY, lookup searches SELECT expressions before FROM columns; for GROUP BY and HAVING, it searches FROM columns first. Position references in ORDER BY and GROUP BY are deprecated because that syntax was removed from the SQL standard. |
| PostgreSQL 18 | ORDER BY may use an output name or position, as well as an input expression. |
If a simple name matches both an output name and an input name, ORDER BY resolves it as the output name; GROUP BY makes the opposite choice. |
Sources: Microsoft Learn, MySQL 8.4 Reference Manual, and PostgreSQL 18 Documentation. These details apply to the named documentation; check the relevant version’s manual before assuming another database behaves the same way.
WHERE and HAVING are not interchangeable
WHERE filters input rows before grouping; HAVING filters groups. MySQL documents SELECT aliases as usable in HAVING, but that allowance does not make aliases available in WHERE, nor should it be treated as a universal cross-database rule.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
For a row-level test such as whether an individual line total exceeds a threshold, use WHERE with the expression or a query boundary. For a condition on grouped results, use HAVING and verify the alias rules for your database.
Avoid confusing names in ORDER BY and GROUP BY
A simple name can be ambiguous if it names both an input column and a SELECT output. PostgreSQL resolves that collision as the output name in ORDER BY, but as the input name in GROUP BY. MySQL documents its own lookup precedence: it searches SELECT expressions first for ORDER BY, while GROUP BY and HAVING search FROM columns first. When a name could refer to either source, use distinct, descriptive output aliases and qualify input columns where appropriate; do not assume every clause resolves the collision the same way.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
