login

Subscribe to SQL Tutorial websiteRSS Feed
 » SQL Tutorial » SQL Alias

SQL Alias

Summary: In this tutorial, you will learn how to use different SQL alias including column alias and table alias with SQL SELECT statement.

SQL Alias is used to organize the output and avoid ambiguous table error in SQL statement when multiple tables with the same column names are refered to.

SQL supports two types of alias which are known as column alias and table alias.

SQL Column Alias

Basically when you query data from a database table, the column headings of the output are the same as the columns’ name of the table. However, the column's name heading is, most of the time, too technical that does not bring you any intuitive information therefore you want to change it in the output. SQL provides you SQL alias to allow you to do so. You can assign a column alias in the column list of the SELECT statement by using AS keyword.

Let’s take a look at several examples of using SQL column alias to organize the output.

SELECT productName AS product,
       unitPrice   AS price
FROM products
WHERE unitPrice >50

SQL Alias Example

In the SELECT statement above, we've used two column aliases. The first column alias is product which represents as productname and the second one is the price which represents as unitprice.

When we design a datatable table, we often name its columns as abbreviations. In this case, the column alias make the output more meaningful and easy to read.

It is important to note that the AS keyword is optional. You can omit the AS keyword in the column alias. If the column alias contains space it must be enclosed in double quotes. We can rewrite the above SELECT query as follows:

SELECT productName product,
       unitPrice "unit price"
FROM products
WHERE unitPrice > 50

SQL Table Alias

SQL table alias as its name implied is the new name of the table you refer to when specifying its columns. Table alias is put after the table name in FROM clause of SELECT statement. You often use SQL table alias when you refer a table multiple times in a SELECT statement with JOIN clause or when the table name is too long that you do want to save time typing its name.

For example you can query organization structure to figure out who is manager of whom by using SQL JOIN with table alias as follows:

SELECT E.lastname "Employee name",
       M.lastname "Manager name"
FROM employees E
INNER JOIN employees M ON M.employeeID = E.ReportsTo

SQL Table Alias Example

In above query, we referred to the same table employee.  In the FROM clause we use E for employee as table alias and in the INNER JOIN clause we M for manager as table alias.

When you use table alias, the column of the table has to be referred as syntax below to avoid ambiguous column name error.

table_alias.column_name

SQL alias is is very useful when using with SQL subqueries, SQL self join and SQL INNER JOIN statements.

In this tutorial, you've learned how to use SQL alias including column alias and table alias.

  • You use column alias to reorganize or reformat the output to make the output more meaningful.
  • You use table alias when you address a table multiple times in a single SQL SELECT statment to make it easier to write and maintain the lengthy query.
Related Tutorials