VMware ESXi and vSphere Cluster Management

MySQL Aliases

Learn how to use MySQL column and table aliases with AS, function results, joins, self-joins, quoting rules, and alias scope.

An alias is a temporary name assigned to a column, expression, or table for the duration of a query. Aliases make SQL easier to read and give returned columns clearer headings.

An alias does not permanently rename a database object. It changes how a column or table is referenced in one query, or how a selected value is labeled in that query's result set.

Column Aliases

A column alias is a temporary label for a selected column, calculated expression, or function result. The general syntax is:

SELECT expression AS alias_name
FROM table_name;

The alias changes the result-column heading. It does not change the stored column name or the table definition.

Aliasing an ordinary column

SELECT customer_name AS Name
FROM customers;

The returned column is labeled Name, even though the stored field is still called customer_name.

Aliasing a calculated expression

An expression is a value MySQL evaluates, such as a calculation, operator expression, or function call.

SELECT quantity * unit_price AS TotalAmount
FROM order_items;

This produces a readable heading, TotalAmount, for the calculated value.

Using the AS keyword

AS introduces an alias and makes the intent obvious:

SELECT expression AS alias_name
FROM table_name;

In many column-alias contexts, AS is optional:

SELECT expression alias_name
FROM table_name;

Both forms can work, but using AS generally improves readability and reduces confusion between an expression and its label.

Aliasing Function Results

Without an alias, MySQL may use the function call or expression as the result heading. For example:

SELECT CURDATE();

The heading may appear as CURDATE(). A descriptive alias makes the result easier to understand:

SELECT CURDATE() AS CurrentDate;

The returned value is the same current date, but the result-column heading is now CurrentDate.

Aliases are useful for other function results as well:

SELECT COUNT(*) AS OrderCount,
       AVG(unit_price) AS AveragePrice
FROM order_items;

Alias Types

Alias type: Column alias

What it names: A selected column, expression, or function result

Typical location: The SELECT list

Main purpose: Clear result headings and readable calculations

Example: quantity * unit_price AS TotalAmount

Alias type: Table alias

What it names: A table reference used by the query

Typical location: The FROM or JOIN clause

Main purpose: Shorter qualified references and unambiguous joins

Example: orders AS o

Alias Naming and Quoting

Simple identifier-style aliases contain letters, digits, or underscores and are easy to use without quoting:

SELECT customer_name AS CustomerName,
       quantity * unit_price AS TotalAmount
FROM order_items;

An alias containing spaces or punctuation needs appropriate quoting. In MySQL, backticks quote an identifier-style alias:

SELECT customer_name AS `Customer Name`
FROM customers;

The result heading is displayed as Customer Name, including the space. Backticks are especially useful when an alias contains spaces, reserved words, or special characters.

Quoted aliases are display labels, not new stored columns. The backticks are SQL syntax and are not normally part of the returned heading.

Desired alias: TotalAmount

Valid syntax pattern: expression AS TotalAmount

When quoting is needed: Usually not needed

Notes: Simple and convenient for later references such as ORDER BY TotalAmount

Desired alias: Customer Name

Valid syntax pattern: customer_name AS `Customer Name`

When quoting is needed: Needed because the alias contains a space

Notes: Produces a presentation-friendly heading

Desired alias: Order#

Valid syntax pattern: order_id AS `Order#`

When quoting is needed: Needed because the alias contains punctuation

Notes: Backticks make the intended alias unambiguous

MySQL also has rules involving quoted strings and SQL modes. For consistent SQL style, use backticks when quoting an alias as an identifier, and avoid relying on ambiguous single-quote behavior for column labels.

Table Aliases

A table alias is a short temporary name assigned to a table reference. It is defined in the FROM or JOIN clause:

SELECT c.customer_name
FROM customers AS c;

After assigning c as the table alias, qualify columns with c. A qualified column name includes a table name or alias, such as c.customer_name.

Table aliases are useful because they:

  • Reduce repeated, fully qualified table names.
  • Make long joins easier to read.
  • Identify which table supplies each column.
  • Resolve ambiguity when multiple tables contain columns with the same name.
  • Allow the same table to appear more than once in a query.

When a table has an alias, use that alias for qualified references throughout the query. For example, this query consistently uses c:

SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE c.customer_id > 100;

Do not assign c and then continue qualifying columns with customers in the same query block.

Aliases in Joins

Aliases make joins shorter and help avoid ambiguous column errors. For example, both customers and orders may contain a customer_id column:

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

The references c.customer_id and o.customer_id tell MySQL exactly which table each column belongs to. This is clearer than leaving a shared column name unqualified.

Self-Joins

A self-join joins a table to another reference of the same table. Each reference needs its own alias so MySQL can distinguish their roles.

Suppose an employees table contains employee_id, employee_name, and manager_id. The following query displays each employee and that employee's manager:

