Using EXISTS

Go Up to Using Comparison Operators in Expressions

EXISTS tests that for a given value there is at least one qualifying row meeting the search condition specified in a subquery. To select all columns, the SELECT clause in the subquery must use the * (asterisk). The complete syntax for EXISTS is:

[NOT] EXISTS (SELECT * FROM <tablelist> WHERE <search_condition>)

The following cursor retrieves all countries with rivers:

EXEC SQL
DECLARE RIVER_COUNTRIES CURSOR FOR
SELECT COUNTRY
FROM COUNTRIES C
WHERE EXISTS (SELECT * FROM RIVERS R
WHERE R.COUNTRY = C.COUNTRY);

Use NOT EXISTS to retrieve rows that do not meet the qualifying condition specified in the subquery. The following cursor retrieves all countries without rivers:

EXEC SQL
DECLARE NON_RIVER_COUNTRIES COUNTRIES FOR
SELECT COUNTRY
FROM COUNTRIES C
WHERE NOT EXISTS (SELECT * FROM RIVERS R
WHERE R.COUNTRY = C.COUNTRY);

EXISTS always returns either True or False, even when handling NULL values.

For more information about subqueries, see Using Subqueries.

Advance To:

• Using SINGULAR