Simple Subqueries
Go Up to Using Subqueries (Embedded SQL Guides)
A subquery is especially useful for extracting data from a single table when a self-join is inadequate. For example, it is impossible to retrieve a list of those countries with a larger than average area by joining the COUNTRIES
table to itself. A subquery, however, can easily return that information.
EXEC SQL DECLARE LARGECOUNTRIES CURSOR FOR SELECT COUNTRY, AREA FROM COUNTRIES WHERE AREA > (SELECT AVG(AREA) FROM COUNTRIES); ORDER BY AREA;
In this example, both the query and subquery refer to the same table. Queries and subqueries can refer to different tables, too. For example, the following query refers to the CITIES
table, and includes a subquery that refers to the COUNTRIES
table:
EXEC SQL DECLARE EUROCAPPOP CURSOR FOR SELECT CIT.CITY, CIT.POPULATION FROM CITIES CIT WHERE CIT.CITY IN (SELECT COU.CAPITAL FROM COUNTRIES COU WHERE COU.CONTINENT = 'Europe') ORDER BY CIT.CITY;
This example uses correlation names to distinguish between tables even though the query and subquery reference separate tables. Correlation names are only necessary when both a query and subquery refer to the same tables and those tables share column names, but it is good programming practice to use them. For more information about using correlation names, see Declaring and Using Correlation Names.