To avoid repeatedly typing a full table name, a substitute correlation name for the same table may be designated, if desired. For example, to avoid repeating the table name qualifiers "supplier" and "partsupp" throughout the query, single letter correlation names can be assigned to replace the original table names henceforth in the same query.
Correlation names are designated in the FROM clause (immediately following the table name, and separated with a space) to serve as substitutes for qualifying table names later on in the WHERE clause. For example, to display the supplier number and city of all vendors who provide part "P2", the correlation names "p" and "s" can be used as qualifying prefixes instead of the table names (SUPPLIER and PARTSUPP).
SELECT sno, city
FROM partsupp p, supplier s
WHERE p.sno = s.sno
AND pno = "P2"
Notice the correlation names appearing as column name prefixes in the WHERE clause join condition above.
It is possible to join a table to itself, if a correlation name is used to qualify each instance of the table appearing in the WHERE clause. For example, consider a table named personnel containing the columns staff_id, salary, and super_id. The result of the self-join query shown next displays all staff members who earn more than their supervisors:
SELECT a.staff_id
FROM personnel a, personnel b
WHERE a.salary > b.salary
AND a.super_id = b.staff_id