SQL Alias Scope Explained

Why WHERE can't see a SELECT alias

SQL's logical processing order runs FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Column aliases are defined inside SELECT, which means they don't exist as far as any clause evaluated before SELECT is concerned — including WHERE. This is the single most common alias error:

-- Error: column "order_age_days" does not exist
SELECT id, order_date,
       DATEDIFF(now(), order_date) AS order_age_days
FROM orders
WHERE order_age_days > 30;

The fix is to either repeat the expression, or move the filter into an outer query wrapped around the aliased one:

-- Option 1: repeat the expression
SELECT id, order_date,
       DATEDIFF(now(), order_date) AS order_age_days
FROM orders
WHERE DATEDIFF(now(), order_date) > 30;

-- Option 2: filter in an outer query, where the alias is a real column
SELECT * FROM (
  SELECT id, order_date,
         DATEDIFF(now(), order_date) AS order_age_days
  FROM orders
) sub
WHERE order_age_days > 30;

GROUP BY and HAVING: it depends on the database

Both GROUP BY and HAVING also logically run before SELECT in the standard order — see Why HAVING Is Used After GROUP BY for exactly why HAVING's position matters. Standard SQL says neither should be able to see a SELECT alias, but real databases diverge here as a practical convenience:

Why ORDER BY can always use an alias

ORDER BY is the one clause that logically runs after SELECT in every major database, so by the time sorting happens, every alias has already been computed and is simply a regular, available column:

SELECT id, total, total * 0.08 AS tax
FROM orders
ORDER BY tax DESC;
-- Works everywhere — ORDER BY runs after SELECT

Table alias scope

A table (or CTE) alias defined in FROM or a JOIN clause has a much wider scope than a column alias — it's visible throughout the entire query it's defined in: in the join conditions, WHERE, GROUP BY, HAVING, SELECT, and ORDER BY, since it's established at the very first logical step, FROM.

SELECT c.name, o.total          -- SELECT: visible
FROM customers c
JOIN orders o ON o.customer_id = c.id  -- ON: visible
WHERE c.region = 'West'          -- WHERE: visible
ORDER BY o.total DESC;          -- ORDER BY: visible

What a table alias is not visible in is an unrelated, non-correlated subquery — it belongs to the query where FROM defined it, not to every query in the statement.

CTE and subquery alias scope

A CTE's name becomes usable only in the queries written after it — a non-recursive CTE cannot reference its own name inside its own definition. A recursive CTE is the deliberate exception, where the name is explicitly allowed to reference itself in the recursive term. This distinction matters when choosing between the two — see CTEs vs Subqueries – When to Use What and SQL Recursive CTEs With Examples.

WITH big_orders AS (
  SELECT * FROM orders WHERE total > 1000
)
-- big_orders is only usable from here downward
SELECT customer_id, COUNT(*)
FROM big_orders
GROUP BY customer_id;

A correlated subquery is the one place a query genuinely reaches into an outer query's table alias by design — that's exactly what "correlated" means, covered in Correlated Subqueries Explained Simply.

Cross-database summary

Common error and the fix

Putting it all together — a query that tries to use an alias everywhere, and the portable version that works on any database:

SELECT customer_id,
       SUM(total) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING SUM(total) > 1000   -- repeated, not "revenue"
ORDER BY revenue DESC;         -- alias is fine here