Renaming Table Name and Column Name using Alias Keyword

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.

Sql alias keyword used to sql server database alias, sql alias column name, and sql alias example.

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;


  • 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
111 Suresh Babu Male 32 Nasik MH
222 Siva Kumar Male 22 Chennai TN
333 Azagu Murugan Male 21 Madurai TN
444 Azagu Varshith Female 26 Bangalore KN

Sample Database Table - Employee_Official

ID JoinDate Designation Technology Salary
111 03 October 2014 Programmer Sql Server 25000
222 17 December 2014 Administrator Sql Server 18000
333 23 July 2015 Programmer MySql 15000
444 20 March 2014 Administrator 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:

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

The result of above query is:

FirstName Last Name
Suresh Babu
Siva Kumar
Azagu Murugan
Azagu Varshith

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

FName + " " + LName As FullName 
FROM Employee;

The result of above query is:

Suresh Babu
Siva Kumar
Azagu Murugan
Azagu Varshith

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

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:

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
111 Suresh Babu 25000
222 Siva Kumar 18000
333 Azagu Murugan 15000
444 Azagu Varshith 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".

Sql alias table name using rename table as, rename multiple table, and joins table using alias name..

SQL Alias with Aggregate Function

In ORDER BY and GROUP BY clauses, a column's name can be used to relate to the column's value, but not in WHERE or HAVING clauses; you should write out the phrase alternatively.

Aliases are widely used to make your result set's column titles easier to read. When utilising an aggregate function like MIN, MAX, AVG, SUM, or COUNT in your query, you'll most likely alias a column.


col_name [AS alias_col_name], aggregate_function(col_name) [AS alias_name], ...
FROM table_name [ alias_table_name ];

There is no requirement for the AS keyword in column name and table name alias table name.

Example 1:

Let's look at an example of how to use to alias a column name in SQL.

In this example, we have a table called employees with the following data:

employee_number last_name first_name salary dept_id
1001 Smith John 62000 500
1002 Anderson Jane 57500 500
1003 Everest Brad 71000 501
1004 Horvath Jack 42000 501

Let's demonstrate how to alias a column. Enter the following SQL statement:

SELECT dept_id, COUNT(*) AS total
FROM employees
GROUP BY dept_id;

There will be 2 records selected. These are the results that you should see:

dept_id total
500 2
501 2

In this example, we've aliased the COUNT(*) field as total. As a result, total will display as the heading for the second column when the result set is returned. Because our alias_name did not include any spaces, we are not required to enclose the alias_name in quotes.

Example 2:

SELECT name AS Employee_Name, 
SUM(hours) AS Total_Hours 
FROM employee_hour emp_hours
GROUP BY name;


Opal Kole 24
Beccaa Moss 29
Paul Singh 29
Max Miller 23

SQL Alias Using All Columns

Multiple columns can be aliased in the same query.

Long table names and needless column identity (e.g., the table may have two IDs, but only one is used in the statement) can be eliminated, reduce the length of�code. This, together with table aliases, allows you to use longer meaningful names in your database structure while still maintaining queries on it simple.

