There are cases when the column name or the table name that is existing in the database is not so human readable. We can use SQL ALIAS feature to assign a new name to the table or columns in our query.
SQL ALIAS is used for temporary naming of table or column of a table for making it more readable. The renaming is temporary in nature and does not affect the real name of the table or the column of the table.
The lifetime of the SQL ALIAS is till the duration of the query where SQL ALIAS is defined.
SQL ALIAS is more useful where multiple tables are involved like in case of JOINS.
We will discuss the usage of SQL ALIAS for table and column name of the table in detail in the below-mentioned sections.
SQL Alias For Table Name
Syntax:
|
1 |
SELECT column_name(s) FROM table_name AS alias_name; |
In the syntax above the alias_name is the name that will be temporarily assigned for the table_name.
Let’s try an understand in detail about aliasing a table name using the below-mentioned example.
We will consider the below mentioned Product and Supplier table for example purpose.
Product Table:
| ProductID | ProductName | SupplierID |
|---|---|---|
| 1 | Cookies | 2 |
| 2 | Jam | 2 |
| 3 | Butter | 1 |
| 4 | Bread | 3 |
| 5 | Cake | 1 |
Supplier Table:
| SupplierID | SupplierName |
|---|---|
| 1 | ABC Company |
| 2 | ACD Industries |
| 3 | XYZ Pvt Ltd |
Scenario: Get the name of all the products and their supplier name along with the productid.
SQL Select Query:
|
1 |
SELECT p.ProductID, p.ProductName, s.SupplierName FROM Product AS p, Supplier AS s WHERE p.SupplierID = s.SupplierID; |
| ProductID | ProductName | SupplierName |
|---|---|---|
| 1 | Cookies | ACD Industries |
| 2 | Jam | ACD Industries |
| 3 | Butter | ABC Company |
| 4 | Bread | XYZ Pvt Ltd |
| 5 | Cake | ABC Company |
In the output above SQL ALIAS, is used for aliasing the table name, making it easy to differentiate the two SupplierID columns of Product and Supplier table.
SQL Alias For Column Name
Syntax:
|
1 |
SELECT column_name AS alias_name FROM table_name; |
In the syntax above the alias_name is the name that will be temporarily assigned for the column_name.
Let’s try an understand in detail about aliasing a column name using the below-mentioned example.
Scenario: Get the name of all the products and their supplier name along with the productid. The ProductName column should be displayed as Product and SupplierName column should be displayed as Supplier.
Query:
|
1 |
SELECT p.ProductID, p.ProductName AS Product, s.SupplierName AS Supplier FROM Product AS p, Supplier AS s WHERE p.SupplierID = s.SupplierID; |
| ProductID | Product | Supplier |
|---|---|---|
| 1 | Cookies | ACD Industries |
| 2 | Jam | ACD Industries |
| 3 | Butter | ABC Company |
| 4 | Bread | XYZ Pvt Ltd |
| 5 | Cake | ABC Company |
In the example above we have seen the usage of alias for table and column both in a single query.