Alias in SQL

SQL Aliases are basically used as a short way to refer to a table, view, subquery or column. Use alias when you want to avoid repeating the long table names.

The general syntax is:

SELECT column_name AS alias_name
FROM table_name alias_name
WHERE condition

• Alias for table/view/subquery is meant to reduce the number of typing required, thus cleaner code
• Alias for column is meant for renaming the column headers for better readability

Example of SQL Alias

Instead of the below long query that keeps referring to the 2 table names

select
  users.id,
  users.first_name || ' ' || users.last_name,
  tbl_user_info.gender
from tbl_users, tbl_user_info
where tbl_users.id = tbl_user_info.user_id

It can be simplified as:

select
  U.id,
  U.first_name || ' ' || U.last_name AS full_name,
  I.gender
from tbl_users U, tbl_user_info I
where U.id = I.user_id

Alias for Subquery

In some language like MySQL, MariaDB PostgreSQL, when using subquery with FROM clause, it's compulsory for the subquery to have alias.

The below will throw error:

select count(1) from (
  select * from users
)

ERROR:  42601: subquery in FROM must have an alias

To fix it, simply alias the sub-query being used:

select count(1) from (
  select * from users
) S