---
title: "Aliases in SQL: Column, Table and Subquery Reference"
description: "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."
canonical: "https://www.aifordatabase.com/blog/aliases-in-sql/"
author: "Dhrumil Barot"
author_bio: "Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team."
published: 2026-10-31
updated: 2026-10-31
---

# 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)

| Clause | PostgreSQL | MySQL 8 | SQL Server | Oracle 23ai | SQLite | Standard SQL |
| --- | --- | --- | --- | --- | --- | --- |
| `WHERE` | No | No | No | No | Extension, avoid | No |
| `GROUP BY` | Yes | Yes | No | Yes (new in 23ai) | Yes | No |
| `HAVING` | No | Yes | No | Yes (new in 23ai) | Yes | No |
| `ORDER BY` | Yes | Yes | Yes | Yes | Yes | Yes |
| Same `SELECT` list | No | No | No | No | No | No |
| `AS` before table alias | Optional | Optional | Optional | Not allowed | Optional | Optional |

Sources: [PostgreSQL SELECT](https://www.postgresql.org/docs/current/sql-select.html), [MySQL alias problems](https://dev.mysql.com/doc/refman/8.4/en/problems-with-alias.html), [Oracle-Base on 23ai](https://oracle-base.com/articles/23/group-by-and-having-clause-using-column-alias-or-column-position-23), [SQLite SELECT](https://www.sqlite.org/lang_select.html). Snowflake and Databricks add lateral column aliases on top.

![Clauses run in logical order, so a SELECT alias exists only for the clauses after it](https://cdn.sanity.io/images/upsr56wu/production/a0167009f71babd1ab68674d5f0340e5fda13395-1344x326.png?w=1280)

FIG 01 — Anything left of SELECT can't see a column alias in standard SQL.

### 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;
```

![A self join reads one employees table twice, once as e and once as m](https://cdn.sanity.io/images/upsr56wu/production/d22f54ced28fbc192507541af4de90a8fcac3fdd-1344x410.png?w=1280)

FIG 02 — e is the employee row, m is the same table read as the manager.

## 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](https://www.aifordatabase.com/) 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/).

D

Written by [Dhrumil Barot](https://www.aifordatabase.com/about/#founder)

Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team.

[LinkedIn](https://www.linkedin.com/in/bdhrumil)[X](https://x.com/fyidhrumil)

## Frequently Asked Questions

### What is an alias in SQL?

An alias is a temporary name for a column, table or subquery, assigned with AS or by simply writing the name after it. It lasts only for that query and doesn't change the schema.

### Can I use a column alias in WHERE clauses?

No. WHERE is evaluated before SELECT assigns the alias, so every major database rejects it. Repeat the expression, or put the query in a CTE or subquery and filter on the alias in the outer query.

### Do I need AS for an alias?

Usually not. AS is optional for column and table aliases in PostgreSQL, MySQL and SQL Server. It's required in PostgreSQL when a column alias is a keyword, and it's forbidden before table aliases in Oracle.

### How do I alias a table in a self join?

List the table twice with different aliases, for example FROM employees e JOIN employees m ON m.id = e.manager\_id, and qualify every column with e. or m..

### Are SQL aliases case sensitive?

Unquoted aliases are case-insensitive in PostgreSQL (folded to lowercase), Oracle (folded to uppercase) and SQL Server under default collations. Quoted aliases such as "Revenue" keep their case and must be referenced exactly. In MySQL, column aliases are case-insensitive but table aliases follow lower\_case\_table\_names on some platforms.

### Can I use an alias in HAVING?

In MySQL and Oracle 23ai, yes. In PostgreSQL and SQL Server, no: repeat the aggregate, for example HAVING COUNT() > 5.

### Can I reference an alias in the same SELECT list?

Not in PostgreSQL, MySQL, SQL Server or Oracle: SELECT price qty AS revenue, revenue 0.2 AS tax fails. Snowflake and Databricks allow it as a lateral column alias. Elsewhere, repeat the expression or use a CTE.

Read next

*   [8 CALL CENTER METRICS DASHBOARD EXAMPLES (WITH SQL) →](https://www.aifordatabase.com/blog/call-center-metrics-dashboard/)
*   [9 BEST QUERY BUILDERS FOR SQL: VISUAL, CODE, AI (COMPARED) →](https://www.aifordatabase.com/blog/query-builders/)
*   [WHY IS POSTGRES BETTER THAN MYSQL? POSTGRES VS MYSQL →](https://www.aifordatabase.com/blog/why-is-postgres-better-than-mysql/)

Source: https://www.aifordatabase.com/blog/aliases-in-sql/