NOT

---

Remarks

Using the NOT keyword with the EXISTS keyword allows you to test whether rows do not exist in the result of the subquery. For every row the outer query evaluates, Pervasive.SQL tests for the existence of a related row from the subquery. Pervasive.SQL excludes from the statement's result table each row from the outer query that corresponds to a related row from the subquery.

Including the NOT keyword along with the IN operator allows you to test whether the result of the outer query is not included in the result of the subquery. The result table for the statement includes only rows the outer query returns that do not have a related row from the subquery.

Examples

The following statement returns a list of students who are not enrolled in any classes:

SELECT * FROM Person p WHERE NOT EXISTS 
(SELECT * FROM Student s WHERE s.id = p.id 
AND Cumulative_Hours = 0) 

This statement can be rewritten to include IN:

SELECT * FROM Person p WHERE p.id NOT IN 
(SELECT s.id FROM Student s WHERE Cumulative_Hours = 0) 

See Also

SELECT