SQL SELECT AS – Alias in SQL

SQL SELECT AS is used to assign temporary names to table or column name or both. This is known as creating Alias in SQL. In this guide, we will learn what is an Alias and why it is used in SQL.

Why use Alias in SQL?

1. To reduce the amount of time to query by temporary replacing the complex & long table and column names with simple & short names.
2. This method is also used to protect the column names of the databases by not showing the real column names on the screen.
3. Alias are useful when we are working with JOIN operations or aggregate functions such as COUNT, SUM etc.

Alias Facts

1. An alias only temporary renames the column or table name, it lasts for the duration of select query. The changes to the names are not permanent.
2. This technique of creating alias is generally used by DBA (Database Administrators) or Database users.
3. The temporary table name is also called correlation name.

SQL Alias Syntax

Creating Alias for Column Syntax

SELECT column_name1 AS alias_name1, column_name2 AS alias_name2, ...
FROM table_name;

Creating Alias for Table Syntax

SELECT column_name1, column_name2, ...
FROM table_name AS alias_name;

SQL Alias Example

Table: STUDENT

STUDENT_ID    STUDENT_NAME   STUDENT_AGE     STUDENT_ADDRESS
----------    ------------   -----------     ---------------
1001           Negan           29             Noida
1002           Sirius          28             Delhi
1003           Ron             28             Delhi
1004           Luna            30             Agra

The following SQL statement creates three aliases, alias ID for STUDENT_ID column, alias NAME for STUDENT_NAME column and alias ADDRESS for STUDENT_ADDRESS column.

SELECT STUDENT_ID AS ID, STUDENT_NAME AS NAME, STUDENT_ADDRESS ADDRESS
FROM STUDENT;

Result:

ID             NAME          ADDRESS
-----          ------        -------
1001           Negan         Noida
1002           Sirius        Delhi
1003           Ron           Delhi
1004           Luna          Agra

SQL Alias example with aggregate functions

Table: CUSTOMER

CUSTOMER_ID    CUSTOMER_NAME    CUSTOMER_AGE     CUSTOMER_CITY
-----------    -------------    ------------     -------------
1001            Aditya           30               Noida
1002            Steve            39               Agra
1003            Carl             30               Noida
1004            Dinesh           33               Noida
1005            Lina             35               Agra

The following SQL statement will count the customers in the same city and display the count under temporary column “CustomerCount”, along with the alias City of CUSTOMER_CITY column. This statement also creates table alias “C” for the table CUSTOMER.

SELECT COUNT(CUSTOMER_ID) AS CustomerCount, C.CUSTOMER_CITY AS City
  FROM CUSTOMER C
GROUP BY C.CUSTOMER_CITY

Result:

CustomerCount    City
-------------    ----
    2            Agra
    3            Noida