Learning Objective

The objective of this SQL Server tutorial is to teach you how to use the ALIAS feature available in almost all RDBMS including SQL Server.

What is ALIAS in SQL Server?

Alias literally means another name for the same thing. Aliases are used in SQL Server for column names and table names. They serve the purpose of convenience and security. Aliases allow us to shorten and simplify long and complicated column and table names making queries and code easier to read and understand. It also helps to secure the database by concealing the actual names of the columns and table. It is particularly significant for web applications which can be accessed over a public network and where code compromise can lead to the compromise of the database and all information in it. A Table Alias is also referred to as Correlation Name or Range Variable.


It is important to note that there is no ALIAS keyword in SQL. It is also important to note that an alias is a temporary construct. To specify an alias for a column or table one simply has to mention the alias (i.e. the other name) after the actual column or table name. Once defined it can be used in the rest of the query.

SQL Server ALIAS Syntax

The basic SQL Server syntax for column and table ALIAS is as follows.

For column: An alias for a column can be defined either with or without the AS keyword. Hence both the below are valid.

column_name | expression AS alias 
column_name | expression alias  

For table: An alias for a table can also be defined either with or without the AS keyword. Hence both the below are valid.

table_name AS alias 
table_name alias  

We can understand the above more precisely by going through the examples that follow.

SQL Server ALIAS Examples

  • Columns are also referred to as fields or attributes and the terms are used interchangeably.
  • A row of information in a table is called a tuple.

Let us see how the ALIAS feature is used in queries. Suppose a university has a database with below 2 tables –one listing the different courses and another containing student data. We will query the same to demonstrate the different ALIAS usage scenarios.

9911International Business33
9919Political Science25
9948Applied Physics18
9991Molecular Biology11
Table Name:  disciplines_currently_available
Table Name:  current_year_enrollments

1)  SQL Server ALIAS – column alias examples

We can see above that the table names and column names are long and complex. We can run the below query using the ALIAS feature to represent the columns in the resultset with simple easy names. Please note that if the alias name contains space it must be within quotes. That is why Subject is without quote and ‘Available Seats’ is within quotes. The good practice is to always put the alias name within quotes.

discipline_name AS Subject
,max_seat AS 'Available Seats'
FROM disciplines_currently_available;

It is also possible to rewrite the above query without AS since it is optional as mentioned before.

discipline_name Subject
,max_seat 'Available Seats' 
FROM disciplines_currently_available;

Both the above queries will generate the same below output listing the subjects available for enrollment in the university.

SubjectsAvailable Seats
International Business33
Political Science25
Applied Physics18
Molecular Biology11

It is also possible to combine values from multiple columns and present it as a single column or attribute identified by an alias name. The below example query combines the first_name and last_name columns as Student Name.

first_name+ ' ' + last_name AS 'Student Name' 
FROM current_year_enrollment;

The query will generate the below output listing the name of all enrolled students.

Student Name
Gordon Brown
Priya Patel
Syed Ahmed
Niki Chang
Roshni Shaikh
Sandra Kugelman
William Hasselhoff
Jeremy Knight
Jamie Cosby
Melinda Keys
Ian Smith
Herbert Sanders

2)  SQL Server ALIAS – table alias example

A table alias is usually used in long complex queries to make it simple and easy to read and comprehend. Let us consider the below query. It joins the current_year_enrollment and disciplines_currently_available tables to list all students with their subjects.

current_year_enrollment.first_name + ' ' + current_year_enrollment.last_name 'Name'
,disciplines_currently_available.discipline_name ‘Subject’ 
FROM current_year_enrollment 
INNER JOIN disciplines_currently_available 
ON disciplines_currently_available.discipline_identifier=current_year_enrollment.discipline_identifier 
ORDER BY disciplines_currently_available.discipline_name;

The query will generate the following output listing all students and their corresponding subjects.

William HasslehofApplied Physics
Priya PatelBotany
Syed AhmedBotany
Roshni ShaikhBotany
Niki ChangInternational Business
Herbert SandersInternational Business
Jamie CosbyMolecular Biology
Melinda KeysMolecular Biology
Ian SmithPolitical Science
Gordon BrownPsychology
Sandra KugelmanPsychology
Jeremy KnightPsychology

The same output can be obtained by issuing a more graceful query with the help of table alias as below. The query uses stu as alias for current_year_enrollment table and sub as alias for disciplines_currently_available table and produces the same resultset as above. Please note that the entire query has been written without using the AS keyword for both column and table alias.

first_name + ' ' + last_name 'name'
,discipline_name ‘subject’ 
FROM current_year_enrollment stu 
INNER JOIN disciplines_currently_available sub 
ON sub.discipline_identifier=stu.discipline_identifier 
ORDER by sub.discipline_name;