SQL online course

SQL Aliases: Rename Columns and Expressions in Query Results

Learn how SQL aliases and the AS keyword give columns, expressions, aggregates, and tables clear temporary names in query results.

What Is an SQL Alias?

An alias is a temporary name assigned within a SQL query. A column alias labels a selected column or expression in the returned result set, which is the rows and columns produced by a query.

An alias changes the displayed result heading; it does not change the underlying table schema, stored column name, or data. For example, assigning first_name to a column does not permanently rename that column. A permanent schema change requires a statement such as ALTER TABLE, not a SELECT alias.

SELECT fName AS first_name
FROM Customers;

The query still reads the stored column named fName. It simply presents that output column with the heading first_name.

Basic Column Alias Syntax

The usual syntax for a column alias is:

SELECT column_name AS alias_name
FROM table_name;

source column or expression: The value to return, such as fName or AMOUNT * 1.10.

AS keyword: Introduces the temporary name.

alias name: The heading used for that output column.

source table: The table from which the value is selected.

For example:

SELECT COUNTRY AS country
FROM Customers;

Here, COUNTRY is the stored column, AS introduces the alias, country is the output label, and Customers is the source table.

Many SQL dialects allow the AS keyword to be omitted:

SELECT COUNTRY country
FROM Customers;

Although this is often valid, using AS makes the distinction between a source expression and its alias clearer and improves readability.

Readable Output Labels

Aliases are useful when database column names are abbreviated, inconsistent, or intended for internal use rather than display. A report or application response is easier to understand when its headings describe the values clearly.

Identifier-style aliases

A one-word alias, or an alias using an identifier-style convention such as snake case, usually needs no special quoting:

SELECT fName AS first_name,
       lName AS last_name,
       COUNTRY AS country
FROM Customers;

The result headings are first_name, last_name, and country.

Aliases containing spaces

For a heading such as First Name, quote the alias with the identifier delimiter required by your database:

SELECT fName AS "First Name",
       lName AS "Last Name"
FROM Customers;

Without quoting, SQL generally interprets First and Name as separate tokens. A name containing spaces is a delimited identifier: an identifier enclosed in database-appropriate quoting characters.

Single quotes are normally used for string literals, not portable identifier names. Do not rely on AS 'First Name' across database systems.

Multiple Aliases in One SELECT

A SELECT list is the comma-separated collection of columns and expressions returned by a query. Each item can have its own alias:

SELECT fName AS "First Name",
       lName AS "Last Name",
       COUNTRY AS "Country"
FROM Customers;

The commas separate the selected items. Each AS applies only to the expression immediately before it.

Aliases for Expressions and Calculated Values

An expression is a calculated or evaluated SQL value. It can contain arithmetic, a function call, text concatenation, a CASE expression, or an aggregate function. Expressions should usually have meaningful aliases because automatically generated headings can be difficult to read.

Arithmetic expressions

SELECT lName AS customer_last_name,
       AMOUNT * 1.10 AS amount_with_tax
FROM Customers;

The calculation remains an expression, but the result column is labeled amount_with_tax.

Concatenated text

Concatenation syntax differs among database products. For example, systems that support the || operator can label a combined name like this:

SELECT fName || ' ' || lName AS full_name
FROM Customers;

Use the text-concatenation function or operator supported by your database when writing portable application code.

CASE expressions

SELECT AMOUNT,
       CASE
         WHEN AMOUNT >= 1000 THEN 'Large'
         ELSE 'Standard'
       END AS order_size
FROM Customers;

The alias order_size identifies the category produced by the CASE expression.

Aggregate results

Aggregate functions summarize multiple rows. Giving their results clear names makes reports easier to read:

SELECT COUNTRY AS country,
       COUNT(*) AS customer_count
FROM Customers
GROUP BY COUNTRY;

Other functions, such as sums, averages, minimums, maximums, and date or text functions, can also use aliases:

SELECT SUM(AMOUNT) AS total_amount,
       AVG(AMOUNT) AS average_amount
FROM Customers;

Alias Scope and Query Processing

Alias visibility depends partly on the logical order in which SQL evaluates a query. Conceptually, filtering with WHERE happens before the SELECT list produces its named output columns. Consequently, a SELECT-list alias is generally unavailable to WHERE.

SELECT: The alias is created here and can label the output item.

WHERE: A SELECT-list alias is generally not available. Repeat the expression or calculate it in a subquery.

GROUP BY: Support for a SELECT-list alias varies by SQL product. Use the original expression for portability.

HAVING: Alias support varies by SQL product. Use the aggregate expression directly or filter an outer query.

ORDER BY: Most SQL products allow an output alias here.

Using an alias in ORDER BY

Sorting by an output alias is commonly supported:

SELECT fName AS first_name,
       lName AS last_name
FROM Customers
ORDER BY last_name;

This sorts by the selected last-name value while referring to its result heading.

Why an alias usually fails in WHERE

This pattern is generally invalid:

SELECT AMOUNT * 1.10 AS amount_with_tax
FROM Customers
WHERE amount_with_tax > 2000;

Use the original expression instead:

SELECT AMOUNT * 1.10 AS amount_with_tax
FROM Customers
WHERE AMOUNT * 1.10 > 2000;

Alternatively, calculate the value in a subquery and filter its named output:

SELECT amount_with_tax
FROM (
  SELECT AMOUNT * 1.10 AS amount_with_tax
  FROM Customers
) AS calculated
WHERE amount_with_tax > 2000;

