Summary: in this tutorial, you will learn how to use MySQL alias to improve the readability of the queries.
MySQL supports two kinds of aliases which are known as column alias and table alias. Let’s examine each kind of alias in detail.
MySQL alias for columns
Sometimes the names of columns are so technical that make the query’s output difficult to understand. To give a column a descriptive name, you use a column alias. The following illustrates how to use the column alias:
SELECT [col1 | expression] AS `descriptive name` FROM table_name
To give a column an alias, you use the
AS keyword followed by the alias. If the alias contains space, you must quote the it as shown in the syntax. Because the
AS keyword is optional, you can omit it in the statement.
Note that you can also give an expression an alias.
Let’s look at the
employees table in the sample database.
The following query selects first names and last names of employees, and combine them to produce the full names. The
CONCAT_WS function is used to concatenate first name and last name.
SELECT CONCAT_WS(', ',lastName, firstname) FROM employees;
The column heading is quite difficult to read. You can assign the heading of the output a column alias to make it more readable as the following query:
SELECT CONCAT_WS(', ',lastName, firstname) AS `Full name` FROM employees;
The following query uses the column alias in the
ORDER BY clause to sort the employee’s full names alphabetically:
SELECT CONCAT_WS(', ',lastName, firstname) `Full name` FROM employees ORDER BY `Full name`;
The following statement selects the order whose total amount is greater than
60000. It uses column aliases in
GROUP BY and
SELECT orderNumber `Order no.`, SUM(priceEach * quantityOrdered) Total FROM orderDetails GROUP BY `Order no.` HAVING total > 60000;
MySQL alias for tables
An alias also gives a table a different name. You assign a table an alias by using the
AS keyword as the following syntax:
table_name AS table_alias
The alias for the table is called table alias. Like the column alias, the
AS keyword is optional so you can omit it.
Let’s look at the
See the following query:
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
The query above selects customer name and the number of orders from the
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
orders tables is referred via the table aliases.
If you do not use alias in the query above, you have to use the table name to refer to its columns, which makes the query lengthy and less readable.
In this tutorial, we have shown you how to use MySQL alias to make your query easy to read and simple to understand.