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