How to use aliases in SQL
Share
What are aliases?
The English word “alias” means an assumed or additional name that a person sometimes uses. This meaning is not very far from what it stands for in SQL.
Aliases in SQL are names that you can allocate to a table or table column for use in a single SQL query. In other words, an alias is a makeshift variable or temporary name for these tables and columns.
Usage
You can use an alias for the following scenarios:
- When the name of a particular table is long and there is a need to shorten it.
- When you wish to improve the readability of your query, as the table or column names might be ambiguous.
- A huge preference is given to the use of aliases in a query where multiple tables are involved, like in a
joinstatement, as it can prevent confusion.
Notes on aliases
- Aliases are created with the
"AS"keyword. - These aliases should be letters and not numbers.
- The new names do not affect the former names of the table and columns in the database.
- Aliases are temporary and are available in the statement block where they are created.
Syntax
SELECT column_name AS alias_name FROM table_name AS alias_name;
SELECTandFROM: query commands.column_name: the name of the table column to be renamed."AS": the keyword used to create aliases.alias_name: temporary alias name to be used to replace the original column name.table_name: the name of the table being queried. In the syntax above, we create an alias for both the column and table, and we can keep using these aliases in the query if it continues.
Examples
Below is a products table and a category table. cat_id is a foreign key in the products table, which is equal to the id column on the category table.
Products
id | Product_name | price | cat_id | product_no |
1 | Miler Table Fan | $300 | 2 | JH353 |
2 | Rollex Goldwatch | $1000 | 1 | 7858ty |
3 | Magic Blender | $50 | 2 | GT45Y |
4 | Rew Rotatable Chair | $200 | 3 | 456hu |
Category
id | cat_name |
1 | Fashion Accessories |
2 | Home Electronics |
3 | Office Accessories |
4 | Kitchen Accessories |
Let’s use aliases to query these tables.
Code
SELECT p.product_name, p.price, p.product_no, c.cat_nameFROM products AS p JOIN category AS cON p.cat_id = c.idWHERE c.cat_id = 2;
Imagine having to write out product.column_name or category.column_name for every column in the query; we will surely have a long SQL query block.
Upon successful execution, the output of the query will be as follows.
Query result
product_name | price | product_no | cat_name |
Miller Table Fan | $300 | JH353 | Home Electronics |
Magic Blender | $50 | GT45Y | Home Electronics |