In the SELECT clause of a SELECT statement, a column alias appears after the column name. For column heads, the DBMS will utilise default values. (The original column's name in the table description is used as the default heading in a result.) The AS clause can be used to create a column alias.

A column alias is a different name (identifier) that you supply to modify how column heads appear in a result. If column names are confusing, difficult to type, or too lengthy or short, use column aliases.

Column aliases are mostly used to keep the code shorter and the column names easier to understand.

Note: If the alias includes spaces, punctuation, or special characters, it's recommended that you surround it in single or double quotes. If the alias is a single non-reserved term with only letters, numerals, or underscores, you can skip the quotations. If you want a column to keep its default heading, remove the AS clause from it.

Syntax: To create column aliases:

SELECT column1 [AS] alias1,
column2 [AS] alias2,
columnN [AS] aliasN
FROM table;

column1, column2, ..., columnN are column names; alias1, alias2, ..., aliasN are their corresponding column aliases; and table is the name of the table that contains column1, column2, ....

Example 1:

SELECT course_id AS "Course ID", exercise_id AS "Exercise ID"
FROM bugs;

Example 2: Aliases can be created in all versions of SQL using double quotes (").

SELECT Name AS "First Name", 
MName AS "Middle Name",
LName AS "Last Name"
FROM Employees  

SQL Alias With CASE Statement

Example 1: Is it possible to alias a column name and then use that in a CASE statement?

SELECT col1 as a, CASE WHEN a = 'test' THEN 'yes' END as value FROM table;

I am trying to alias the column because actually my CASE statement would be generated programmatically, and I want the column that the case statement uses to be specified in the SQL instead of having to pass another parameter to the program.

SQL Alias Column in Where Clause

It is possible to utilise column_alias in an ORDER BY clause, though not in a WHERE, GROUP BY, or HAVING clause.

In standard SQL, you can't use a WHERE clause to refer to a column alias. This constraint is in place since this column value may not yet be known when the programme is run.


select col1 as alias1
FROM table1
WHERE col2 in (select col3 from table2 where cil3 = alias1);

Example 1: use of correlated column aliases in the WHERE clause:

select abs(col1) as alias1
FROM table1
WHERE col2 in (select col3 as a3 from table2 where alias3 = alias1);

Example 2:

SELECT YEAR(transaction_date) AS YEAR,amount FROM #temp

You get an error

Msg 207, Level 16, State 1, Line 2
Invalid column name �year�.

It is because the column alias are not immediately known to the WHERE clause, whereas it is known in the ORDER BY clause because ORDER BY is executed lastly after the entire column list is known.

Example 3:

SELECT logcount, logUserID, maxlogtm,
DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
WHERE ( DATEDIFF(day, maxlogtm, GETDATE() > 120)

Normally you can't refer to field aliases in the WHERE clause. (Think of it as the entire SELECT including aliases, is applied after the WHERE clause.)

SQL Alias Column Name with Spaces

If the alias name includes spaces, it must be surrounded by quotation marks.

While aliasing a column name, spaces are permitted. Therefore, it is not recommended to use spaces when aliasing a table name.

The alias name can only be used inside the SQL statement.

Example 1: using space in alias Double quotes for alias with a space.

SELECT U.first_name + ' ' + U.last_name AS "User Name"

Example 2:

SELECT dept_id, COUNT(*) AS "Count_employees"
FROM employees
GROUP BY dept_id;


dept_id Count employees
500 2
501 2

We've aliased the COUNT(*) field to "Count employees" in this instance, so that this will be the heading for the second column in our result set. "Count employees" must be surrounded in quotes in the SQL statement since this col alias name contains spaces.

SQL Alias with Delete Statement

In SQL, the Delete statement is used to remove one or more records from a table. By combining the DELETE statement with the WHERE clause, you can delete specific rows from a table; otherwise, it would delete all entries.

Example : The reason why needs to use aliases is that some conditions need to be written in the subquery in the where condition of delete, for example:

delete products from products as p1
where p1.rowid<
    select MAX(p2.rowid) from products p2 
    where and p1.price=p2.price


Rowid Name Price
1 Apple 50
4 oranges 100
6 Banana 80

If you move a database used by an application to a new server and you can't change the specified database connection in the application, you can use an alias and keep the application configuration unchanged.

SQL Alias Name in Subquery

SQL Relations Subqueries are used to retrieve data from a table that is mentioned in the outer query. Because the subquery is associated with the outer query, it is called correlated. A table alias (also known as a correlation name) must be provided in these queries to identify which table link to utilise.

Because you have access to the parent's aliases in a correlated subquery, the aliases have to be identical in both the parent query and the corresponding subquery.

Example 1: The alias is the pet name of a table which is brought about by putting directly after the table name in the FROM clause. This is suitable when anybody wants to obtain information from two separate tables.

SELECT a.ord_num,a.ord_amount,a.cust_code,a.agent_code
FROM orders a
WHERE a.agent_code=(
SELECT b.agent_code
FROM agents b WHERE b.agent_name='Alex');


200127 2500 C00015 A003
200100 1000 C00015 A003

Example 2: If we take a correlated subquery such as the one below we have a single, global name space shared between the parent query and the correlated subquery:

from Foobar a
join Bar b
on b.FooBarID = a.FooBarID
where not exists
(select 1
from Bar b2
where b2.BarCategoryID = b.BarCategoryID
and b2.BarDate > b.BarDate)

The correlated subquery does not have an alias as it does not participate in a join as such. The references b and b2 for bar are both available to the subquery as correlated subqueries share their namespace for aliases with the parent.

Example 3:

SELECT Inc.OwnerIdName as OIN
FROM Incident 
WHERE CreatedOn BETWEEN '2011/1/1' AND '2011/1/31'
AND Incident.OwnerIdName = Inc.OwnerIdName ) as CasesOpened
FROM IncidentResolution 
WHERE ActualEnd BETWEEN '2011/1/1' AND '2011/1/31'
AND Incident.OwnerIdName = Inc.OwnerIdName ) as CasesClosed
FROM Incident Inc

If you want to use the Alias directly, you will have to do that early on in your query and then use it for all the Scalar Sub-Queries.

SQL Alias for Table Name

SQL aliases are used to provide a temporary name to a table or a column in a table. Aliases are frequently used to enhance column names easier to understand. An alias persists only while the query is active. The AS keyword is used to generate an alias.

TABLE ALIASES are used to make your SQL more readable by shortening it or when conducting a self join (ie: listing the same table more than once in the FROM clause).

Table Alias assistance is a highly handy addition to the Select statement. Table aliasing is a simple technique to make writing your select statement simpler, and it also has the added benefit of allowing you to quickly alter the table utilised in your query.

Table aliases are a necessary evil when dealing with highly normalized schemas. For example, and I'm not the architect on this DB so bear with me, it can take 7 joins in order to get a clean and complete record back which includes a person's name, address, phone number and company affiliation.

Table aliases are typically used to retrieve data from multiple tables and link them using field relations.

Example 1: To fetch Grade and NAME of Student with Age = 20.

SELECT s.NAME, d.Grade FROM Student AS s, Student_Details


NAME Grade

Example 2: Typically that would look like the statement above. Notice how you are using the combination of table and column names in multiple places throughout the statement. Now that we support Table Alias, you can make this statement in this way instead:

Select a.Address, a.AddressPostalCode, 
a.BuildingName, p.plantype, p.plannr, p.plannavn
From City As "c", Plan As "p";

Notice how much simpler it is to read the statement now that the long table names have been removed. The table and the Table Alias: City As "c" has been bolded. The string included in quotes is the Table Alias, which can be substituted for the table name in the statement. A Table Alias is a string of characters that identifies the table. I usually only use one or two characters from the table name, usually just the first.

You only need to alter the table name in the From section of the statement if you wish to utilise a different table in your statement. This, of course, necessitates that any columns used be present in both tables.

SQL Alias in Group By Clause

This sequence specifies which names (columns or aliases) are permitted in relational database systems since they really must be added in a prior step.

In SQL Server, implement group by for the alias column. In general, we can't utilise alias column names in query functions like where, group by, order by, and so forth. To build groups by for the alias column, we must wrap all SQL query statements in a subquery and construct groups on the statement's exterior.

Example 1: Since this GROUP BY clause is run before the SELECT clause in Oracle and SQL Server, you can't use a term defined in the SELECT clause in the GROUP BY clause.

SELECT itemName as ItemName,
substring(itemName, 1,1) as FirstLetter,
FROM table1
GROUP BY itemName, FirstLetter;

Example 2: Because of the logical sequence of processing, SQL Server does not enable you to reference the alias in the GROUP BY clause. The alias is not known when the GROUP BY clause is analysed because it is executed before the SELECT clause. This also demonstrates how the alias can be used in the ORDER BY clause.

SELECT ItemName, FirstLetter, COUNT(ItemName)
   SELECT ItemName, SUBSTRING(ItemName, 1, 1) AS FirstLetter FROM table1
) ItemNames
GROUP BY ItemName, FirstLetter

