A correlation name is an identifier used for distinguishing multiple uses of an object. A correlation name can be defined in the FROM clause of a query and in the first clause of an UPDATE or DELETE statement. It can be associated with a table, view, or a nested table expression but only within the context that it is defined.
For example, the clause FROM STAFF S, ORG O establishes S and O as the correlation names for STAFF and ORG, respectively.
SELECT NAME, DEPTNAME
FROM STAFF S, ORG O
WHERE O.MANAGER = S.ID
Once you have defined a correlation name, you can only use the correlation name to qualify the object. For example, in the above example, if we had stated ORG.MANAGER=STAFF.ID the statement would fail.
You can also use a correlation name as a shorter name for referring to a database object. Typing just S is easier than typing STAFF.
By using correlation names, you can make duplicates of an object. This is useful when you need to compare entries of a table with itself. In the following example, table EMPLOYEE is compared with another instance of itself to find the managers of all employees. It displays the name of the employees who are not designers, the name of their manager and the department number.
SELECT E2.FIRSTNME, E2.LASTNAME, E2.JOB, E1.FIRSTNME AS MGR_FIRSTNAME,
E1.LASTNAME AS MGR_LASTNAME, E1.WORKDEPT
FROM EMPLOYEE E1, EMPLOYEE E2
WHERE E1.WORKDEPT = E2.WORKDEPT
AND E1.JOB = 'MANAGER'
AND E2.JOB <> 'MANAGER'
AND E2.JOB <> 'DESIGNER'
This statement produces the following result:
FIRSTNME LASTNAME JOB MGR_FIRSTNAME MGR_LASTNAME WORKDEPT
------------ ---------- -------- ------------- --------------- --------
DOLORES QUINTANA ANALYST SALLY KWAN C01
HEATHER NICHOLLS ANALYST SALLY KWAN C01
JAMES JEFFERSON CLERK EVA PULASKI D21
SALVATORE MARINO CLERK EVA PULASKI D21
DANIEL SMITH CLERK EVA PULASKI D21
SYBIL JOHNSON CLERK EVA PULASKI D21
MARIA PEREZ CLERK EVA PULASKI D21
ETHEL SCHNEIDER OPERATOR EILEEN HENDERSON E11
JOHN PARKER OPERATOR EILEEN HENDERSON E11
PHILIP SMITH OPERATOR EILEEN HENDERSON E11
MAUDE SETRIGHT OPERATOR EILEEN HENDERSON E11
RAMLAL MEHTA FIELDREP THEODORE SPENSER E21
WING LEE FIELDREP THEODORE SPENSER E21
JASON GOUNOT FIELDREP THEODORE SPENSER E21