SQL SERVER ALIAS

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.

Operation

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 
OR 
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 
OR 
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.

discipline_identifier	discipline_name	discipline_max_seat
9911	International Business	33
9913	Botany	50
9919	Political Science	25
9922	Psychology	15
9948	Applied Physics	18
9991	Molecular Biology	11

enrollment_identifier	first_name	last_name	discipline_identifier
2011	Gordon	Brown	9922
2025	Priya	Patel	9913
2031	Syed	Ahmed	9913
2033	Niki	Chang	9911
2046	Roshni	Shaikh	9913
2049	Sandra	Kugelman	9922
2051	William	Hasslehof	9948
2055	Jeremy	Knight	9922
2057	Jamie	Cosby	9991
2062	Melinda	Keys	9991
2071	Ian	Smith	9919
2088	Herbert	Sanders	9911

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.

SELECT 
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.

SELECT 
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.

Subjects	Available Seats
International Business	33
Botany	50
Political Science	25
Psychology	15
Applied Physics	18
Molecular Biology	11

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.

SELECT 
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.

SELECT 
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.

Name	Subject
William Hasslehof	Applied Physics
Priya Patel	Botany
Syed Ahmed	Botany
Roshni Shaikh	Botany
Niki Chang	International Business
Herbert Sanders	International Business
Jamie Cosby	Molecular Biology
Melinda Keys	Molecular Biology
Ian Smith	Political Science
Gordon Brown	Psychology
Sandra Kugelman	Psychology
Jeremy Knight	Psychology

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.

SELECT 
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;