SELECT employee.employee_name AS Employee,
       manager.employee_name AS Manager
FROM employees AS employee
LEFT JOIN employees AS manager
  ON manager.employee_id = employee.manager_id;

Here, employee and manager are two aliases for the same table. They identify different roles played by two table instances. Every relevant column is qualified so the query does not confuse an employee's ID with a manager's ID.

Alias Scope and Limitations

Aliases are scoped to the query in which they are defined. They do not create permanent database names, and they are not automatically available to a separate query.

Using a SELECT alias in ORDER BY

A SELECT-list alias is commonly available to ORDER BY:

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

This avoids repeating the calculation in the sorting clause.

Using a SELECT alias in WHERE

A SELECT-list alias is generally not available in WHERE:

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

This fails because the WHERE filter is logically evaluated before the SELECT list assigns the alias. Repeat the expression when appropriate:

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

For a more complex calculation, calculate the alias in a derived table and filter from an outer query:

SELECT item.TotalAmount
FROM (
    SELECT quantity * unit_price AS TotalAmount
    FROM order_items
) AS item
WHERE item.TotalAmount > 100;

GROUP BY and HAVING

MySQL permits SELECT aliases in some GROUP BY and HAVING queries. For example:

SELECT customer_id,
       SUM(unit_price * quantity) AS TotalAmount
FROM order_items
GROUP BY customer_id
HAVING TotalAmount > 1000;

Although this is convenient in MySQL, alias visibility in grouping and filtering clauses can vary between SQL systems and query forms. For portable SQL, prefer repeating the expression when practical, or calculate it in a derived table or common table expression before applying the outer filter.

Clause: ORDER BY

Can use SELECT alias? Commonly yes

MySQL-specific notes: MySQL supports ordering by a SELECT-list alias

Portable guidance: Usually portable and convenient

Clause: WHERE

Can use SELECT alias? Generally no

MySQL-specific notes: The WHERE condition is evaluated before the SELECT alias is assigned

Portable guidance: Repeat the expression or use an outer query

Clause: GROUP BY

Can use SELECT alias? MySQL commonly permits it

MySQL-specific notes: Behavior depends on the query and SQL features being used

Portable guidance: Check the target database; repeating the expression is safer

Clause: HAVING

Can use SELECT alias? MySQL commonly permits it

MySQL-specific notes: Aggregate aliases are often usable in HAVING

Portable guidance: Verify portability or filter from an outer query

Logical Query Processing and Aliases

The written order of SQL clauses is not the same as the logical order in which a query is processed. A simplified order is:

  1. FROM and JOIN identify source rows.
  2. WHERE filters those rows.
  3. GROUP BY forms groups.
  4. Aggregate calculations are evaluated where applicable.
  5. HAVING filters groups.
  6. SELECT produces the output columns and assigns column aliases.
  7. ORDER BY sorts the result.

This order explains why an alias created in SELECT is normally available to ORDER BY but not to WHERE.

Troubleshooting Alias Errors

Unknown column in WHERE

Problem: A calculated alias is reported as an unknown column in WHERE.

Cause: The filter runs before the SELECT list assigns the alias.

Fix: Repeat the expression in WHERE, or calculate it in a derived table or common table expression and filter in the outer query.

Query fails after adding a table alias

Problem: A query fails after a table receives an alias.

Cause: The query still uses the original table name in qualified references.

Fix: Use the assigned table alias consistently in that query block.

Ambiguous column error

Problem: MySQL reports an ambiguous column.

Cause: More than one joined table contains the referenced column name.

Fix: Prefix the column with the relevant alias, such as o.order_id or c.customer_id.

Spaces or punctuation in an alias

Problem: An alias containing spaces causes a syntax error or displays unexpectedly.

Cause: The alias was not quoted appropriately, or it was written in a form interpreted differently from the intended label.

Fix: Use MySQL-compatible backtick quoting, such as AS `Customer Name`, and follow a consistent project style.

Self-join roles are unclear

Problem: A self-join cannot distinguish employees from managers.

Cause: Both references use the same table without separate aliases.

Fix: Assign a unique alias to each table reference and qualify every relevant column.

Practical Alias Checklist

  • Use a column alias when a result heading needs to be clearer.
  • Use AS to make column aliases easy to identify.
  • Alias calculations and function results, not only stored columns.
  • Use simple names when possible; quote aliases containing spaces or punctuation.
  • Use short table aliases in joins, especially when column names repeat.
  • Qualify shared column names to prevent ambiguity.
  • Give each reference a different alias in a self-join.
  • Remember that aliases are temporary and query-scoped.
  • Use SELECT aliases in ORDER BY when supported, but do not normally expect them in WHERE.
  • Consider a derived table or common table expression when an expression must be filtered after it is calculated.

For related material, see MySQL aliases.