A subselect used with a search condition is a subquery. A subquery is said to be at a lower level than (or nested in) the subselect that contains the search condition.
A subquery can contain search conditions of its own. Among the object tables of those search conditions are all the tables and views identified at any higher level.
Some of the higher level tables and views may be other instances of the same tables or views named in the subquery. It may be necessary to refer specifically to a column in a table or view identified at a higher level. The means for doing so is a correlated reference.
A qualified column-name, QUAL.COLUMN, is a correlated reference if, and only if, these conditions are met:
QUAL.COLUMN refers specifically to the value of column COLUMN in table (or view) QUAL at the level where QUAL is used in the FROM clause. If QUAL is used in the FROM clause of more than one level, then QUAL.COLUMN refers to the level that most directly contains the subquery that contains QUAL.COLUMN.
The correlated reference QUAL.COLUMN identifies a value of COLUMN in a row or group of QUAL to which two search conditions are being applied. The conditions are located as follows:
If condition 2 is used in a WHERE clause, the subquery is evaluated for each row to which condition 2 is applied.
If condition 2 is used in a HAVING clause, the subquery is evaluated for each group to which condition 2 is applied.
For example:
SELECT EMPNO, LASTNAME, WORKDEPT
FROM NAMETBL X
WHERE SALARY > (SELECT AVG(SALARY)
FROM NAMETBL
WHERE WORKDEPT = X.WORKDEPT)
The previous statement lists employees who make more than the average salary for their department. The first FROM clause establishes X as a correlation-name for NAMETBL. The correlated reference X.WORKDEPT (in the last line) refers to the value of WORKDEPT in table NAMETBL at the level of the first FROM clause.
|
Copyright © 2014 CA.
All rights reserved.
|
|