Table aliases

Previous Next

A table alias is a name (a string), usually an abbreviation, that represents a table instance in an SQL statement.

Syntax

This example shows the most common syntax. Table aliases also appear in various join constructs not shown here.

FROM           table  alias
,              table  alias ...

For each table declared in the FROM clause, you can optionally add an alias separated from the table name by whitespace. A table alias may not contain whitespace. A table alias must be unique within the SQL statement.

Once a table alias exists, it can be optionally used in column expressions as a prefix separated from the column name by a full stop ( ):

table-alias.column-name

Example

SELECT         cs.name

,              ci.name

FROM           customer cs
,              city ci

WHERE          ci.id = cs.lives_in

Use cases

The principal use case for table aliases is disambiguation. In the following statement, table aliases are essential, because they are the only way to refer to the correct table instances in the SELECT and WHERE clauses:

SELECT         e1.name

,              e2.name

FROM           employee e1
,              employee e2

WHERE          e2.id = e1.manager

The following statement does not strictly require table aliases for this reason, in spite of the fact that 2 different tables each have a "name" column. This makes prefixing necessary, but here a possible alternative is to prefix the entire table name:

SELECT         customer.name

,              city.name

FROM           customer
,              city

WHERE          city.id = customer.lives_in

Table aliases (and table name prefixes) are legal even if they are not essential to disambiguate column expressions. USoft recommends that you use short (1-letter, 3-letter...) table aliases as a default, at least in all your multi-table statements. This makes statements shorter and easier to read, and it keeps you from worrying constantly whether or not you need table prefixes or table alias prefixes for disambiguation.

 

 

See also

Queries and subqueries

Join constructs

Column expressions

SQL and the Rules Engine