Correlated Subqueries
Go Up to Using Subqueries (Embedded SQL Guides)
A correlated subquery is a subquery that depends on its parent query for the values it evaluates. Because each row evaluated by the parent query is potentially different, the subquery is executed once for each row presented to it by the parent query.
For example, the following query lists each country for which there are three or more cities stored in the CITIES table. For each row in the COUNTRIES table, a country name is retrieved in the parent query, then used in the comparison operation in the subquery’s WHERE clause to verify if a city in the CITIES table should be counted by the COUNT() function. If COUNT() exceeds 2 for a row, the row is retrieved.
EXEC SQL DECLARE TRICITIES CURSOR FOR SELECT COUNTRY FROM COUNTRIES COU WHERE 3 <= (SELECT COUNT (*) FROM CITIES CIT WHERE CIT.CITY = COU.CAPITAL);
Simple and correlated subqueries can be nested and mixed to build complex queries. For example, the following query retrieves the country name, capital city, and largest city of countries whose areas are larger than the average area of countries that have at least one city within 30 meters of sea level:
EXEC SQL DECLARE SEACOUNTRIES CURSOR FOR SELECT CO1.COUNTRY, C01.CAPITAL, CI1.CITY FROM COUNTRIES C01, CITIES CI1 WHERE CO1.COUNTRY = CI1.COUNTRY AND CI1.POPULATION = (SELECT MAX(CI2.POPULATION) FROM CITIES CI2 WHERE CI2.COUNTRY = CI1.COUNTRY) AND CO1.AREA > (SELECT AVG (CO2.AREA) FROM COUNTRIES C02 WHERE EXISTS (SELECT * FROM CITIES CI3 WHERE CI3.COUNTRY = CO2.COUNTRY AND CI3.ALTITUDE <= 30));
When a table is separately searched by queries and subqueries, as in this example, each invocation of the table must establish a separate correlation name for the table. Using correlation names is the only method to assure that column references are associated with appropriate instances of their tables. For more information about correlation names, see Declaring and Using Correlation Names.
An indexed retrieval is now used to fetch rows from the correlated subquery in the UPDATE statement if there is an appropriate index defined. Utilize an indexed access path for correlated subqueries in UPDATE statements as in the following code example:
UPDATE A SET A.C1 = (SELECT B.C1 FROM B WHERE B.C2 = A.C2)
Where index is B.C2, InterBase will use index to retrieve the matching row in table B where B.C2 = A.C2, since the row in the outer table A has already been fetched.