In all of the other nested query examples in this reference, the subqueries have been evaluated completely before performing the outer query. In some cases, however, it is necessary to test the subselect against each row in the outer query. This type of query is known as a correlatedsubquery.
The correlated subquery is executed once for each row retrieved by the outer query. One or more tables in the outer level query are given a correlation name, which is used to prefix the correlated columns located in the subqueries. The correlation name is an alternate name that is defined immediately after the corresponding table name or view name in the SELECT statement of the outer level query.
One common use of correlated subqueries is to test for existence. The following query first performs the subquery to qualify each SUPPLIER table record by testing whether or not there is a part made in the city where the supplier is located:
SELECT * FROM supplier x WHERE EXISTS (SELECT * FROM part WHERE city = x.city)
The correlation name "x" tells the system that the column name "x.city" is actually referring to the CITY column of the SUPPLIER table that appears in the outer level query. The results of this query are as follows:
| sno | sname | status | city |
|---|---|---|---|
| S1 | SMITH | 20 | LONDON |
| S2 | JONES | 10 | PARIS |
| S3 | BLAKE | 30 | PARIS |
| S4 | CLARK | 20 | LONDON |
The next sample query finds all employees with a pay rate greater than the average pay rate for the department they work in:
SELECT dept, fname, lname, payrate
FROM employee e
WHERE payrate >
(SELECT AVG(payrate)
FROM employee
WHERE dept = e.dept)
In this example, both the inner and outer SELECT command statements query the EMPLOYEE table. The correlation name "e" tells the system that the column name "e.dept" comes from the outer level EMPLOYEE table.