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, PSQL tests for the existence of a related row from the subquery. PSQL excludes from the statement result table each row from the outer query that corresponds to a related row from the subquery.

By combining NOT with the IN operator, you can test whether the result of the outer query is not included in the result of the subquery. The result table 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 use IN:

SELECT * FROM Person p WHERE p.id NOT IN

(SELECT s.id FROM Student s WHERE Cumulative_Hours = 0)