Correlation identifiers assign a new identifier to a table.

Correlation identifiers can only be defined in:

• the FROM clauses of either a query specification

• a DELETE statement  

• an UPDATE statement.

Important: Correlation identifiers can only be used in the query specification, DELETE or UPDATE statement where they were defined.

Correlation identifiers affect the query specification or statement where they have been defined and all the sub-queries present within that query specification or statement.

If a correlation identifier has been defined for a table and a column of the table needs to be qualified, only the correlation name can be used to do so. The original table name or synonym can not be used.

Whenever you need to distinguish between two separate occurrences of the same table use a correlation identifier. In this example,

 

SELECT * FROM cruise a, cruise b;

 

the table CRUISE now logically exists twice and can be referenced as either A or B.

You can also use a correlation identifier if you want to use a shorter qualifier within the statement.