2. Overview of OpenSQL : OpenSQL Features : Correlation Names
 
Share this page                  
Correlation Names
Correlation names are used in queries to clarify the table (or view) to which a column belongs. For example, the following query uses correlation names to join a table with itself:
select a.empname from emp a, emp b
  where a.mgrname = b.empname
  and a.salary > b.salary;
Correlation names can also be used to abbreviate long table names.
Specify correlation names in select statements. A single query can reference a maximum of 126 correlation and table names (including all base tables referenced by views specified in a query).
Note:  The maximum number of tables referenced in a single query is dependent on the host DBMS. The 126 maximum listed here is for the Ingres DBMS; other DBMSs supported by Enterprise Access and EDBC may have a higher or lower limit.
If a correlation name is not specified, the table name implicitly becomes the correlation name. For example, in the following query:
select * from employee 
  where salary > 100000;
OpenSQL assumes the correlation name, employee, for the salary column and interprets the preceding query as:
select * from employee 
  where employee.salary > 100000;
If a correlation name is specified for a table, the correlation name (and not the actual table name) must be used within the query. For example, the following query generates a syntax error:
/*incorrect*/
select * from employee e
where employee.salary > 35000;
A correlation name must be unique. For example, the following statement is illegal because the same correlation name is specified for different tables:
/*incorrect*/
select e.ename from employee e, manager e 
  where e.dept = e.dept;
A correlation name that is the same as a table that you own cannot be specified. If you own a table called mytable, the following query is illegal:
select * from othertable mytable...;
In nested queries, OpenSQL resolves unqualified column names by checking the tables specified in the nearest from clause, then the from clause at the next higher level, and so on, until all table references are resolved.
For example, in the following query, the dno column belongs to the deptsal table, and the dept column to the employee table:
select ename from employee
  where salary >
    (select avg(salary) from deptsal
     where dno = dept);
Because the columns are specified without correlation names, OpenSQL performs the following steps to determine to which table the columns belong:
Column
Action
dno
OpenSQL checks the table specified in the nearest from clause (the deptsal table). The dno column does belong to the deptsal table. OpenSQL interprets the column specification as deptsal.dno.
dept
OpenSQL checks the table specified in the nearest from clause (deptsal). The dept column does not belong to the deptsal table.
OpenSQL checks the table specified in the from clause at the next higher level (the employee table). The dept column does belong to the employee table. OpenSQL interprets the column specification as employee.dept.
OpenSQL does not search across subqueries at the same level to resolve unqualified column names. For example, given the query:
select * from employee
where 
  dept = (select dept from sales_departments 
      where mgrno=manager)
   or
   dept = (select dept from mktg_departments 
      where mgrno=manager_id);
OpenSQL checks the description of the sales_departments table for the mgrno and manager columns. If they are not found, OpenSQL checks the employee table next, but will not check the mktg_departments table. Similarly, OpenSQL first checks the mktg_departments table for the mgrno and manager_id columns. If they are not found, OpenSQL will check the employee table, but will never check the sales_departments table.