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:

SELECT COLUMN_A AS alias [, COLUMN_B AS anotherAlias]
FROM tableA ASSOME alias” [, tableB AS anotherAlias ]    
[WHERE condition ]

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

CREATE TABLE developers (
d_id INT PRIMARY KEY,
d_name TEXT NOT NULL,
d_age INT NOT NULL,
d_address CHAR(50),
d_salary INT NOT NULL
);  

INSERT INTO developers (
d_id,
d_name,
d_age,
d_address,
d_salary
)
VALUES
(0,'JOHN CONNER',19,'23 BOULEVARD ST.',20000),
(1,'ADAM SMITH',21,'28 BOULEVARD ST.',25000),
(2,'BLAKE JONES',23,'22 BOULEVARD ST.',30000),
(3,'BRIDGETTE JOHNSON',18,'21 BOULEVARD ST.',40000),
(4,'CMAILIA BLACK',20,'27 BOULEVARD ST.',35000),
(5,'AMY WINEHOUSE',20,'23 BOULEVARD ST.',20600),
(6,'CHRIS JONES',23,'01 BOULEVARD ST.',37000);

CREATE TABLE programs (
p_id INT PRIMARY KEY,
p_date TEXT NOT NULL,
d_id INT NOT NULL,
p_value INT NOT NULL,
FOREIGN KEY f_key (d_id) referances developers(d_id)
);  

INSERT INTO programs (
p_id,
p_date,
d_id,
p_value
)
VALUES
(0,'09-01-2018',5, 3000),
(1,'11-02-2019',1, 2110),
(2,'20-03-2015',0, 4500),
(3,'25-03-2019',6, 1010),
(4,'26-12-2018',0, 350);

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

Image from Gyazo

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

SELECT COUNT(*) AS “Total NUMBER OF developers”
FROM developers;

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

Image from Gyazo   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

SELECT d_name AS “Developer’s Name”, d_salary AS “Salary”
FROM developers;

Image from Gyazo

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:

SELECT d_name AS “Developer’s Name”, p_id AS ‘Program ID’, p_value AS ‘Revenue FROM the Program’
FROM developers AS d, programs AS p
WHERE d.d_id = p.d_id;

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:

Image from Gyazo

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.