What Does Keyword “AS” Do in SQL

1. Introduction

When working with SQL, clarity becomes paramount, especially as queries grow in complexity. The AS keyword is a small but mighty tool that significantly enhances the readability and manageability of your SQL code.

In this tutorial, we’ll explore what AS does in SQL queries and its various use cases.

We’ll run the queries on the Student table from the Baeldung University database, testing them on PostgreSQL 16, MySQL 8, and SQL Server 2022. However, they should work similarly in most modern databases.

2. Aliases in SQL

In SQL, an alias is a temporary name assigned to a column, table, or subquery for the duration of a query. Aliases can make query results more readable and complex SQL statements easier to manage.

The AS keyword is used to create an alias. It allows us to rename columns, tables, or subqueries in the query output without affecting the actual schema. While the AS keyword is optional for columns and tables, it makes aliases more explicit and may make them more readable.

2.1. Column Alias

Now, let’s look at how to use AS to create an alias for a column. Column aliases are used to rename the output of a column or an expression in the SELECT statement. This is especially useful when dealing with calculated fields or when we want more meaningful headers in the query result.

Let’s look at a sample query:

SELECT name, gpa AS grade_point_average
FROM Student;

When we run this query, we get the following (truncated) result:

+-----------------+---------------------+
|      name       | grade_point_average |
+-----------------+---------------------+
| John Liu        |                 4.0 |
| Rita Ora        |                 4.2 |
| Philip Lose     |                 3.8 |
| Samantha Prabhu |                 4.9 |
| Vikas Jain      |                 3.3 |
+-----------------+---------------------+

The column heading grade_point_average appears instead of gpa because we used an alias in the query. This makes the output clearer and easier to understand.

Aliases become especially helpful when working with calculated fields:

SELECT name, 
       graduation_date - enrollment_date AS study_duration_in_days
FROM Student;

In this query, we calculate how many days each student spent at the university, and the alias study_duration_in_days makes the result easy to understand.

Note that, in SQL Server, we’ll need to use the query as follows since the – operator is not supported:

SELECT name, 
       DATEDIFF(DAY, enrollment_date, graduation_date) AS study_duration_in_days
FROM Student;

2.2. Table Alias

Table aliases simplify query writing, especially when working with long table names or when joining multiple tables.

Let’s see what happens when we join the Student and Exam tables to find who has been given a grade:

SELECT s.name, e.course_id, e.grade
FROM Student AS s
JOIN Exam AS e ON s.id = e.student_id
WHERE e.grade IS NOT NULL;

In this query, by using table aliases, s for Student and e for Exam, we keep the query concise and readable.

While this is a relatively simple join, aliases become even more valuable in real-world queries that involve multiple tables or longer join conditions.

2.3. Subquery Alias

In SQL, a subquery alias is used to assign a name to a derived table, typically the result of a subquery in the FROM clause. This alias allows us to reference the result of the subquery as if it were a regular table.

Let’s look at a sample query to understand the subquery alias:

SELECT s.name,s.gpa
FROM
    Student AS s,
    (SELECT AVG(gpa) AS overall_avg_gpa FROM Student) AS avg_gpa_subquery
WHERE s.gpa > avg_gpa_subquery.overall_avg_gpa;

In this query, the inner subquery SELECT AVG(gpa) AS overall_avg_gpa FROM Student calculates the average GPA across all students. We assign it the alias avg_gpa_subquery, which allows us to refer to the result of the subquery – overall_avg_gpa – in the outer WHERE clause.

Although the AS keyword is optional when assigning an alias to a subquery, it makes the query more readable and explicit, particularly when dealing with complex subqueries or multiple derived tables. Subquery aliases help organize SQL logic more cleanly and are especially useful when working with aggregated data or intermediate results.

3. Conclusion

In this article, we explored the aliases in SQL using the AS keyword. Aliases let us rename columns, tables, or subqueries to make SQL queries clearer. The AS keyword is optional but helps make the aliases more explicit and easier to read. Using aliases, with or without AS, improves the clarity and maintainability of your SQL code.