SQL alias is used to organize the output. There are two types of alias: column alias and table alias.
Column alias
Basically when you retrieve data from a database table, the column headings of the output result are the same to the columns’ name of the database table. However, for some reasons, you want change the column’s names 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 column alias
SELECT productName AS product,
unitPrice AS price
FROM products
WHERE unitPrice >50
product price
------------------------ --------
Mishi Kobe Niku 97.0000
Carnarvon Tigers 62.5000
Sir Rodney's Marmalade 81.0000
Thüringer Rostbratwurst 123.7900
Côte de Blaye 263.5000
Manjimup Dried Apples 53.0000
Raclette Courdavault 55.0000
In the selection statement, we used two column aliases. The first column alias is product which represents for product name and the second alias is the price which represent for unit price. In case the column name is lengthy and sometimes it is designed as an abbreviation, the column alias enables the output more meaningful and easy to read.
Be noted that the AS keyword is optional and the alias can contain space but it has to be within quotes.
So you can rewrite the above query as follows:
SELECT productName product,
unitPrice "unit price"
FROM products
WHERE unitPrice > 50
Table Alias
Table alias is another name which you put after the table name of FROM clause of SELECT statement. Table alias is used when you refer a table multiple times in a SELECT statement with JOIN clause or when the table name is too long and you do want to make it easier to read and maintain.
For example you can retrieve organization structure to find 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
Employee name Manager name
------------- ------------
Davolio Fuller
Leverling Fuller
Peacock Fuller
Buchanan Fuller
Suyama Buchanan
King Buchanan
Callahan Fuller
Dodsworth Buchanan
In this tutorial, you've learn how to use SQL alias including column alias and table alias. You use column alias to reorganize or reformat the output and it make the output more meaningful. You use table alias when you address a table multiple times in a single SQL SELECT statment and you use it to make it easier to write and maintain the lengthy query.