SQL Alias | AS Keyword


The SQL Aliases are used to temporarily rename a column name or a table name with user friendly heading.

The SQL aliases are created to make column or table names more readable.

The use of aliases means to rename a table or column in a particular SQL statement will not affect to that database table or column.


Use Of Sql Alias

Sql Aliases can be very useful for table name when:

  • There are more than one table used in a SQL statement.

Sql Aliases can be very useful for column name when:

  • Column names are too big or not easy readable.
  • SQL Functions are used in the SQL statement with column names like SUM(), COUNT(), or AVG().
  • Two or more columns are combined together as a single column in the SQL statement.

Sql Alias | AS Syntax

The below syntax is used to create aliases for column name.


SELECT column_name1 AS column_alias_name1
FROM table_name;

The below syntax is used to create aliases for table name.


SELECT column_name1, column_name2
FROM table_name AS table_alias_name;

Note:

  • If the table or column alias_name contains spaces, you must enclose the alias_name with single or double quotes.
  • The column alias_name is only valid within the scope of the SQL SELECT statement and It cannot be used in SQL WHERE clause for conditions.

Sample Database Table - Employee

ID FName LName Gender Age City State
777 Azagu Murugan Male 21 Madurai TN
888 Azagu Varshith Female 26 Bangalore KN
111 Suresh Babu Male 32 Nasik MH
222 Siva Kumar Male 22 Chennai TN
333 Bala Murugan Male 33 Nasik MH
444 Bala Karthik Male 20 Madurai TN
555 Haris Karthik Male 25 Bangalore KN
666 Varshini Kutty Female 28 Madurai TN

Sample Database Table - Employee_Official

ID JoinDate Designation Technology Salary
777 23 July 2015 Programmer MySql 15000
888 20 March 2014 Administrator Oracle 25000
111 03 October 2014 Programmer Sql Server 25000
222 17 December 2014 Administrator Sql Server 18000
333 15 April 2015 Programmer MySql 20000
444 10 September 2014 Programmer MySql 17000
555 21 May 2015 Administrator Sql Server 26000
666 18 September 2014 Programmer Oracle 25000

SQL Alias | AS Example for Column Names

The following SQL statement specifies two aliase names, one for the "FName" column and another for the "LName" column, in the "Employee" table.

Tip: It requires single or double quotation marks or square brackets if the alias name contains spaces:


SELECT 
FName As FirstName, 
LName As 'Last Name' 
FROM Employee;

The result of above query is:

FirstName Last Name
Azagu Murugan
Azagu Varshith
Suresh Babu
Siva Kumar
Bala Murugan
Bala Karthik
Haris Karthik
Varshini Kutty

In the following SQL statement we combine two columns (FName and LName) and create an alias named "FullName", in the "Employee" table:


SELECT 
FName + " " + LName As FullName 
FROM Employee;

The result of above query is:

FullName
Azagu Murugan
Azagu Varshith
Suresh Babu
Siva Kumar
Bala Murugan
Bala Karthik
Haris Karthik
Varshini Kutty

Note: To get the SQL statement above to work in MySQL use the following:


SELECT 
CONCAT(FName, " " , LName) As FullName 
FROM Employee;


SQL Alias | AS Example for Table Names

Table alias name mainly used, when selecting columns from multiple tables.

The following SQL statement selects all(ID, FName, LName, Salary) the records from the "Employee" and "Employee_Official" tables.

We use the "Employee" and "Employee_Offical" tables, and give them the table aliases of "e" and "o" respectively:


SELECT 
e.ID, e.FName + " " + e.LName As Name, o.Salary 
FROM Employee As e, Employee_Official o 
WHERE e.ID = o.ID;

The result of above query is:

ID Name Salary
777 Azagu Murugan 15000
888 Azagu Varshith 25000
111 Suresh Babu 25000
222 Siva Kumar 18000
333 Bala Murugan 20000
444 Bala Karthik 17000
555 Haris Karthik 26000
666 Varshini Kutty 25000

Note:- There must be a common columns while selecting columns from multiple tables. In the "Employee" and "Employee_Official" both tables contains a common column called "ID".