Menu

MySQL Aliases: Column, Table, and Derived Table

Learn MySQL column, table, and derived-table aliases, including where column aliases can be used and why derived tables need an alias.

Updated on

A MySQL alias gives a name to a selected value or a table reference. Column aliases make result columns easier to read; table aliases shorten qualified names and distinguish tables in joins. A subquery in the FROM clause also needs a table alias.

Column aliases

Use AS to name a column or expression in the result. AS is optional in MySQL, but writing it makes the boundary between an expression and its alias clear.

SELECT
    actor_id,
    CONCAT(first_name, ' ', last_name) AS full_name
FROM actor
ORDER BY full_name
LIMIT 5;

The result column is named full_name. You can also use an alias in GROUP BY, ORDER BY, and HAVING:

SELECT customer_id, SUM(amount) AS total_amount
FROM payment
GROUP BY customer_id
HAVING total_amount > 100
ORDER BY total_amount DESC
LIMIT 5;

Do not use a select-list alias in the same query block’s WHERE clause. WHERE filters input rows before the select-list expression is calculated. To filter an aggregate such as SUM(amount), use HAVING; to filter a nonaggregate expression, repeat it in WHERE or place the query in a derived table.

If an alias contains spaces or special characters, quote it as an identifier with backticks:

SELECT CONCAT(first_name, ' ', last_name) AS `Full Name`
FROM actor;

Use backticks when referring to a quoted alias in another clause. Single quotes are string-literal quotes, so avoid using them to refer to an alias outside the select list.

Table aliases

A table alias is declared in the FROM clause. AS is optional:

SELECT a.actor_id, a.first_name, a.last_name
FROM actor AS a
WHERE EXISTS (
    SELECT 1
    FROM film_actor AS fa
    WHERE fa.actor_id = a.actor_id
);

Here a refers to actor, and fa refers to film_actor. The a.actor_id reference inside the subquery points to the row from the outer query. Once a table has an alias in a query block, use that alias to qualify its columns in that block.

Derived-table aliases

A derived table is a subquery in the FROM clause. MySQL requires every derived table to have an alias; the outer query uses that alias to qualify the derived table’s columns.

SELECT totals.customer_id, totals.total_amount
FROM (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM payment
    GROUP BY customer_id
) AS totals
WHERE totals.total_amount > 100
ORDER BY totals.total_amount DESC
LIMIT 5;

The subquery produces the derived table totals. Its output column names come from the subquery’s select list, including total_amount.

Further reading