A table alias can be thought of as an alternative name for a table or view. A table or view, therefore, can be referred to in an SQL statement by its name or by a table alias.
An alias can be used wherever a table or view name can be used. An alias can be created even though the object does not exist (though it must exist by the time a statement referring to it is compiled). It can refer to another alias if no circular or repetitive references are made along the chain of aliases. An alias can only refer to a table, view, or alias within the same database. An alias name cannot be used where a new table or view name is expected, such as in the CREATE TABLE or CREATE VIEW statements; for example, if an alias name of PERSONNEL is created then a subsequent statement such as CREATE TABLE PERSONNEL... will cause an error.
The option of referring to a table or view by an alias is not explicitly shown in the syntax diagrams or mentioned in the description of the SQL statement.
A new unqualified alias cannot have the same fully-qualified name as an existing table, view, or alias.
The effect of using an alias in an SQL statement is similar to that of text substitution. The alias, which must be defined when the SQL statement is compiled, is replaced at statement compilation time by the qualified base table or view name. For example, if PBIRD.SALES is an alias for DSPN014.DIST4_SALES_148, then at compilation time:
SELECT * FROM PBIRD.SALES
effectively becomes
SELECT * FROM DSPN014.DIST4_SALES_148
In a federated system, the aforementioned uses and restrictions apply not only to table aliases but also to aliases for nicknames. Thus, a nickname's alias can be used in lieu of the nickname in an SQL statement; an alias can be created for a nickname that does not yet exist, provided that the nickname is created before statements that reference the alias are compiled; an alias for a nickname can refer to another alias for that nickname; and so on.
For syntax toleration of other relational database management system applications, SYNONYM can be used in place of ALIAS in the CREATE ALIAS and DROP ALIAS statements.
Refer to CREATE ALIAS for more information about aliases.