Why is the AS Clause Used in SQL?

Learn via video course
FREE
View all courses
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
Topics Covered

Why is the AS Clause used in SQL?

We use the AS clause in SQL to give a new name to a column or a table in a database. This name is known as an alias, and it is used to make the column or table name more readable. The alias exists only for the duration of the query. When multiple queries are to be made for a single table, we use an alias for that table to speed up the process.

The AS Clause helps to shorten the queries, thus improving code readability and understandability.

Why is the AS clause used in SQL?

The AS Clause in SQL is used for temporary renaming operations.

Now, you might wonder why shall we need to rename our tables or columns.

  • By temporarily renaming a column or table, we can get a better presentation of query results and create more user-friendly queries without permanently changing the name of the column or the table.
  • Giving short and concise names to the columns and tables is particularly useful when the queries become large and complex or contain many joins.
  • Working with long and incomprehensible table and column names can be extremely exhausting and time-consuming. In such cases, meaningful alias can speed up the work and improve code readability.
  • We can avoid retyping the long table and column names every time we have to access them by using short temporary names to suit our needs.

Where is the AS clause used in SQL?

The AS Clause is used along with the SELECT Statement in SQL.

We use the SELECT statement to select some data from our database. The AS Clause is also used to specify that we want to select some data (e.g., a column) as our alias name. This alias is the temporary name we assign to the data (column). This name will exist only for the duration of the query.

Let us see the syntax for AS Clause in SQL.

AS Clause for Column Syntax

AS Clause for Table Syntax

Examples for AS clause in SQL

We are creating a table called College_Students which stores the following data for each student - ID, Name, Branch, Country, City, and Age.

We use the CREATE TABLE command to create the table and the INSERT command to insert each row of student data.

The Table:

STUDENT_IDSTUDENT_NAMEENGINEERING_BRANCHCOUNTRYCITYSTUDENT_AGE
1SanyaCSEIndiaMeerut23
2AkashMechanicalIndiaNagpur21
3RakeshBiotechnologyIndiaKolkata22
4BuntyCivilBangladeshDhaka23
5RahulElectricalIndiaBangalore20
6SnigdhakumariMechanicalIndiaMumbai21
7KashishCivilIndiaJaipur23
8AnanyaComputer ScienceIndiaAhmedabad20

Let us now go through various examples demonstrating the use of AS Clause with the help of the above table.

EXAMPLE 1:

Write a query to get the student ID and the student name under the temporary column name 'ID' and 'Name', respectively.

Result:

IDName
1Sanya
2Akash
3Rakesh
4Bunty
5Rahul
6Snigdhakumari
7Kashish
8Ananya

Explanation:

Here we are creating an alias for two columns using AS Clause. In the result table, the column names now appear as the alias names, 'ID' and 'Name'.

EXAMPLE 2:

Write a query to get the City and the Country name under the temporary column name 'Address', along with each Student ID.

Let us see the result of the above SQL Query:

AddressID
Meerut, India1
Nagpur, India2
Kolkata, India3
Dhaka, Bangladesh4
Bangalore, India5
Mumbai, India6
Jaipur, India7
Ahmedabad, India8

Explanation:

Here we are appending the country and city for each student into a single column, whose alias is 'Address'.

EXAMPLE 3:

Write a query to get the name of all students as "Name" from an alias of the above table named "s"

Result:

Name
Sanya
Akash
Rakesh
Bunty
Rahul
Snigdhakumari
Kashish
Ananya

Explanation:

Here we are creating an alias of the above table named 's'. Then from 's', we select the column STUDENT_NAME under the alias 'Name'.

EXAMPLE 4:

Create another table that contains records of students who have backlogs. Write a query to get the data of students present in the above table who have their records in the Backlogs Table. Use Alias for Column and Table Names.

We create a table named Students_With_Backlogs to store the Student_ID, Student_Backlogs, and GPA of the students with backlogs.

Let us view the above table.

STUDENT_IDSTUDENT_BACKLOGSGPA
516
1225
725
5217
2334
1118
9217
1735
117

SQL Query:

Result:

namesfails
Sanya1
Rahul1
Kashish2

Explanation:

We are selecting those records from the Students_With_Backlogs (alias is b) table whose student id is also present in College_Students (alias is c) table. We are displaying the STUDENT_NAME as 'names', and STUDENT_BACKLOGS as 'fails' in our result table, using the AS Clause.

Learn more about Clauses in SQL

You can learn more about clauses in SQL from the below resource: SQL Query Execution Order

Conclusion

  • AS Clause is used in SQL to rename columns and tables.
  • AS Clause helps to shorten the queries and improves code readability.
  • The alias created by AS Clause exists only for the duration of the query.