Chapter 3 Home | Prev: 3.3 Basics of SELECT | Next: 3.5 Calculated Values and Single-Line

3.4 - Aliases

Aliases (Field and Table Aliases)

Aliases are a feature of SQL that allows you to rename fields and tables inside the SQL statement for a variety of purposes.  Some of these purposes include:

• Having the name of columns different than the field name in the output.
• If the output is a new calculated value, it needs an appropriate name
• when joining multiple tables, the same field name may exist in more than one table, so we need an identifier
• can be used to same significant typing and make the SQL code much cleaner and easier to read

Quotations in SQL

Quotes are utilized differently in many programming languages  and even used differently in different versions of SQL.  Oracle uses the most strict implementation of quotes and is standardized to work in mosts versions of SQL.

Single Quotes: are used to defined string literals.  For example:  Inserting a person's name, would require the name to be in single quotes 'Name'

Double Quotes: are used to define or use an object.  For example: If a column name has a space in it, which is bad practice, you would have to use double quotes around the name to use it or define it.  Aliases can have spaces in them, if they are to be in a printed report, but are required to be defined using double quotes.

In MS SQL, the use of square brackets [ ] may be used in place of double quotes, although double quotes still work the same.

Although MS SQL seems to prefer square brackets, it is highly recommended not to use square brackets, but use double quotes instead in order to maximize compatibility with other DBMSs in case the SQL is to be ported.

It is bad practice to ever have a space in an object name, even if it is an alias.  In modern technology, creating reports directly in the database for printing is becoming obsolete.  Most of the time, the database is feeding query results to software which has a user interface.  This process often means columns are referred to by name in the software and spaces in the column names makes this extremely difficult in many programming languages.  Spaces should only ever be used when the output is being directly viewed by the recipient of the report or it is being printed. In these cases, the name or aliases must be surrounded by double quotes.

Field Aliases

Aliases can be applied to fields in several ways.  Here are a few examples:

SELECT firstname AS first, lastname AS last
FROM employees;

FIRST	LAST

SELECT firstname || ' ' || lastname AS name
FROM employees;

SELECT firstname + ' ' + lastname AS name
FROM employees;

NAME

SELECT productCode, price, quantityOrdered AS quantity, price * quantityOrdered
FROM orders;

PRODUCTCODE	PRICE	QUANTITY	[PRICE * QUANTITYORDERED]

Without the alias, the column name for the calculation becomes the calculation itself.

SELECT productCode, price, quantityordered AS quantity, price * quantityordered AS subtotal
FROM orders;

By using an appropriate alias, the column name is much more readable and usable

PRODUCTCODE	PRICE	QUANTITY	SUBTOTAL

The AS keyword is optional. It is a good idea for readability though.

By default, output column names are in ALL CAPS.  If a case senstive column name is required or a column name with a space then the use of double quotes is needed.

SELECT firstname AS "FirstName", lastname AS LastName
FROM employees;

FirstName	LASTNAME

SELECT firstname || ' ' || lastname AS "Employee Name"
FROM employees;

SELECT firstname + ' ' + lastname AS "Employee Name"
FROM employees;

In the above example, the name FirstName will appear as it is in the quotes, but LastName will appear as LASTNAME. Also note that the space between the first and last names is defined using single quotes while the aliases, which are objects, are defined using double quotes.

Table Aliases

Aliases can also be applied to data sources as well, regardless if they are tables, views, or user-defined objects.

SELECT firstname, lastname FROM employees e WHERE employeeid = 123;

Table aliases can then be used in other parts of the statement to refer to fields more directly

SELECT e.firstname, e.lastname FROM employees e WHERE employeeId = 123;

In this example, the prefix e is used to absolutely declare the firstname and lastname fields. This is important when more than one table may be referenced and multiple fields with the same name are available.