Alias Syntax

Pakhi Garg
Last Updated: May 13, 2022

Introduction

You might have kept the nicknames of your friends, or maybe you have one but have you ever heard a nickname or a temporary name of a table or column in SQL? Yes, you read it right. You can give a temporary name to a table or column using an alias. 

 

In this article, we will study aliases.

What is an alias?

You can temporarily rename a table or a column by giving another name to it. This other name is known as an alias. The scope of the alias given to a table or column lies until that specific SQL statement. Once the query is executed, the aliases defined are destroyed. Moreover, the original name of the table and column does not change in the database. This means if you have defined an alias of a table or column in a SQL statement, then that table or column will have two names - its original name and its temporary name, i.e., alias. The original name applies to all the SQL statements performed, but the alias applies only to that particular SQL statement.

Use of alias

  • Using aliases makes a table or column more readable.
  • Generally used with SQL queries that involve more than one table or column.
  • Also used when the table name or column name is very big or not very readable.

Syntax

  • Alias is created using the AS keyword.
  • If the alias name contains space, then the alias name is included in double-quotes.
     

The basic syntax of column alias is as follows:

SELECT column_name1 AS alias_name1, column_name2 AS alias_name2 FROM table_name WHERE [condition];

The basic syntax of table alias is as follows:

SELECT column1,column2.... FROM table_name1 AS alias_name1, table_name2 AS alias_name2 WHERE[condition];

Examples

Consider the following two tables:

Table 1: EMPLOYEE table

eid

emp_name

age

dept_id

salary

1001

Sushma

39

D2

50000

1002

Akshay

31

D3

60000

1003

Vaibhav

28

D2

50000

1004

Shreya

27

D1

80000

1005

Kavya

33

D1

60000

1006

Gaurav

30

D4

60000

1007

Surbhi

35

D5

40000

 

Table 2: DEPARTMENT table

dept_id

dept_name

D1

Computer Science

D2

Electronics

D3

Information Technology

D4

Mechanical

D5

Civil

 

The following examples will show the usage of column alias:

  • If the alias name does not contain space:
     

Below query fetches the data from two columns EID and EMP_NAME from EMPLOYEE table but we have aliased the name of EID as ID and EMP_NAME as NAME.

SELECT eid AS id, emp_name AS names FROM EMPLOYEE;

 

The above SQL query will give the following output:

id

names

1001

Sushma

1002

Akshay

1003

Vaibhav

1004

Shreya

1005

Kavya

1006

Gaurav

1007

Surbhi

 

  • If the alias name contains space:
     

Below query fetches dept_id and the count of employees in each department. The count of employees is aliased as “Total Employees”. 

Note: COUNT() is an aggregate function that returns the number of records in the table.

SELECT dept_id, COUNT(*) AS "Total Employees" FROM EMPLOYEE GROUP BY dept_id;

 

The above SQL query will give the following output:

dept_id

Total Employees

D1

2

D2

2

D3

1

D4

1

D5

1

 

Now, the following examples will show the usage of table alias: 

  • Aliasing single table:
     

Below query fetches data from two columns DEPT_ID and DEPT_NAME from DEPARTMENT table but we have defined an alias of DEPARTMENT table as D.

SELECT dept_id, dept_name FROM DEPARTMENT AS D;

 

The above SQL query will give the following output:

dept_id

dept_name

D1

Computer Science

D2

Electronics

D3

Information Technology

D4

Mechanical

D5

Civil

 

  • Aliasing multiple tables:
     

Below query fetches the data from three columns. EID and EMP_NAME from EMPLOYEE table, DEPT_NAME from DEPARTMENT table. We have defined alias for two tables EMPLOYEE AS E and DEPARTMENT AS D.

SELECT E.eid, E.emp_name, D.dept_name FROM EMPLOYEE E, DEPARTMENT D WHERE E.dept_id = D.dept_id; 

 

The above SQL query will give the following output:

eid

emp_name

dept_name

1004

Shreya

Computer Science

1005

Kavya

Computer Science

1001

Sushma

Electronics

1002

Vaibhav

Electronics

1003

Akshay

Information Technology

1006

Gaurav

Mechanical

1007

Surbhi

Civil

 

Frequently asked questions

  1. What is the need of column alias?
    Column alias is generally used to make column names in your result set more readable. It is generally used with aggregate functions like MIN, MAX, COUNT, SUM, AVG.
     
  2. What is the need for a table alias?
    Table alias is generally used to shorten the SQL query to make it more readable. It is often used while performing JOIN operations.
     
  3. Is an alias given to a table or column permanent?
    No, the scope of the alias is up to that query in which it is defined.
     
  4. How many aliases can we define for a table or column?
    You can define only one alias for a particular table name or column name in a query. You can use different aliases for the same table or column but in different queries.

Key Takeaways

In this article, we have briefly discussed aliases in SQL. We discussed the need and use of the alias. We can use alias in two ways- giving an alias to a column name and giving an alias to a table name. We went through the syntax of both and discussed their examples.

Reader, don’t stop here. Start your DBMS journey with DBMS course. Solve SQL problems here.

Happy Learning!

Was this article helpful ?
0 upvotes

Comments

No comments yet

Be the first to share what you think