SQL Alias

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 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 >50Code language: PHP (php)

In the 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 unitprice column.

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 > 50Code language: JavaScript (javascript)

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: 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.ReportsToCode language: JavaScript (javascript)

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.

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

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