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.