Example 3: To implement group by to alias column name in sql server below query used:

Select Dob,count(Id) as NumberofEmployees
Select Id,Name,CreatedDate as Joindate from @temp
) subdata
group by Joindate

If you observe above query �Dob� is an alias column name in sub query and we are using same �Dob� alias column in group by outside of that statements in sql server.

SQL Alias in Having Clause

Because the Having clause comes before Select, the alias declared in the SELECT portion should not be passed to the Having clause by its revealed name. As a result, the server is unaware of that alias.

The groups that do not fulfill the HAVING clause's search condition are then removed.

When SQL_COMPAT='NPS', a HAVING clause can refer to a column of a SELECT clause by either its name or its exposed name.

Example 1: one, because the select list includes the same expression that can also be used in the having clause:


Example 2:

SELECT Major_category,
ROUND(AVG(College_jobs)/AVG(Total), 3) as Share_degree_jobs
FROM new_grads
GROUP BY Major_category
HAVING Share_degree_jobs < 0.3

Example 3: The following examples illustrate the use of exposed names of SELECT clause columns in having clauses when SQL_COMPAT='NPS':

SELECT c1 as a, COUNT(*) as c
GROUP BY c1 having a > 20 and c > 10;
SELECT t1.c1 as a, t1.c2+t2.c3 as b, COUNT(*) as c
FROM t1 JOIN t2 ON t1.c1 = t2.c1
GROUP BY t1.c1, b having a+5 = 10 ;
SELECT var(c1) as a
GROUP BY c1 having a > 200

