Aliases in SQL: Column, Table and Subquery Reference

TL;DR: An alias in SQL is a temporary name for a column, table or subquery, written with AS (usually optional) and valid only inside that one query. Column aliases work in ORDER BY everywhere, but never in WHERE, because WHERE runs before SELECT names anything. Whether they work in GROUP BY and HAVING depends on the database.

Aliases in SQL let you rename a result column (SUM(amount) AS revenue), shorten a table name (FROM customers AS c) or name a derived table (FROM (SELECT ...) AS t). The alias exists only while the query runs; nothing in the schema changes. The rule that trips everyone up is clause order: the database logically evaluates FROM, WHERE, GROUP BY and HAVING before SELECT, so a name created in SELECT doesn't exist yet in those clauses, and does exist in ORDER BY. MySQL and Oracle 23ai bend that rule for GROUP BY and HAVING; PostgreSQL bends it for GROUP BY only; SQL Server doesn't bend it at all. The table below is the lookup.

Where can you use a column alias? (per database)

Sources: PostgreSQL SELECT, MySQL alias problems, Oracle-Base on 23ai, SQLite SELECT. Snowflake and Databricks add lateral column aliases on top.

Can I use a column alias in WHERE?

No. MySQL's manual gives the reason: when WHERE is evaluated, "the column value may not yet have been determined."

-- fails: column "revenue" does not exist (PostgreSQL)
-- fails: Unknown column 'revenue' in 'where clause' (MySQL error 1054)
-- fails: Invalid column name 'revenue' (SQL Server)
SELECT price * quantity AS revenue
FROM order_items
WHERE revenue > 100;

Three fixes, in order of preference:

-- 1. repeat the expression (the optimizer computes it once)
SELECT price * quantity AS revenue
FROM order_items
WHERE price * quantity > 100;

-- 2. wrap it in a subquery or CTE, then filter on the alias
WITH items AS (
  SELECT price * quantity AS revenue FROM order_items
)
SELECT revenue FROM items WHERE revenue > 100;

-- 3. SQL Server: name the expression with CROSS APPLY
SELECT v.revenue
FROM order_items AS oi
CROSS APPLY (SELECT oi.price * oi.quantity AS revenue) AS v
WHERE v.revenue > 100;

For filtering on an aggregate, the filter belongs in HAVING, not WHERE; in PostgreSQL and SQL Server repeat the aggregate there (HAVING SUM(total_amount) > 1000), in MySQL and Oracle 23ai the alias works (HAVING revenue > 1000).

How do column and table aliases work?

Column aliases

A column alias names what you return. Use it for every aggregate and expression, because otherwise the database invents a name: PostgreSQL falls back to names like ?column? or the function name, which is useless in a report or an API response.

-- name aggregates so the result is readable
SELECT
  customer_id,
  COUNT(*)            AS order_count,
  SUM(total_amount)   AS revenue,
  AVG(total_amount)   AS avg_order_value
FROM orders
GROUP BY customer_id
ORDER BY revenue DESC;

Aliases with spaces or capitals need quoting: double quotes in PostgreSQL, Oracle and standard SQL (AS "Monthly Revenue"), backticks or double quotes in MySQL, square brackets or double quotes in SQL Server (AS [Monthly Revenue]). In PostgreSQL an unquoted alias is folded to lowercase, so AS Revenue comes back as revenue.

Do I need AS?

No, in most cases AS is optional for both columns and tables: SUM(total_amount) revenue works. Two exceptions matter:

• PostgreSQL column aliases that are keywords need AS. The docs say AS is required "if the new column name matches any keyword at all, reserved or not", so SELECT created_at AS year works and SELECT created_at year doesn't. The docs recommend always writing AS or double-quoting.
• Oracle table aliases can't use `AS`. FROM customers AS c fails with ORA-00933: SQL command not properly ended; write FROM customers c. Column aliases in Oracle accept AS.

Write AS for columns (it makes a missing comma visible: SELECT name email silently renames name to email) and follow your team's style for tables.

Table aliases

A table alias shortens qualified column names and is mandatory when the same table appears twice.

