Alias (SQL)
Encyclopedia
An alias is a feature of SQL that is supported by most, if not all, relational database management systems (RDBMSs).
Aliases provide Database Administrators (DBAs)
, as well as other database users, with two things:
There are two types of aliases in SQL:
You can give another name to a table (for the duration of the SELECT query) by using an alias.
This does not rename the database table!
This is often useful when you have very long or complex table names. An alias name could be anything, but usually it is kept short. For example, it might be common to use a table alias such as "pi" for a table named "price_information".
Syntax: SELECT * FROM table_name [AS] alias_name
AS is an optional keyword.
Here is some sample data that the queries below will be referencing:
Using a table alias:
SELECT D.DepartmentName FROM Department AS D
We can also write the same query like this (Note that the AS clause is missing this time):
SELECT D.DepartmentName FROM Department D
A column alias is similar:
SELECT d.DepartmentId AS Id, d.DepartmentName AS Name FROM Department d
In the returned result sets, the data shown above would be returned, with the only exception being "DepartmentID" would show up as "Id", and "DepartmentName" would show up as "Name".
Also, if only one table is being selected and the query is not using table joins
, it is permissible to omit the table name or table alias from the column name in the SELECT statement. Example as follows:
SELECT DepartmentId AS Id, DepartmentName AS Name FROM Department d
Aliases provide Database Administrators (DBAs)
Database administrator
A database administrator is a person responsible for the design, implementation, maintenance and repair of an organization's database. They are also known by the titles Database Coordinator or Database Programmer, and is closely related to the Database Analyst, Database Modeller, Programmer...
, as well as other database users, with two things:
- Reduces the amount of code required for a query, and
- To make queries generally simpler to follow.
There are two types of aliases in SQL:
- Table aliases
- Column aliases
You can give another name to a table (for the duration of the SELECT query) by using an alias.
This does not rename the database table!
This is often useful when you have very long or complex table names. An alias name could be anything, but usually it is kept short. For example, it might be common to use a table alias such as "pi" for a table named "price_information".
Syntax: SELECT * FROM table_name [AS] alias_name
AS is an optional keyword.
Here is some sample data that the queries below will be referencing:
| DepartmentID | DepartmentName |
|---|---|
| 31 | Sales |
| 33 | Engineering |
| 34 | Clerical |
| 35 | Marketing |
Using a table alias:
SELECT D.DepartmentName FROM Department AS D
We can also write the same query like this (Note that the AS clause is missing this time):
SELECT D.DepartmentName FROM Department D
A column alias is similar:
SELECT d.DepartmentId AS Id, d.DepartmentName AS Name FROM Department d
In the returned result sets, the data shown above would be returned, with the only exception being "DepartmentID" would show up as "Id", and "DepartmentName" would show up as "Name".
Also, if only one table is being selected and the query is not using table joins
Join (SQL)
An SQL join clause combines records from two or more tables in a database. It creates a set that can be saved as a table or used as is. A JOIN is a means for combining fields from two tables by using values common to each. ANSI standard SQL specifies four types of JOINs: INNER, OUTER, LEFT, and RIGHT...
, it is permissible to omit the table name or table alias from the column name in the SELECT statement. Example as follows:
SELECT DepartmentId AS Id, DepartmentName AS Name FROM Department d