SQL - ALIAS Syntax


Advertisements

Previous Page Next Page

The SQL ALIAS statement is used to provide a temporary name to a column of a table, aggregate function column or a table itself. A alias name exists for only for the duration of the query. It facilitates easy to understand names and hence increases readability of a SQL code.

Syntax

The syntax for using SQL ALIAS statement is given below:

/* Using ALIAS with a column of a table */
SELECT column_name AS alias_name
FROM table_name;

/* Using ALIAS with an aggregate function */
SELECT SUM(column_name) AS alias_name
FROM table_name
WHERE condition(s);

/* Using ALIAS with a table */
SELECT alias_name_1.column1, alias_name_2.column1, ...
FROM table1 AS alias_name_1
INNER JOIN table2 AS alias_name_2
ON alias_name_1.matching_column = alias_name_2.matching_column;

Example:

Consider a database tables called Employee and Contact_Info with the following records:

Table 1: Employee table

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

Table 2: Contact_Info table

Phone_NumberEmpIDAddress
+1-80XXXXX0002XXX, Brooklyn, New York, USA
+33-14XXXXX013XXX, Grenelle, Paris, France
+31-20XXXXX194XXX, Geuzenveld, Amsterdam, Netherlands
+86-10XXXXX4586XXX, Yizhuangzhen, Beijing, China
+65-67XXXXX47XXX, Yishun, Singapore
+81-35XXXXX728XXX, Koto City, Tokyo, Japan

  • ALIAS with a column: The below SQL code is used to create a aliases of columns - name and City of the Employee table.

    SELECT Name AS [Employee Name], Age AS [Employee Age], City 
    FROM Employee;
    

    This will produce the result as shown below:

    Employee NameEmployee AgeCity
    John25London
    Marry24New York
    Jo27Paris
    Kim30Amsterdam
    Ramesh28New Delhi
    Huang28Beijing

  • ALIAS with an aggregate function: The below SQL code is used to fetch the number of employees whose age is greater than 27.

    SELECT COUNT(Name) AS [Number of Employees]
    FROM Employee
    WHERE Age > 27;
    

    This will bring the following result:

    Number of Employees
    3

  • ALIAS with a table: To inner join Employee and Contact_Info tables based on matching column EmpID, the SQL code is given below. While performing inner join of two tables, aliases of tables are used.

    SELECT A.Name, A.Age, B.Address 
    FROM Employee AS A
    FULL JOIN Contact_Info AS B
    ON A.EmpID = B.EmpID;
    

    The result of above SQL code will be:

    NameAgeAddress
    Marry24XXX, Brooklyn, New York, USA
    Jo27XXX, Grenelle, Paris, France
    Kim30XXX, Geuzenveld, Amsterdam, Netherlands
    Huang28XXX, Yizhuangzhen, Beijing, China

Previous Page Next Page
Advertisements