EXISTS

The EXISTS keyword tests whether rows exist in the result of a subquery. True is returned if the subquery contains any rows.

Syntax

EXISTS ( subquery ) 

Remarks

For every row the outer query evaluates, Pervasive.SQL tests for the existence of a related row from the subquery. Pervasive.SQL includes in the statement's result table each row from the outer query that corresponds to a related row from the subquery.

You may use EXISTS for a subquery within a stored procedure. However, the subquery SELECT statement within the stored procedure may not contain a COMPUTE clause or the INTO keyword.

In most cases, a subquery with EXISTS can be rewritten to use IN. Pervasive.SQL can process the query more efficiently if the query uses IN.

Examples

The following statement returns a list containing only persons who have a 4.0 grade point average:

SELECT * FROM Person p WHERE EXISTS 
(SELECT * FROM Enrolls e WHERE e.Student_ID = p.id 
AND Grade = 4.0) 

This statement can be rewritten to use IN:

SELECT * FROM Person p WHERE p.id IN 
(SELECT e.Student_ID FROM Enrolls WHERE Grade = 4.0) 

The following procedure selects the ID from the Person table using a value as an input parameter. The first EXEC of the procedure returns "Exists returned true." The second EXEC returns "Exists returned false."

CREATE PROCEDURE ex1(IN :vID INTEGER) AS 
BEGIN 
IF EXISTS ('SELECT id FROM person WHERE id < :vID) 
THEN PRINT 'Exists returned true'; 
ELSE PRINT 'Exists returned false'; 
ENDIF; 
END; 

EXEC ex1(222222222) 
EXEC ex1(1) 

See Also

SELECT