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.
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.
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.
- 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.
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.
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.
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.
|William Hasslehof||Applied Physics|
|Niki Chang||International Business|
|Herbert Sanders||International Business|
|Jamie Cosby||Molecular Biology|
|Melinda Keys||Molecular Biology|
|Ian Smith||Political Science|
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;