SQL Alias

Submitted by admin on Saturday, March 19, 2011 - 13:18.

As in normal life we associate some nicknames with people, or call them with names that are not there actual names SQL also allows us to do the same with the table columns, since it is much easier to memorize, view as it is in real life. Now the point where SQL ALIAS becomes important is that whenever we have a table which has complex column names we can easily rename the column understand it easily.

NOTE that SQL Alias will not change the actual name of the column in the table.

The SQL ALIAS can be used on any column of any table, but cannot use the reserved word.

SQL ALIAS Syntax for Columns

SELECT column_name(s) AS ALIAS_NAME FROM TABLE_NAME

Consider the following table for this exercise

Users

Firstname	Lastname	Salary	DeptNumber
John	Smith	1000	1
Mathew	Simon	3000	1
Bill	Steve	2200	1
Amanda	Rogers	1800	2
Steve	Hills	2800	2
Steve	jobs	2400	2
bill	cosby	700	3

Departments

Dept_No	DepartmentName
1	Employee
2	Management
3	Staff

Example # 1

SELECT Firstname AS First_Name, Lastname AS Last_Name, salary AS Monthly_Salary, DeptNumber AS Department_Code FROM users

Result of the Query

First_Name	Last_Name	Monthly_Salary	Department_Code
John	Smith	1000	1
Mathew	Simon	3000	1
Bill	Steve	2200	1
Amanda	Rogers	1800	2
Steve	Hills	2800	2
Steve	jobs	2400	2
bill	cosby	700	3

Notice that the column names has been changed as mentioned in the query.

The SQL Alias can also be use for table name, which can simplify the JOIN operations, The Alias name for table also work the same way as it works for the columns.

SQL ALIAS Syntax for Tables

SELECT column_name(s) FROM TABLE_NAME AS alias_name

Consider the following example.

Example # 2

SELECT Firstname AS First_Name, Lastname AS Last_Name, salary AS Monthly_Salary, DepartmentName FROM users AS U JOIN Departments AS D WHERE U.deptnumber=D.Dept_No

Result of the Query

First_Name	Last_Name	Monthly_Salary	DepartmentName
John	Smith	1000	Employee
Mathew	Simon	3000	Employee
Bill	Steve	2200	Employee
Amanda	Rogers	1800	Management
Steve	Hills	2800	Management
Steve	jobs	2400	Management
bill	cosby	700	Staff

We have joined the two tables, and in the WHERE clause the alias name is used as a reference to the table, if you do not know how to join a table then please see the SQL JOIN tutorial.

SQL Alias

Example # 1

Add new comment