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
[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);
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.