MySQL online course

MySQL Aliases

Learn how to use MySQL column and table aliases with AS to create readable names for functions, calculations, aggregates, and query results.

A MySQL alias is a temporary alternate name assigned within a query. Aliases make result sets easier to read, especially when a query returns a calculation, function result, or aggregate expression.

An alias changes how a value is labeled in the query result. It does not permanently rename a database column or table and does not modify the database schema.

What Is a MySQL Alias?

A column alias is a temporary name assigned to a selected column, function result, calculated expression, or aggregate result. The alias becomes the heading for that value in the result set.

For example, without an alias, MySQL may display a calculation using an expression such as quantity * unit_price as its result heading. With an alias, the heading can simply be total_amount.

SELECT quantity * unit_price AS total_amount
FROM order_items;

The underlying columns remain quantity and unit_price. Only the returned result is labeled total_amount.

Aliases are not permanent renames

An alias exists only for the query result or the scope of the query where it is defined. It does not change column metadata, table definitions, indexes, constraints, or application schema.

Use an alias when you want a clearer output label. Use a schema-changing statement such as ALTER TABLE only when you intentionally want to permanently rename a database object.

Basic Alias Syntax

The general pattern is:

SELECT expression AS alias_name
FROM table_name;

An expression is any SQL construct that produces a value, including a column reference, function call, arithmetic calculation, or aggregate function.

AS is commonly used because it clearly separates an expression from its alias. MySQL also permits the alias without AS in many contexts:

SELECT quantity * unit_price total_amount
FROM order_items;

Although this form can work, using AS is recommended in teaching examples, shared queries, and production code because it is easier to read.

Aliasing Columns and Expressions

Column aliases

You can give a selected column a clearer output name:

SELECT first_name AS customer_first_name,
       last_name AS customer_last_name
FROM customers;

The source columns are unchanged. The result set uses customer_first_name and customer_last_name as headings.

Aliases for calculations

Calculated values should usually have descriptive aliases:

SELECT quantity,
       unit_price,
       quantity * unit_price AS total_amount
FROM order_items;

This is more useful to a person or application than exposing the raw arithmetic expression as a heading.

Aliases for aggregate results

An aggregate function summarizes multiple rows. Common aggregate functions include COUNT(), SUM(), AVG(), MIN(), and MAX().

SELECT COUNT(*) AS customer_count
FROM customers;

SELECT SUM(amount) AS total_revenue,
       AVG(amount) AS average_order_amount
FROM orders;

Aliases make exported reports, dashboards, and application result sets easier to understand.

Functions with Aliases

MySQL may use the function expression itself as the default result heading. For example:

SELECT CURDATE();

CURDATE() is a MySQL function that returns the current date. The default heading may appear as CURDATE(), which is technically accurate but less descriptive for a report.

Assign a clearer heading with an alias:

SELECT CURDATE() AS CurrentDate;

Now the returned value is labeled CurrentDate. This is particularly helpful when query output is read interactively or exported to a spreadsheet or reporting system.

You can apply the same pattern to other functions:

SELECT UPPER(first_name) AS uppercase_name,
       LENGTH(last_name) AS last_name_length
FROM customers;

Alias Naming and Quoting Rules

Simple aliases can use identifier-style names such as CurrentDate, total_sales, or customer_count.

• Use letters, numbers, and underscores for predictable names.
• Prefer descriptive but concise names.
• Avoid names that are likely to collide with existing columns or SQL keywords.
• Use a consistent convention, such as snake_case for application-facing data.

An alias containing spaces or special characters must be quoted. MySQL identifier quoting uses backticks:

SELECT SUM(amount) AS `Total Revenue`
FROM orders;

Backticks are the preferred style in this lesson because they clearly show that the text is an identifier used as an output label. MySQL can also accept string-style quoting for some alias forms, but mixing identifier and string quoting can be confusing. Use backticks consistently when a display label contains spaces.

For APIs and application code, names such as total_revenue are often easier to reference than `Total Revenue`. For human-readable reports, spaces and title-style labels may be appropriate.

Using SELECT Aliases in Later Clauses

A SELECT-list alias is created as part of the query output. MySQL generally allows that alias to be referenced by clauses that operate on the selected or grouped result, especially GROUP BY, HAVING, and ORDER BY.

Sorting by an alias

This query calculates a value, labels it, and sorts by that label:

SELECT quantity * unit_price AS total_amount
FROM order_items
ORDER BY total_amount DESC;

ORDER BY total_amount sorts the result using the calculated value represented by the alias.

Grouping and filtering aggregate aliases

You can use an aggregate alias in GROUP BY or HAVING in appropriate queries:

SELECT customer_id,
       SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 1000;

HAVING is used here because the condition applies to grouped results. For more information about summarizing rows, see MySQL aggregate functions.

Why WHERE usually cannot use a SELECT alias