SQL Alias in Order By Clause

The ORDER BY clause in SQL Server operates with the alias name of the column provided in the SELECT statement, however the WHERE, GROUP BY, and HAVING clauses do not since ORDER BY arrives after the SELECT statement in the logical query filtering process.

Example: The reason for this is that the name dob could be used as an alias name for formatted date values, and when used in the ORDER BY clause, it arranges the resultset using VARCHAR values of formatted dates rather than an actual DATE value.

SELECT customer_id,customer_name,CONVERT(VARCHAR(10),dob,101) AS dob_new

Now the result is ordered by DATE value and not by formatted VARCHAR value.

SQL Alias in Update Statement

You would ordinarily use an alias in your FROM clause, but until an update doesn't have a FROM clause, you must mention your alias in your update clause.


syntax for using an alias in an update statement on SQL Server is as follows:

WHERE Q.ID = 101;

The alias should not be necessary here though.

Example :

UPDATE table1, table2 as tbl2, tbl3 as tbl3
SET table1.field1 = tbl2.field1, table1.field3=tbl3.field3
WHERE tbl3.field4 = tbl2.field4

Because MySQL allows you to alias tables from other databases, this is useful while updating a table with data from another database.

SQL Alias with Table Join

Names can get long and unwieldy as requests become increasingly complicated. We can use aliases to allocate names to objects in the query to keep things better when performing join table alias names.

Your SQL query will be easier to read and manage if you use alias.


SELECT t1.column_name (AS) new_column_name,
t2.column_name (AS) other_new_column_name,
FROM table1 (AS) t1
JOIN table2 (AS) t2
  ON t1.column_name = t2.column_name

Example 1: We can alias both table names:

SELECT ar.Title, ar.First_Author, jo.Journal_Title
FROM articles AS ar
JOIN journals  AS jo
ON ar.ISSNs = jo.ISSNs;

Example 2: Using an Alias with Non-Unique Column Names

It's common to have the same column name in two columns while working with several tables. This happens a lot with the id column. This is demonstrated in the following example:

SELECT,, employee_name, employee_department
FROM employees
INNER JOIN customer
ON = customer.employee_id


id id employee_name employee_department
12 14 John Jacobs Human Resources

Example 3: As a result, stakeholders will be puzzled, as they will have no means of knowing which id corresponds to which database.

Creating a column alias for each id column can be beneficial:

SELECT employee_id, customer_id, employee_name, employee_department
FROM employees
INNER JOIN customer
ON = customer.employee_id


employee_id customer_id employee_name employee_department
12 14 John Jacobs Human Resources

SQL Alias Naming Convention

The last line is important. Your naming conventions should be simple to comprehend for all types of objects and aliases. You (or someone else) will be reading your code dozens of times for every time you type it out (at least). I will decide which one I consider to be more essential. When crafting multi-table queries, it happens.

It's often easier to prevent conflicts between two instances of the same table when using a short-hand term. SQL statements, as a declarative language, are usually short and easy to understand, with command names that sound like English. As long as they follow a consistent norm, short alias names do not cause any difficulty in this scenario.

In SQL queries, utilise aliases for table names. A good rule of thumb is to make the alias out of the first or first two letters of each capitalised table name, and you may differentiate between two aliases for the same table name by using a numerical suffix. If two tables have the same initial letter, you can use the second letter to break ties, or you can use a suitably distinguishable substring of the name.

For Example: �Site� becomes �s� and �SiteType� becomes �st�.

If the original name is longer than you want to type for the rest of the query, this option should be utilised. Although aliasing is used to improve readability even when it isn't required, most short queries don't use it.

