MySQL Aliases

Summary: in this tutorial, you will learn how to use MySQL aliases to assign temporary names to columns or tables in a query.

MySQL supports two kinds of aliases: column aliases and table aliases.

Column aliases

In MySQL, you use column aliases to assign a temporary name to a column in the query’s result set.

For example, column names sometimes are so technical that make the query’s output very difficult to understand. To give a column a descriptive name, you can use a column alias.

The following statement illustrates how to use the column alias:

SELECT [column_1 | expression] AS descriptive_name FROM table_name;Code language: SQL (Structured Query Language) (sql)

To assign a column an alias, you use the AS keyword followed by the alias. If the alias contains spaces, you must enclose it in quotes as follows:

SELECT [column_1 | expression] AS `descriptive name` FROM table_name;Code language: SQL (Structured Query Language) (sql)

Because the AS keyword is optional, you can omit it in the statement. Note that you can also assign an expression an alias.

Let’s look at the employees table in the sample database.

The following query selects the first names and last names of employees. It uses the CONCAT_WS() function to concatenate first name and last name into full name:

SELECT CONCAT_WS(', ', lastName, firstname) FROM employees;Code language: SQL (Structured Query Language) (sql)

Try It Out

The column heading can be challenging to read. To address this issue, you can assign a column alias to the output column, as shown in the following query:

SELECT CONCAT_WS(', ', lastName, firstname) AS `Full name` FROM employees;Code language: SQL (Structured Query Language) (sql)

Try It Out

In MySQL, you can use the column alias in the ORDER BY, GROUP BY and HAVING clauses to reference the column.

The following query uses the column alias in the ORDER BY clause to alphabetically sort the employee’s full names:

SELECT CONCAT_WS(', ', lastName, firstname) `Full name` FROM	employees ORDER BY `Full name`;Code language: SQL (Structured Query Language) (sql)

Try It Out

The following statement selects orders whose total amount is greater than 60000. It uses column aliases in GROUP BY and HAVING clauses.

SELECT	orderNumber `Order no.`, SUM(priceEach * quantityOrdered) Total FROM	orderdetails GROUP BY `Order no.` HAVING	total > 60000;Code language: SQL (Structured Query Language) (sql)

Try It Out

Notice that you cannot use a column alias in the WHERE clause. The reason is that when MySQL evaluates the WHERE clause, the values of columns specified in the SELECT clause have not been evaluated yet.

Table aliases

Similar to column aliases, you can assign a temporary name to a table in a query using a table alias.

Here’s the basic syntax of table alias:

table_name AS table_aliasCode language: SQL (Structured Query Language) (sql)

In this syntax, the AS keyword is optional, so you can choose to omit it.

This query shows how to assign an alias e to the employees table:

SELECT * FROM employees e;Code language: SQL (Structured Query Language) (sql)

Once you assign an alias to a table, you can reference a table column using the table alias like this:

table_alias.column_nameCode language: SQL (Structured Query Language) (sql)

For example:

SELECT e.firstName, e.lastName FROM employees e ORDER BY e.firstName;Code language: SQL (Structured Query Language) (sql)

In practice, you often use table aliases in statements that contain INNER JOIN, LEFT JOIN, RIGHT JOIN clauses or in subqueries.

See the customers and orders tables from the sample database:

Both tables customers and orders have the same column name: customerNumber.

If you reference the customerNumber column in a query, you will get an error message:

Error Code: 1052. Column 'customerNumber' in on clause is ambiguousCode language: SQL (Structured Query Language) (sql)

To avoid this error, you use a table alias to qualify the customerNumber column:

SELECT	customerName, COUNT(o.orderNumber) total FROM	customers c INNER JOIN orders o ON c.customerNumber = o.customerNumber GROUP BY	customerName ORDER BY	total DESC;Code language: SQL (Structured Query Language) (sql)

Try It Out

The query above selects the customer name and the number of orders from the customers and orders tables.

It uses c as a table alias for the customers table and o as a table alias for the orders table. The columns in the customers and orders tables are referred to via the table aliases.

If you do not use the alias in the query above, you have to use the table name to refer to its columns, which makes the query more verbose:

SELECT	customers.customerName, COUNT(orders.orderNumber) total FROM	customers INNER JOIN orders ON customers.customerNumber = orders.customerNumber GROUP BY	customerName ORDER BY	total DESCCode language: SQL (Structured Query Language) (sql)

Try It Out

Summary

• Use MySQL aliases to assign a column or a table a temporary name.
• Use a column alias to assign a temporary name to a column in a query.
• Use a table alias to assign a temporary name to a table in a query.