A common table expression can provide the same separation when supported by the database.

GROUP BY and HAVING portability

Some products permit aliases in GROUP BY or HAVING, while others do not or apply different rules. For portable SQL, use the original column or expression:

SELECT COUNTRY,
       COUNT(*) AS customer_count
FROM Customers
GROUP BY COUNTRY
HAVING COUNT(*) > 5;

Another option is to place the grouped query in a subquery and apply an outer WHERE condition to the resulting alias.

Column Aliases Versus Table Aliases

A column alias labels an output column. A table alias gives a table reference a shorter temporary name inside a query. Table aliases do not rename the table in the database.

What is renamed: A column alias renames a result column; a table alias renames a table reference within the query.

Where it is used: A column alias is used mainly as an output label; a table alias prefixes qualified column references.

Primary purpose: Column aliases improve result readability; table aliases shorten references and clarify joins.

Typical syntax: column_name AS alias_name versus table_name AS table_alias.

Example using both kinds of aliases:

SELECT c.fName AS first_name,
       c.lName AS last_name,
       o.order_date AS order_date
FROM Customers AS c
JOIN Orders AS o
  ON o.customer_id = c.customer_id;

c and o are table aliases. The references c.fName and o.order_date identify which table supplies each column. The names first_name, last_name, and order_date are column aliases for the result.

Table aliases are especially useful in joins and self-joins, where the same table may appear more than once.

Dialect-Specific Quoting

Use identifier quoting when an alias contains spaces, special characters, mixed-case requirements, or a reserved word. The delimiter depends on the SQL product.

ANSI-style SQL and PostgreSQL: Double quotes; example AS "First Name". Double quotes are the standard-style choice for delimited identifiers.

MySQL: Backticks are commonly used; example AS `First Name`. Double quotes may depend on SQL mode and should be checked for the target configuration.

SQL Server: Square brackets are commonly used; example AS [First Name]. Double-quoted identifiers can also depend on database settings.

SQLite: Double quotes are the conventional identifier delimiter; example AS "First Name". Other forms may be accepted for compatibility, but standard-style quoting is clearer.

For portable SQL, prefer simple aliases such as first_name that do not require delimiters. When a display heading must contain spaces, use the documented identifier delimiter for the database that executes the query.

Alias Naming Practices

  • Choose concise, descriptive labels such as customer_count, total_amount, and amount_with_tax.
  • Use one consistent convention, such as lowercase snake case, for application-facing results.
  • Prefer aliases that explain what a calculated value means, not merely how it was calculated.
  • Avoid reserved words such as order, group, or user unless you deliberately delimit them.
  • Avoid ambiguous names such as value when a more precise label is available.
  • Use presentation-friendly labels for reports and stable, predictable names for API-facing query results.
  • Remember that aliases are not permanent schema names. Code that needs a lasting database rename must change the table definition separately.

Troubleshooting SQL Aliases

An alias in WHERE causes an error

Problem:

SELECT AMOUNT * 1.10 AS amount_with_tax
FROM Customers
WHERE amount_with_tax > 2000;

Cause: The WHERE clause generally cannot see a name created by the same SELECT list.

Resolution: Repeat the expression in WHERE, or calculate it in a subquery or common table expression before filtering.

An alias with spaces causes a syntax error

Problem:

SELECT fName AS First Name
FROM Customers;

Cause: The database parses the words as separate tokens.

Resolution: Delimit the identifier:

SELECT fName AS "First Name"
FROM Customers;

Single quotes behave unexpectedly

Problem:

SELECT fName AS 'First Name'
FROM Customers;

Cause: Single quotes represent string literals in standard SQL, and accepting them for aliases varies by database and configuration.

Resolution: Use double-quoted identifiers or the documented delimiter for the target SQL dialect.

An alias conflicts with a reserved word

Problem:

SELECT AMOUNT AS order
FROM Customers;

Cause: The database may interpret order as SQL syntax.

Resolution: Choose a non-reserved name such as order_amount, or delimit the alias according to the target database.

An aggregate alias is rejected in HAVING or GROUP BY

Problem:

SELECT COUNTRY,
       COUNT(*) AS customer_count
FROM Customers
GROUP BY COUNTRY
HAVING customer_count > 5;

Cause: Alias visibility in HAVING and GROUP BY differs among SQL products.

Resolution: Use HAVING COUNT(*) > 5, or place the aggregate query in a subquery and filter its output alias in an outer query.

Exam-Relevant Notes

  • AS introduces a temporary alias; it does not rename a stored table column.
  • A column alias labels a result-set column, while a table alias labels a table reference used inside the query.
  • Each selected column or expression can have a separate alias.
  • Aliases are commonly available in ORDER BY, but usually not in WHERE.
  • Alias support in GROUP BY and HAVING is database-dependent.
  • Use identifier delimiters for aliases containing spaces, and do not treat single quotes as portable identifier quoting.

Practice Queries

  1. Give fName and lName the output names first_name and last_name.

  2. Calculate AMOUNT * 1.10 and label it amount_with_tax.

  3. Count customers by country and label the aggregate customer_count.

  4. Sort a query by an output alias, then consider whether the same alias would be visible in WHERE.

  5. Join Customers and Orders using short table aliases, while assigning descriptive column aliases to the result.

For related fundamentals, review the SQL SELECT statement, SQL ORDER BY clause, SQL WHERE clause, SQL COUNT function, and SQL ALTER TABLE statement.