-- table aliases keep a three-table join readable
SELECT c.name, o.id AS order_id, p.title AS product
FROM customers AS c
JOIN orders    AS o ON o.customer_id = c.id
JOIN products  AS p ON p.id = o.product_id
WHERE o.created_at >= DATE '2026-01-01';

Once you alias a table, use the alias. In PostgreSQL, SELECT customers.name FROM customers c fails with invalid reference to FROM-clause entry for table "customers", because the alias hides the original name. Pick aliases that mean something (c, o, p or cust, ord), not a, b, c in table order.

When two joined tables share a column name, an unqualified reference fails: PostgreSQL says column reference "id" is ambiguous, MySQL says Column 'id' in field list is ambiguous. Qualify it with the alias (o.id) and alias the output if both are returned (o.id AS order_id, c.id AS customer_id). See our [JOIN glossary entry](/glossary/join/) for how joins multiply rows.

How do I alias in a self join?

Give the same table two aliases and treat them as two tables. Without aliases the query is invalid: PostgreSQL reports table name "employees" specified more than once, MySQL reports Not unique table/alias: 'employees'.

-- each employee with their manager's name
SELECT e.name AS employee, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON m.id = e.manager_id;

Subquery and CTE aliases

A subquery in FROM (a derived table) needs a name in standard SQL, MySQL and SQL Server. PostgreSQL made the alias optional in version 16; on PostgreSQL 15 and older, leaving it off fails with subquery in FROM must have an alias. MySQL reports Every derived table must have its own alias (error 1248).

-- the derived table alias "t" is required in MySQL and SQL Server
SELECT t.customer_id, t.revenue
FROM (
  SELECT customer_id, SUM(total_amount) AS revenue
  FROM orders
  GROUP BY customer_id
) AS t
WHERE t.revenue > 1000;

A CTE is a named subquery declared up front, and its name works like a table alias for the rest of the statement. You can also alias the CTE's columns in a list: WITH monthly (month, revenue) AS (...). Our [CTE explainer](/glossary/cte/) covers when to choose one over a derived table.

Why does an alias work in ORDER BY but not GROUP BY in SQL Server?

Because of logical processing order. Microsoft documents the order as FROM, ON, JOIN, WHERE, GROUP BY, WITH CUBE or WITH ROLLUP, HAVING, SELECT, DISTINCT, ORDER BY, TOP. ORDER BY is the only clause after SELECT, so it's the only one that sees column aliases. Repeat the expression in GROUP BY or move it into a CTE:

-- SQL Server: GROUP BY needs the expression, ORDER BY can use the alias
SELECT YEAR(created_at) AS order_year, COUNT(*) AS orders
FROM orders
GROUP BY YEAR(created_at)
ORDER BY order_year;

PostgreSQL accepts GROUP BY order_year as an extension, but watch the precedence trap: if a real column has the same name as your alias, PostgreSQL's GROUP BY uses the input column, while ORDER BY uses the output alias. Don't give an alias the name of an existing column.

Alias rules and best practices

• Scope is one query level. An alias defined inside a subquery isn't visible outside it except as that subquery's output column.
• No alias on `*`. SELECT * AS x and SELECT t.* AS x are invalid; aliases apply to single expressions.
• Quoted GROUP BY in MySQL. GROUP BY \a\` groups by the alias; GROUP BY 'a' groups by the constant string 'a'`, a silent bug the MySQL manual warns about.
• Keep names stable. Dashboards, BI tools and API clients bind to output column names, so renaming revenue to total_revenue breaks them. Our [SQL cheat sheet](/blog/sql-cheat-sheet/) lists naming conventions worth standardizing.
• Alias every computed column in shared queries; generated names like count or ?column? collide when you join results.

If your team mostly writes SQL to answer the same business questions (revenue by month, active customers, churned accounts), AI for Database lets them ask in plain English against PostgreSQL, MySQL or SQL Server and shows the SQL it ran, aliases included, so the people who know SQL can check it and the people who don't still get the number. Try AI for Database free at aifordatabase.com. For window functions, where aliases and PARTITION BY interact, see [SQL PARTITION BY explained](/blog/sql-partition-by/).