The meaning of a column name depends on its context. A column name can be used to:
A qualifier for a column name can be a table name, a view name, an alias name, or a correlation name.
Whether a column name can be qualified depends on its context:
Where a qualifier is optional it can serve two purposes. See Column Name Qualifiers to Avoid Ambiguity and Column Name Qualifiers in Correlated References for details.
A correlation name can be defined in the FROM clause of a query and in the first clause of 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, view, or alias only within the context in which it is defined. Hence, you can define the same correlation name for different purposes in different statements, or in different clauses of the same statement.
As a qualifier, you can use a correlation name to avoid ambiguity or to establish a correlated reference. You can also use a correlation name as a shorter name for a table, view, or alias. 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 name, view name or alias name, any qualified reference to a column of that instance of the table, view or alias must use the correlation name, rather than the table name, view name, or alias 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, view name, or alias 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, view name, or alias 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:
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).
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).
FROM EMPLOYEE, EMPLOYEE ***INCORRECT***
the two exposed table names included in this clause (EMPLOYEE and EMPLOYEE) are the same, and this is not allowed.
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).
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:
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.
In the context of a function, a GROUP BY clause, ORDER BY clause, an expression, or a search condition, a column name refers to values of a column in some table or view. The tables and views 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.
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
This is how you establish table designators in the FROM clause:
SELECT CORZ.COLA, MYTABLE.COLA FROM OWNX/MYTABLE CORZ, OWNY/MYTABLE
When a column name refers to values of a column, exactly one object table must include a column with that name. 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.
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 CORPDATA.EMPLOYEE:
FROM CORPDATA.EMPLOYEE X, CORPDATA.EMPLOYEE Y
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.
SELECT CORPDATA.EMPLOYEE.WORKDEPT FROM EMPLOYEE
is a valid statement.
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.
A subselect is a form of a query that can be used as a component of various SQL statements. Refer to Queries for more information on subselects. A subselect used within a search condition of any statement is called a subquery.
A subquery can include search conditions of its own, and these search conditions can, in turn, include subqueries. Therefore, an SQL statement can contain a hierarchy of subqueries. Those elements of the hierarchy that contain subqueries are said to be at a higher level than the subqueries they contain.
Every element of the hierarchy has a clause that establishes one or more table designators. This is the FROM clause, except in the highest level of an UPDATE or DELETE statement. A search condition of a subquery can reference not only columns of the tables identified by the FROM clause of its own element of the hierarchy, but also columns of tables identified at any level along the path from its own element to the highest level of the hierarchy. A reference to a column of a table identified at a higher level is called a correlated reference.
A correlated reference to column C of table T can be of the form C, T.C, or Q.C, if Q is a correlation name defined for T. However, a correlated reference in the form of an unqualified column name is not good practice. The following explanation is based on the assumption that a correlated reference is always in the form of a qualified column name and that the qualifier is a correlation name.
Q.C is a correlated reference only if these three conditions are met:
Q.C refers to column C of the table or view at the level where Q is used as the table designator of that table or view. Because the same table or view can be identified at many levels, unique correlation names are recommended as table designators. If Q is used to designate a table at more than one level, Q.C refers to the lowest level that contains the subquery that includes Q.C.
In the following statement, Q is used as a correlation name for T1 and T2, but Q.C refers to the correlation name associated with T2, because it is the lowest level that contains the subquery that includes Q.C.
SELECT *
FROM T1 Q
WHERE A < ALL (SELECT B
FROM T2 Q
WHERE B < ANY (SELECT D
FROM T3
WHERE D = Q.C))
An unqualified column name can also be a correlated reference if the column:
Unqualified correlated references are not recommended because it makes the SQL statement difficult to understand. The column will be implicitly qualified when the statement is prepared depending on which table the column was found in. Once this implicit qualification is determined it will not change until the statement is re-prepared. An SQL precompiler issues a warning message in the precompile listing and the database manager issues a positive SQLCODE (+12) and SQLSTATE (01545) when an SQL statement that has an unqualified correlated reference is prepared or executed.