SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-10
Correlation Names
Correlation Names
A correlation name is a name you can associate with a table reference that is a table,
view, or subquery in a SELECT statement to:
•
Distinguish a table or view from another table or view referred to in a statement
•
Distinguish different uses of the same table
•
Make the query shorter
A correlation name can be explicit or implicit.
Explicit Correlation Names
An explicit correlation name for a table reference is an SQL identifier associated with
the table reference in the FROM clause of a SELECT statement. The correlation name
must be unique within the FROM clause. For more information about the FROM
clause, table references, and correlation names, see SELECT Statement on
page 2-174.
The syntax of a correlation name for the different forms of a table reference within a
FROM clause is the same:
{table | view | (query-expression)} [AS]correlation-name
A table or view is optionally followed by the AS keyword and the correlation name. A
derived table, resulting from the evaluation of a query expression, must be followed by
the AS keyword and the correlation name. An explicit correlation name is known only
to the statement in which you define it. You can use the same identifier as a correlation
name in another statement.
Implicit Correlation Names
A table or view reference that has no explicit correlation name has an implicit
correlation name. The implicit correlation name is the table or view name qualified with
the catalog and schema names.
You cannot use an implicit correlation name for a reference that has an explicit
correlation name within the statement.
Examples of Correlation Names
•
This query refers to two tables (ORDERS and CUSTOMER) that contain columns
named CUSTNUM. In the WHERE clause, one column reference is qualified by an
implicit correlation name (ORDERS) and the other by an explicit correlation name
(C):
SELECT ordernum, custname
FROM orders, customer c
WHERE orders.custnum = c.custnum
AND orders.custnum = 543;