Column name qualifiers to avoid ambiguity

Start of change 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 table or view in a DELETE, MERGE, or UPDATE statement or table-reference in a FROM clause. End of change

The tables, views, and table-references¹ 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 in external SQL procedures.

A nested table expression which is preceded by a TABLE keyword will consider table-references that precede it in the FROM clause as object tables. The table-references that follow it 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 statement are named in the FROM clause that follows it, as in the following statement:

   SELECT DISTINCT Z.EMPNO, EMPTIME, PHONENO
     FROM DSN8C10.EMP Z, DSN8C10.EMPPROJACT
     WHERE WORKDEPT = 'D11'
       AND EMPTIME > 0.5
       AND Z.EMPNO = DSN8C10.EMPPROJACT.EMPNO;

Table designators in the FROM clause are established as follows:

A name that follows a table or view name is both a correlation name and a table designator. Thus, Z is a table designator and qualifies the first column name in the select list.
An exposed table or view name is a table designator. Thus, the qualified table name, DSN8C10.EMPPROJACT is a table designator and qualifies the second column name in the select list.

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 instance of the table. In the following example, the X and Y in the FROM clause are defined to refer, respectively, to the first and second instances of the DSN8C10.EMP table:

   SELECT *
     FROM DSN8C10.EMP X, DSN8C10.EMP Y;

Avoiding undefined or ambiguous references in Db2 SQL: When a column name refers to values of a column, the following situations result in errors:

No object table contains a column with the specified name. The reference is undefined.
The column name is qualified by a table designator, but the table named does not include a column with the specified name. Again, the reference is undefined.
The name is unqualified and more than one object table includes a column with that name. The reference is ambiguous.

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 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.

Two or more object tables can be instances of the same table. A FROM clause that includes n references to the same table should include at least n - 1 unique correlation names.

For example, in the following FROM clause X and Y are defined to refer, respectively, to the first and second instances of the table EMP.

   SELECT X.LASTNAME, Y.LASTNAME
     FROM DSN8C10.EMP X, DSN8C10.EMP Y
     WHERE Y.JOB = 'MANAGER'
       AND X.WORKDEPT = Y.WORKDEPT
       AND X.JOB <> 'MANAGER';

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 can 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.

Example 1: If the authorization ID of the statement is CORPDATA, the following statement is valid:

   SELECT CORPDATA.EMPLOYEE.WORKDEPT          
     FROM EMPLOYEE;

Example 2: If the authorization ID of the statement is REGION, the following statement is invalid because EMPLOYEE represents the table REGION.EMPLOYEE, but the qualifier for WORKDEPT represents a different table, CORPDATA.EMPLOYEE:

   SELECT CORPDATA.EMPLOYEE.WORKDEPT               -- Incorrect
     FROM EMPLOYEE;

Example 3: If the authorization ID of the statement is REGION, the following statement 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.

   SELECT EMPLOYEE.WORKDEPT                       -- Incorrect
     FROM CORPDATA.EMPLOYEE;

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