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.
Search Keys
- sql alias
- alias in sql
- table alias sql
- sql update alias
- sql column alias
- sql join alias
- sql alias table
- sql server database alias
- sql alias column name
- sql alias example
- query alias
- sql alias name
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".