How do column aliases work in SQL, and where can you reference them?

How to think about it

This question is really about SQL’s logical processing order. The engine evaluates clauses in a fixed sequence — FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY — and an alias defined in SELECT doesn’t exist yet when the earlier clauses run. So the rule falls straight out of position: ORDER BY (which runs after SELECT) can see the alias; WHERE and HAVING (which run before) cannot.

A worked example — where the alias reaches

ORDER BY discounted_price works because by the time it runs, the alias is a real, computed column:

SELECT product_name, price, price * 0.9 AS discounted_price
FROM products
ORDER BY discounted_price ASC;

product_name	price	discounted_price
Gadget Y	30	27.0
Widget A	55	49.5
Widget B	90	81.0
Gadget X	120	108.0
Tool Z	200	180.0

The rows come back sorted by the discounted price, lowest first — the alias did its job in ORDER BY. But move that same name into WHERE and most engines reject it:

-- FAILS in PostgreSQL/SQL Server: WHERE runs before SELECT computes the alias
SELECT price * 0.9 AS discounted_price
FROM products
WHERE discounted_price < 50;   -- ERROR: column "discounted_price" does not exist

Two clean fixes — repeat the expression, or promote it to a real column with a subquery/CTE:

-- Fix 1: repeat it (the optimiser usually deduplicates the work)
SELECT price * 0.9 AS discounted_price FROM products WHERE price * 0.9 < 50;

-- Fix 2: wrap it, so the alias becomes an actual column name
WITH priced AS (SELECT price * 0.9 AS discounted_price, product_name FROM products)
SELECT * FROM priced WHERE discounted_price < 50;

Dialect quirks worth naming

• Quoting: double quotes are for identifiers (AS "Full Name"), single quotes are for string values. AS 'full_name' is technically a string literal — some engines accept it as an alias, others produce portability bugs.
• Non-standard leniency: MySQL and BigQuery allow SELECT aliases in GROUP BY/HAVING; SQLite even allows them in WHERE (so the failing query above actually works in SQLite). Don’t rely on either in portable code.