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.

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.

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.

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.

Alias Syntax

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

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.
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.
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.
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!