SQL Easy

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

For Data Analyst Data Scientist Data Engineer

The short answer

A column alias defined in SELECT can be referenced in ORDER BY but not in WHERE or HAVING, because SELECT runs after those clauses in the logical processing order. To reuse a complex expression in WHERE or HAVING, repeat the expression or wrap the query in a subquery or CTE.

How to think about it

This question is really about SQL’s logical processing order. The engine evaluates clauses in a specific sequence — FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY — and an alias defined in SELECT doesn’t exist yet when the earlier clauses run.

Defining an alias

-- AS keyword (preferred — clearest intent)
SELECT price * 0.9 AS discounted_price FROM products;

-- AS is optional — same result, but harder to read
SELECT price * 0.9 discounted_price FROM products;

What works and what doesn’t — and why

-- WORKS: ORDER BY runs after SELECT, so the alias is visible
SELECT price * 0.9 AS discounted_price
FROM products
ORDER BY discounted_price;

-- FAILS in most databases: WHERE runs before SELECT
-- The engine hasn't evaluated the alias yet
SELECT price * 0.9 AS discounted_price
FROM products
WHERE discounted_price < 50;  -- Error: column "discounted_price" does not exist

Two clean fixes:

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

-- Fix 2: wrap in a subquery or CTE — the alias becomes a real column name
WITH priced AS (
    SELECT price * 0.9 AS discounted_price, product_name
    FROM products
)
SELECT * FROM priced WHERE discounted_price < 50;

Try it — see where the alias is and isn’t reachable

Aliases with spaces or reserved words

Wrap in double quotes (ANSI standard) or backticks (MySQL):

SELECT first_name || ' ' || last_name AS "Full Name" FROM employees;
SELECT COUNT(*) AS "count" FROM events;  -- "count" is a reserved word

Database exceptions to the ANSI rule

MySQL and BigQuery allow SELECT aliases in GROUP BY and HAVING as a non-standard extension, so this works in those dialects:

-- Works in MySQL/BigQuery, fails in PostgreSQL/SQL Server:
SELECT YEAR(created_at) AS yr, COUNT(*) AS cnt
FROM orders
GROUP BY yr
HAVING cnt > 10;

SQLite also allows aliases in WHERE as a non-standard extension (you may have seen this in the playground above). Don’t rely on it in code that needs to be portable.

Learn it properly SELECT basics