The SELECT statement allows us to retrieve the data from one more database table. However, you might encounter such instances where you need to give the columns in the query with a different name or some sort of alias. For whatever reason, it is either for clarity or to perform a given calculation.
This is where the AS keyword comes into aid. It allows us to assign an alias to a column, a table, or an expression within an SQL query.
In this tutorial, we will dive into the world of SQL and learn about the AS keyword, why it exists, and how we can use it.
Syntax:
The SELECT AS clause allows us to assign the aliases to columns, tables, or expressions in your SQL query.
We can express its syntax as follows:
FROM table_name;
Here, the “column_name” refers to the name of the column that we want to select and the “alias_name” refers to the alias that we wish to assign to the selected column.
The best way to understand how this feature works is to use it. Therefore, let us look at some example usage of its application.
Example 1: Column Alias
The most common usage of the AS keyword is assigning a column with an alias. Suppose we have a table that contains the customer information with the “first_name” and “last_name” columns.
If you want to select the data from the table but use the “First Name” and “Last Name” aliases for the columns, we can use a query as follows:
FROM customer;
This should provide a different name for the resulting columns as demonstrated in the following example output:
We can also use the aliases in a calculation. For example, suppose we wish to calculate the yearly salary of all employees and output the resulting values as “Annual Salary” column. We can use the query as follows:
FROM employees;
In this example, we calculate the annual salary by multiplying the salary column by 12 and give it with an alias of “Annual Salary”.
Example 2: Table Aliases
The second use case of the AS keyword is setting and assigning the table aliases. Table aliases are quite useful as they come in handy when dealing with joins or even to make your queries more readable.
Consider the following example that demonstrates how to create a table alias using the AS keyword:
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.department_id;
In this case, we assign the “e” and “d” aliases to the “employees” and “departments” tables, respectively. This makes it very easy to reference the tables later in the query. This is very prevalent when dealing with SQL joins.
Example 3: Expression Aliases
Another use case of the AS keyword is to create the aliases for various expressions. This can help to simplify a complex expression or calculation.
SELECT CONCAT(first_name, ' ', last_name) AS "Full Name"
FROM employees;
This demonstrates how to assign an aliase for a “concat” function.
Example 4: Subquery Aliases
We can also create the aliases when dealing with subqueries. This can make the subqueries easier to reference and understand.
An example is as follows:
SELECT MAX(salary) FROM employees
) AS "Max Salary"
FROM employees;
In this example, we use a subquery to determine the maximum salary from the “employee” table and assign it with an alias of “Max Salary” in the main query.
Example 5: Aggregate Function Aliases
Lastly, we can use the aliases to the columns resulting from an aggregate function for a more readable output as demonstrated in the following:
FROM employees;
In this case, we assign the result of the AVG() function to the “Average Salary” alias.
Conclusion
In this tutorial, we learned about one of the fundamental features of SQL which allows us to create the aliases for various objects such as tables, columns, expressions, subqueries, etc. This can help to improve the query readability and provide a clarity for the resulting output.