Consider this query:

SELECT quantity * unit_price AS total_amount
FROM order_items
WHERE total_amount > 100;

It commonly produces an unknown-column error because WHERE is evaluated before the SELECT list creates total_amount. The alias does not exist yet at the point where the row filter is applied.

One solution is to repeat the original expression:

SELECT quantity * unit_price AS total_amount
FROM order_items
WHERE quantity * unit_price > 100;

Another solution is to place the aliased query in a derived table. A derived table is a subquery in the FROM clause whose output can be queried by an outer statement:

SELECT *
FROM (
    SELECT quantity * unit_price AS total_amount
    FROM order_items
) AS totals
WHERE total_amount > 100;

The outer query can use total_amount because it is now a column exposed by the derived table. A common table expression can serve a similar purpose in MySQL versions that support CTEs.

Reusing an alias in the same SELECT list

An alias is not a reusable variable for the next expression in the same SELECT list. This pattern is not reliable:

SELECT quantity * unit_price AS total_amount,
       total_amount * 0.10 AS tax_amount
FROM order_items;

Instead, repeat the expression:

SELECT quantity * unit_price AS total_amount,
       quantity * unit_price * 0.10 AS tax_amount
FROM order_items;

For a complex calculation, use a derived table or common table expression and calculate the second value in an outer query.

Column Aliases Versus Table Aliases

A column alias labels an output value. A table alias gives a table reference a temporary short name inside a query. These are different uses of aliasing.

Here, o is a table alias, while order_date is a column alias:

SELECT o.order_id,
       o.order_date AS order_date
FROM orders AS o;

Table aliases are especially useful when joining tables:

SELECT o.order_id,
       c.customer_name
FROM orders AS o
JOIN customers AS c
  ON c.customer_id = o.customer_id;

The table aliases o and c qualify column references. They do not rename the columns in the returned result unless a column alias is also specified.

Alias Scope, Ambiguity, and Limitations

• Aliases are temporary and do not change schema definitions.
• A SELECT-list alias generally cannot be used in WHERE.
• An alias cannot reliably be reused by another expression in the same SELECT list.
• An alias that matches an existing column name can create ambiguity or misleading clause references.
• Choose a distinct alias when an expression has the same name as a source column.

For example, an alias named amount may be confusing if the source table already has an amount column and the query also calculates another amount. A name such as discounted_amount communicates the meaning more clearly.

Readable Alias-Writing Practices

• Use aliases for aggregates and calculations instead of exposing raw expressions as headings.
• Choose names that describe the meaning of the value, such as total_spent or average_order_amount.
• Use snake_case for stable application-facing result names.
• Use readable title-style labels such as `Total Revenue` for human-facing reports when appropriate.
• Keep aliases concise enough to use comfortably in ORDER BY, GROUP BY, or HAVING.
• Use table aliases consistently in joins and qualify columns when names could be ambiguous.
• Prefer AS even where MySQL permits omitting it.

Troubleshooting Aliases

Raw expression appears as the heading

Problem: The result heading shows a function call or calculation.

Cause: No column alias was assigned.

Fix: Add AS and a descriptive name:

SELECT CURDATE() AS CurrentDate;

Syntax error with an alias containing spaces

Problem: An alias such as Total Revenue causes a syntax error.

Cause: The multiword alias was not quoted.

Fix: Use backticks:

SELECT SUM(amount) AS `Total Revenue`
FROM orders;

Unknown column in WHERE

Problem: MySQL reports an unknown column when a SELECT alias is used in WHERE.

Cause: WHERE filters rows before the SELECT alias is created.

Fix: Repeat the expression, use HAVING when filtering grouped results, or use a derived table.

Alias did not rename the database column

Problem: The table definition still contains the old column name.

Cause: A query alias changes only the result heading.

Fix: Keep the alias for output-only naming. Use a schema modification only when a permanent rename is intended.

Alias cannot be used by another SELECT expression

Problem: A second expression cannot find an alias created earlier in the SELECT list.

Cause: SELECT-list expressions are not sequential reusable variables.

Fix: Repeat the expression or move the first calculation into a subquery, derived table, or CTE.

Quick Reference

-- Function result
SELECT CURDATE() AS CurrentDate;

-- Calculated value
SELECT quantity * unit_price AS total_amount
FROM order_items;

-- Aggregate result
SELECT SUM(amount) AS total_revenue
FROM orders;

-- Report-friendly alias with spaces
SELECT SUM(amount) AS `Total Revenue`
FROM orders;

-- Sort by an alias
SELECT quantity * unit_price AS total_amount
FROM order_items
ORDER BY total_amount DESC;

-- Filter a grouped result with an aggregate alias
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 1000;

Aliases are a small SQL feature with a large readability benefit: they give calculated and summarized values names that people and applications can understand without changing the underlying database design.