Alias (SQL): Difference between revisions

Revision as of 18:32, 26 December 2012

Latest revision as of 12:09, 2 October 2023

(29 intermediate revisions by 19 users not shown)

Line 1:

{{Short description|Feature of SQL}}

{{unref|date=December 2012}}

{{refimprove|date=October 2013}}

An alias is a feature of SQL that is supported by most, if not all, [[RDBMS|relational database management systems (RDBMSs)]].

An '''alias''' is a feature of [[SQL]] that is supported by most, if not all, [[RDBMS|relational database management systems (RDBMSs)]]. Aliases provide users with the ability to reduce the amount of code required for a query, and to make queries simpler to understand. In addition, aliasing is required when doing self joins (i.e. joining a table with itself.)

<br />

Aliases provide [[database administrator]]s, as well as other database users, with two things:

In SQL, you can alias tables and [[Column (database)|columns]]. A table alias is called a '''correlation name''', according to the SQL standard.<ref>ANSI Standard SQL – Foundation Document – Date: 2010-10-14</ref> A programmer can use an alias to temporarily assign another name to a table or column for the duration of the current [[Select (SQL)|SELECT query]]. Assigning an alias does not actually rename the column or table. This is often useful when either tables or their columns have very long or complex 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".

# Reduces the amount of code required for a query, and

# To make queries generally simpler to follow.

The general syntax of an alias is <syntaxhighlight lang="sql" inline>SELECT * FROM table_name [AS] alias_name</syntaxhighlight>. 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:

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 (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 35 ⟶ 20:

| 35 || Marketing

|}

<br {{clear="all" />}}

Using a table alias:

<syntaxhighlight lang="sql">

SELECT D.DepartmentName FROM Department AS D

</syntaxhighlight>

<br>

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

<syntaxhighlight lang="sql">

SELECT D.DepartmentName FROM Department D

</syntaxhighlight>

<br>

A column alias is similar:

<syntaxhighlight lang="sql">

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

</syntaxhighlight>

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:

<syntaxhighlight lang="sql">

SELECT DepartmentId AS Id, DepartmentName AS Name FROM Department d

</syntaxhighlight>

Some systems, such as Postgres<ref>[https://www.postgresql.org/docs/13/queries-table-expressions.html#QUERIES-FROM PostgreSQL: Documentation: 13: 7.2. Table Expressions]</ref> and Presto,<ref>https://prestodb.io/docs/0.248/sql/select.html SELECT — Presto 0.248 Documentation</ref> support specifying column aliases together with table aliases. E.g.

<syntaxhighlight lang="sql">

SELECT D.Id FROM Department AS D(Id)

</syntaxhighlight>

would produce the same result set as before. In this syntax it is permissible to omit aliases for some column names. In the example, an alias was provided for DepartmentId, but omitted for DepartmentName. Columns with unspecified aliases will be left unaliased. This syntax is often used with expressions that do not produce useful table and column names, such as VALUES<ref>https://prestodb.io/docs/0.248/sql/values.html#examples VALUES — Presto 0.248 Documentation</ref> and UNNEST.<ref>https://prestodb.io/docs/0.248/sql/select.html#unnest SELECT — Presto 0.248 Documentation</ref> As an example, one may conveniently test the above SQL statements without creating an actual Departments table by using expressions such as

<syntaxhighlight lang="sql">

WITH Department(DepartmentId, DepartmentName) AS (VALUES (1, 'HR'), (2, 'IT'))

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

</syntaxhighlight>

==References==

{{Reflist}}

{{SQL}}

[[Category:SQL]]

[[Category:Articles with example SQL code]]