Correlation names

A correlation name can be defined in the FROM clause of a query and after the target table-name or view-name in an UPDATE or DELETE statement.

For example, the clause shown below establishes Z as a correlation name for X.MYTABLE:

   FROM X.MYTABLE Z

A correlation name is associated with a table or view only within the context in which it is defined. Hence, the same correlation name can be defined for different purposes in different statements, or in different clauses of the same statement.

As a qualifier, a correlation name can be used to avoid ambiguity or to establish a correlated reference. A correlation name can also be used as a shorter name for a table or view. In the example that is shown above, Z might have been used merely to avoid having to enter X.MYTABLE more than once.

If a correlation name is specified for a table or view, any qualified reference to a column of that instance of the table or view must use the correlation name, rather than the table name or view name. For example, the reference to EMPLOYEE.PROJECT in the following example is incorrect, because a correlation name has been specified for EMPLOYEE:

   FROM EMPLOYEE E                         ***INCORRECT***
     WHERE EMPLOYEE.PROJECT='ABC'

The qualified reference to PROJECT should instead use the correlation name, “E”, as shown below:

   FROM EMPLOYEE E
     WHERE E.PROJECT='ABC'

Names specified in a FROM clause are either exposed or non-exposed. A correlation name is always an exposed name. A table name or view name is said to be exposed in that FROM clause if a correlation name is not specified. For example, in the following FROM clause, a correlation name is specified for EMPLOYEE but not for DEPARTMENT, so DEPARTMENT is an exposed name, and EMPLOYEE is not:

   FROM EMPLOYEE E, DEPARTMENT

A table name or view name that is exposed in a FROM clause must not be the same as any other table name or view name exposed in that FROM clause or any correlation name in the FROM clause. The names are compared after qualifying any unqualified table or view names.

The first two FROM clauses shown below are correct, because each one contains no more than one reference to EMPLOYEE that is exposed:

Given the FROM clause:
```
   FROM EMPLOYEE E1, EMPLOYEE
```
a qualified reference such as EMPLOYEE.PROJECT denotes a column of the second instance of EMPLOYEE in the FROM clause. A qualified reference to the first instance of EMPLOYEE must use the correlation name “E1” (E1.PROJECT).
Given the FROM clause:
```
   FROM EMPLOYEE, EMPLOYEE E2
```
a qualified reference such as EMPLOYEE.PROJECT denotes a column of the first instance of EMPLOYEE in the FROM clause. A qualified reference to the second instance of EMPLOYEE must use the correlation name “E2” (E2.PROJECT).
Given the FROM clause:
```
   FROM EMPLOYEE, EMPLOYEE                     ***INCORRECT***
```
the two exposed table names included in this clause (EMPLOYEE and EMPLOYEE) are the same, and this is not allowed.
Given the following statement:
```
   SELECT *
     FROM EMPLOYEE E1, EMPLOYEE E2             ***INCORRECT***
     WHERE EMPLOYEE.PROJECT='ABC'
```
the qualified reference EMPLOYEE.PROJECT is incorrect, because both instances of EMPLOYEE in the FROM clause have correlation names. Instead, references to PROJECT must be qualified with either correlation name (E1.PROJECT or E2.PROJECT).
Given the FROM clause:
```
   FROM EMPLOYEE, X.EMPLOYEE
```
a reference to a column in the second instance of EMPLOYEE must use X.EMPLOYEE (X.EMPLOYEE.PROJECT). This FROM clause is only valid if the authorization ID of the statement is not X.

A correlation name specified in a FROM clause must not be the same as:

Any other correlation name in that FROM clause
Any unqualified table name or view name exposed in the FROM clause
The second SQL identifier of any qualified table name or view name that is exposed in the FROM clause.

For example, the following FROM clauses are incorrect:

   FROM EMPLOYEE E, EMPLOYEE E
   FROM EMPLOYEE DEPARTMENT, DEPARTMENT            ***INCORRECT***
   FROM X.T1, EMPLOYEE T1

The following FROM clause is technically correct, though potentially confusing:

   FROM EMPLOYEE DEPARTMENT, DEPARTMENT EMPLOYEE

The use of a correlation name in the FROM clause also allows the option of specifying a list of column names to be associated with the columns of the result table. As with a correlation name, these listed column names become the exposed names of the columns that must be used for references to the columns throughout the query. If a column name list is specified, then the column names of the underlying table become non-exposed.

Given the FROM clause:

   FROM DEPARTMENT D (NUM,NAME,MGR,ANUM,LOC)

a qualified reference such as D.NUM denotes the first column of the DEPARTMENT table that is defined in the table as DEPTNO. A reference to D.DEPTNO using this FROM clause is incorrect since the column name DEPTNO is a non-exposed column name.

If a list of columns is specified, it must consist of as many names as there are columns in the table-reference. Each column name must be unique and unqualified.