Declaring and Using Correlation Names

Go Up to Listing Tables to Search with FROM

A correlation name, or alias, is a temporary variable that represents a table name. It can contain up to 31 alphanumeric characters, dollar signs ($), and underscores (_), but must always start with an alphabetic character. Using brief correlation names reduces typing of long queries. Correlation names must be substituted for actual table names in joins, and can be substituted for them in complex queries.

A correlation name is associated with a table in the FROM clause; it replaces table names to qualify column names everywhere else in the statement. For example, to associate the correlation name, DEPT with the DEPARTMENT table, and EMP, with the EMPLOYEES table, a FROM clause might appear as:

FROM DEPARTMENT DEPT, EMPLOYEE EMP

Like an actual table name, a correlation name is used to qualify column names wherever they appear in a SELECT statement. For example, the following query employs the correlation names, DEPT, and EMP, previously described:

EXEC SQL
SELECT DEPARTMENT, DEPT_NO, LAST_NAME, FIRST_NAME,
EMLOYEE.EMP_NO
INTO :dept_name, :dept_no, :lname, :fname, :empno
FROM DEPARTMENT DEPT, EMPLOYEE EMP
WHERE DEPT_NO = 'Publications' AND DEPT.EMP_NO = EMP.EMP_NO;

For more information about the SELECT clause, see Listing Columns to Retrieve with SELECT.