Summary: in this tutorial, you will learn how to use SQL alias in the queries, including column alias and table alias.
Introduction to SQL alias
SQL supports two types of aliases: column alias and table alias.
- Column alias makes the result of queries more meaningful.
- Table alias helps avoid referring to the full table name, hence saving time typing queries. In addition, when a table is referred multiple times in a query, table alias is used to avoid ambiguous table name error.
Both kinds of SQL aliases can make your query more readable if you use them properly.
SQL Column Alias
When you query data from a table, the column names are used as headings of the output. However, most of the time, the column names are so technical that makes the result difficult to understand. To make the result of the queries more meaningful, you use SQL column alias.
You assign a column alias to a column in the SELECT clause using the AS keyword. Let’s take a look at the following example of using SQL column alias to reorganize the output.
SELECT productName AS product, unitPrice AS price FROM products WHERE unitPrice >50
In the SELECT statement above, we used two column aliases. The first column alias is product which represents productname column and the second one is price which represents the unitprice column.
It is important to notice that the AS keyword is optional. You can omit the AS keyword. If the column alias contains space, it must be wrapped inside double quotes. Let’s take a look a the following example:
SELECT productName product, unitPrice "unit price" FROM products WHERE unitPrice > 50
SQL Table Alias
In some cases, especially, when you use multiple tables that has the same column name, you have to refer to full table name in the query that may cause your query less readable. SQL provides table alias to allow you to assign a new name to the table in a query.
SQL table alias appears after the table name in the FROM clause of the SELECT statement. You often use SQL table alias when you refer to a table multiple times such as SQL self join or when the table name is too long that you don’t want to type its name.
For example, you can query organization structure to figure out who is manager of whom by joining employees table with itself as follows:
SELECT E.lastname "Employee name", M.lastname "Manager name" FROM employees E INNER JOIN employees M ON M.employeeID = E.ReportsTo
In above query, we referred to the same employees table twice therefore we had to use the table aliases: E for employee and M for manager.
When you use table alias, you have to refer to columns of the table as the following syntax to avoid ambiguous column name error.
In this tutorial, you have learned how to use SQL aliases including column alias and table alias.