SQL Aliases

Summary: in this tutorial, you will learn how to use SQL aliases in the queries, including column aliases and table aliases.

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 to multiple times in a query, e.g., inner join, left join, or subquery, the table alias is used to avoid ambiguous table name errors.

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 make 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 ASkeyword.

Let’s 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 >50Code language: SQL (Structured Query Language) (sql)

In the SELECTstatement above, we used two column aliases. The first column alias is productthat represents the productnamecolumn, and the second one is  price that represents the unitpricecolumn.

Notice that the ASkeyword is optional therefore you can omit the ASkeyword. If the column alias contains spaces, it must be wrapped inside double quotes.

Let’s take a look the following example:

SELECT productName product,
       unitPrice "unit price"
FROM products
WHERE unitPrice > 50Code language: SQL (Structured Query Language) (sql)

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.

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

This makes your query less readable, especially when the table name is long.

SQL provides a table alias that assigns a new name to the table in a query. The table alias appears after the table name in the FROMclause 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 the 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.ReportsToCode language: SQL (Structured Query Language) (sql)

In the above query, we referred to the same employeestable twice, therefore, we had to use the table aliases:  E for employee and  M for manager.

We often use SQL aliases in the statements that use a subquery, self join and INNER JOIN clause.

In this tutorial, you have learned how to use SQL aliases including column aliases and table aliases in the query.