Column name qualifiers to avoid ambiguity

In the context of a function, a GROUP BY clause, an ORDER BY clause, an expression, or a search condition, a column name refers to values of a column in some target table or view in a DELETE or UPDATE statement or table-reference in a FROM clause. The tables, views, and table-references 31 that might contain the column are called the object tables of the context. Two or more object tables might contain columns with the same name. One reason for qualifying a column name is to designate the object from which the column comes. For information on avoiding ambiguity between SQL parameters and variables and column names, see References to SQL parameters and SQL variables.

A nested table expression which is preceded by a LATERAL or TABLE keyword will consider table-references that precede it in the FROM clause as object tables. The table-references that follow the nested table expression are not considered as object tables.

Table designators

A qualifier that designates a specific object table is called a table designator. The clause that identifies the object tables also establishes the table designators for them. For example, the object tables of an expression in a SELECT clause are named in the FROM clause that follows it:

   SELECT CORZ.COLA, OWNY.MYTABLE.COLA
     FROM OWNX.MYTABLE CORZ, OWNY.MYTABLE

Table designators in the FROM clause are established as follows:

Two or more object tables can be instances of the same table. In this case, distinct correlation names must be used to unambiguously designate the particular instances of the table. In the following FROM clause, X and Y are defined to refer, respectively, to the first and second instances of the table EMPLOYEE:

  SELECT * FROM EMPLOYEE X,EMPLOYEE Y

Avoiding undefined or ambiguous references

When a column name refers to values of a column, it must be possible to resolve that column name to exactly one object table. The following situations are considered errors:

Avoid ambiguous references by qualifying a column name with a uniquely defined table designator. If the column is contained in several object tables with different names, the object table names can be used as designators. Ambiguous references can also be avoided without the use of the table designator by giving unique names to the columns of one of the object tables using the column name list following the correlation name.

When qualifying a column with the exposed table name form of a table designator, either the qualified or unqualified form of the exposed table name may be used. However, the qualifier used and the table used must be the same after fully qualifying the table name or view name and the table designator.

  1. If the authorization ID of the statement is CORPDATA, then:
       SELECT CORPDATA.EMPLOYEE.WORKDEPT
         FROM EMPLOYEE
    is a valid statement.
  2. If the authorization ID of the statement is REGION, then:
       SELECT CORPDATA.EMPLOYEE.WORKDEPT
         FROM EMPLOYEE                             ***INCORRECT***
    is invalid, because EMPLOYEE represents the table REGION.EMPLOYEE, but the qualifier for WORKDEPT represents a different table, CORPDATA.EMPLOYEE.
  3. If the authorization ID of the statement is REGION, then:
       SELECT EMPLOYEE.WORKDEPT
         FROM CORPDATA.EMPLOYEE                             ***INCORRECT***
    is invalid, because EMPLOYEE in the select list represents the table REGION.EMPLOYEE, but the explicitly qualified table name in the FROM clause represents a different table, CORPDATA.EMPLOYEE. In this case, either omit the table qualifier in the select list, or define a correlation name for the table designator in the FROM clause and use that correlation name as the qualifier for column names in the statement.

31.
In the case of a joined-table, each table-reference within the joined-table is an object table.