Aliasing in PostgreSQL?

PostgreSQLDatabaseData Storage

Often, we have some very long table names, and writing the table name every time is troublesome. We can use aliasing to help us there, thanks to which, we will need to write the long table name only once.

The table aliases are generally written in the FROM part of the statement, or the JOIN part.

For example, consider that we have two tables, marks, and student_info, defined respectively below −

marks

nameroll_noperc_marks
Aniket1224
Siddhi4565
Yash2642
Isha5687

student_info

nameroll_noagegender
Aniket1226M
Isha5625F
Siddhi4523F
Yash2625M

Now, if you want to see the name, roll_no, perc_marks, and age of the student in one query, your query will look like this −

SELECT marks.name, marks.roll_no, marks.perc_marks, student_info.age
FROM marks
LEFT JOIN student_info ON student_info.roll_no = marks.roll_no

The output will be

nameroll_noperc_marksage
Aniket122426
Siddhi456525
Yash264223
Isha568725

However, as you can see, this query is quite verbose. Now let us see how this query will look like, with aliasing.

SELECT a.name, a.roll_no, a.perc_marks, b.age
FROM marks a
LEFT JOIN student_info b ON b.roll_no = a.roll_no

As you can see, we had to write the name of the tables just once. Everywhere else, we used the table aliases, and it worked. The output will still be the same as above.

You can see that we have used the syntax ‘table_name alias’ in the above query. Another slightly more explanatory syntax is ‘table_name AS alias’. Thus, the above query can also be written as −

SELECT a.name, a.roll_no, a.perc_marks, b.age
FROM marks AS a
LEFT JOIN student_info AS b ON b.roll_no = a.roll_no

And it will give the same output.

nameroll_noperc_marksage
Aniket122426
Siddhi456525
Yash264223
Isha568725


raja
Published on 02-Feb-2021 13:05:15
Advertisements