Alias (SQL): Difference between revisions

Revision as of 20:30, 8 March 2013

Revision as of 14:33, 1 May 2013

Line 2:

An alias is a feature of SQL that is supported by most, if not all, [[RDBMS|relational database management systems (RDBMSs)]]. Aliases provide [[database administrator]]s, as well as other database users, with the ability to reduce the amount of code required for a query, and to make queries generally simpler to understand. In addition, aliasing can be used as an [[Obfuscation_(software)|obfuscation technique]] to protect the real names of database fields.

In SQL, you can alias both tables themselves or the [[Column (database)|columns]]. A programmer can temporarily assign another name to a table or column (for the duration of the [[Select (SQL)|SELECT query]]) by using an alias. In other words, it does not actually rename the column or table. This is often useful when youeither tables or their columns 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".

There are two types of aliases in SQL:

The general syntax of an alias is <code>SELECT * FROM table_name [AS] alias_name</code>. Note that the AS keyword is completely optional and is usually kept for readability purposes. Here is some sample data that the queries below will be referencing:

# Table aliases

# Column aliases

You can give another name to a table (for the duration of the [[Select (SQL)|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".

<br><br>

Syntax: ''SELECT * FROM table_name [AS] alias_name''<br />

AS is an optional keyword.<br><br>

Here is some sample data that the queries below will be referencing:

{| class="wikitable" style="text-align:center; float:left; margin-left:5px"

Line 32 ⟶ 21:

<br clear="all" />

Using a table alias:

<source lang="sql">

SELECT D.DepartmentName FROM Department AS D

<br/source>

We can also write the same query like this (Note that the AS clause is missingomitted this time):

<source lang="sql">

SELECT D.DepartmentName FROM Department D

<br/source>

A column alias is similar:

<source lang="sql">

SELECT d.DepartmentId AS Id, d.DepartmentName AS Name FROM Department d

</source>

In the returned [[result set]]s, 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".

<br><br>

Also, if only one table is being selected and the query is not using [[Join (SQL)|table joins]], it is permissible to omit the table name or table alias from the column name in the SELECT statement. Example as follows:

<source lang="sql">

SELECT DepartmentId AS Id, DepartmentName AS Name FROM Department d

</source>

[[Category:SQL]]