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, e.g., inner join, left join or subquery, the 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 names of columns are used as the headings of the output. However, the column names are often so technical that makes the result set difficult to understand. They are often cryptic.
To make the result set more meaningful, you use SQL column alias. You assign a column alias to a column in the SELECT clause by using the
Let’s take a look at the following example of using SQL column alias to reorganize the output.
Code language: PHP (php)
SELECT productName AS product, unitPrice AS price FROM products WHERE unitPrice >50
SELECT statement above, we used two column aliases. The first column alias is
product that represents the
productname column, and the second one is
price that represents the
Notice that the
AS keyword is optional therefore you can omit the
AS keyword. If the column alias contains spaces, 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, when you use multiple tables that have the same column names in a query, you have to use both table name and column name to refer to the column to avoid the ambiguous column name error.
Code language: CSS (css)
This makes your query less readable, especially when the table name is long.
SQL provides table alias that assigns a new name to the table in a query. The table alias appears after the table name in the
FROM clause of the SELECT statement.
You often use table alias when you refer to a table multiple times e.g., in the SQL self join or when the table name is too long that you don’t want to type its name. For example, you can select the organization structure by joining employees table to itself as the following query:
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.
In this tutorial, you have learned how to use SQL alias including column alias and table alias in the query.