SQL SELECT AS

Introduction

In this article, we will be discussing the SQL SELECT AS clause in SQL databases but with PostgreSQL in particular. Before getting in the details, let’s just explain briefly what exactly are clauses in an SQL database.

Clauses

Clauses in the SQL are keywords that have definite meaning (similar to their English language counterpart) and purpose. The purpose is self-explanatory by the name of the clause. For example, the WHERE clause is used to query a specific condition. LIKE clause is used for string text comparison on a specific pattern. EXCEPT clause is used to limit the search results of a query on a specific condition. etc.

Some clauses in SQL can only be used with another clause and they usually enhance the functionality of the clause they are being used with. For example, the HAVING clause can only be used with a group by clause and it restricts the results of the group by clause on a condition. The FROM clause is mandatory with the SELECT statement to specify from which table the data needs to be selected.

AS clause

In the English language, one of the uses of the word ‘as’ is aliasing. We use ‘as’ to represent an alias for the thing mentioned before as. For example, ‘coal is known as black gold’ or ‘The king of Egypt is known as pharaoh’. In both of these statements, we have assigned aliases to “coal” and “the king of Egypt” which are “black gold” and “pharaoh” respectively. Now if we say black gold, it is known that we are referring to coal or if someone says pharaoh, we know that he is referring to the king of Egypt.

In SQL, the AS clause is used for the same purpose. AS is used to assign an alias to a column or a table. The general syntax is:

Each column for which an alias is defined is returned in the result with the header as its alias rather than the column name defined in the table. Each table for which an alias is defined can then be called using its alias in that query. As sown in the syntax, if an alias contains spaces, we need to enclose it between double-quotes. Enclosing the alias between the square bracket also works.

One thing to keep in mind here is that aliasing only works for the query in which it is used. So the scope is limited. When we are assigning an alias to a table or a column, we are not changing its name in the database. We are just using a word to refer to that table or column in only that query. So the scope of aliasing is limited to the query in which it is used.

Consider the following example

When the above code is executed, we get the following tables:

Now if we want to know how many developers are on the payroll of the company:

We can now see that the answer is returned with the header “Total number of developers”.

  Let’s take another example. We have to know all the developer names and their salaries in our database and show them to the owner. Now the column names, such as “d_name” and “d_salary” make sense to the developer but they are not so presentable. So what we do is

Now the returned result is much more presentable and makes sense to a user having no knowledge of the internal database. Now it makes sense to us why we assign aliases to columns, but why do we need aliases for tables? Suppose we need to find the developers who have worked on some projects and its details. Consider the following query:

Here we are easily able to distinguish between two columns having the same name in two different tables and we did not have to use the full name of the table to do that. We only need a single letter and we were able to refer to the original table. This query when executed gives the following result:

Conclusion

AS clause is useful for aliasing. We can assign an alias to a column to make our result more presentable or we can assign an alias to a table so that we can refer to it using an alias. Aliases have limited scope and only work in the same query they are defined and they do not affect the original table in the database.