Although there are no column name issues, you keep referring to a long table name.

It is not required to be readable. Here, conciseness is more crucial.

For instance, lsmhd would work but would be unreadable.

However, it should be used in such a way that your query is more readable in the end, not less.

When using the c# convention (# being the number attached to each), the "bad example" below does not easily differentiate between the nations and cities tables. Because it is more obvious that c and ci are different tables, the first "excellent example" is an acceptable choice (note that it is a little easier to follow what the "good example" is selecting). Furthermore, because both countries and cities have a column called "name" (e.g., "India" is a country name and "Pune" is a city name), you must indicate which table you are accessing in the first line for it to be valid SQL.

Example 1: with alias for single countries table

FROM countries c, cities ci1, cities ci2
WHERE ci1.country_code = c.code AND ci2.country_code = c.code
AND ci1.population >= 5000000 AND ci2.population >= 5000000 
AND <;

Example 2: without alias for single countries table

FROM countries, cities ci1, cities ci2
WHERE ci1.country_code = countries.code AND ci2.country_code = countries.code
AND ci1.population >= 5000000 AND ci2.population >= 5000000 
AND <;

SQL Alias Same Table Twice

You can link a table to itself using a self join. It allows you to compare rows inside the same database or query hierarchical data.

The inner join or left join clause is used in a self-join. Because the self-join query refers to the same table, the table alias is used to give the same table multiple aliases inside the query.

Remember that using table aliases to reference the same table several times in a query will result in an error.


The following shows the syntax of joining the table T to itself:

    Tbl t1

Table is mentioned twice in the query. The table aliases t1 and t2 are used in the query to give the T table distinct names.

Example 1: Add a city name column from the city table to the customer table. As a result, we linked two tables together. A self join, for example, would mean merging the customer table with itself.

Here�s the customer table as a reminder:

customer_id firstname lastname birthdate address_id
1 Hari Haran 1983-05-12 2
2 Linda Haran 1990-07-30 1
3 Jeni Mary 1989-04-15 5
4 Jegan Kutty 1988-12-26 6
5 Tim Ross 1957-08-15 3
Steve Donell Haran 1967-07-09 4

The address_id column stores the customer_id of the customer�s address. For example, Customers 1 and 2 (Hari and Linda) are city_id of each other, Customers 3 and 5 (Jeni and Jegan) are city_id of each other, and so on. We can add the first name and the last name of the city_id to each record in the customer table. To do this, we need to perform a self join, that is, join the customer table to itself:

select cust.customer_id,
      spouse.firstname as spouse_firstname,
      spouse.lastname as spouse_lastname
from customer cust
join customer spouse
on cust.spouse_id = spouse.customer_id;


customer_id firstname lastname birthdate spouse_id spouse_firstname spouse_lastname
1 John Mayer 1983-05-12 2 Linda Haran
2 Mary Mayer 1990-07-30 1 Hari Haran
3 Jeni Mary 1989-04-15 5 Tim Ross
4 Jegan Kutty 1988-12-26 6 Steve Donell
5 Tim Ross 1957-08-15 3 Jeny Mary
6 Steve Donell 1967-07-09 4 Jegan Kutty

Example 2: The staff table keeps track of each employee's ID, first name, last name, and email address. It also has a hr id field, which defines the direct hr. Maze, for instance, reports to Cate because the value of Maze's hr id is Cate.

Because Cate has no HR, the hr id column is NULL.

The self join is used to determine who reports to whom, as seen in the following query:

SELECT e.first_name + ' ' + e.last_name employee,
    m.first_name + ' ' + m.last_name manager
    sales.staff e
INNER JOIN sales.staff m ON m.staff_id = e.hr_id

In this example, we referenced to the staff table twice: one as e for the employees and the other as m for the hr. The join predicate matches employee and manager relationship using the values in the e.hr_id and m.staff_id columns.

SQL Alias with Dot

Example 1: It will work if you enclose it in square brackets.

SELECT MAX(Value) AS [PrmTable.Value]
FROM TempTable

Example 2: A dot "." appears in alias... the column name is incorrect:

SELECT column AS 'alias.1' FROM table;


result 1
result 2

Example 3: alias column name can't contains a dot "."

SELECT 'test' AS "testing.this"
| testing.this |