Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql
communitycreator

How to use aliases in SQL

NDUKWE CHIDERA K.

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 join statement, 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;
  • SELECT and FROM: 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_name 
FROM products AS p JOIN category AS c 
ON p.cat_id = c.id 
WHERE 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

RELATED TAGS

sql
communitycreator
RELATED COURSES

View all Courses

Keep Exploring