SAND CDBMS SQL Reference Guide
Selection Criteria for WHERE and HAVING Clauses



The EXISTS Predicate

The [NOT] EXISTS predicate tests a subquery for the presence of a value. If the subquery returns at least one value, the EXISTS predicate evaluates to TRUE, while a NOT EXISTS predicate evaluates to FALSE. Its syntax is as follows:


The following query retrieves the full name of employees who recorded individual sales of over $10,000:

SELECT E.lname, E.fname
  FROM employee E
                  FROM sales S
                  WHERE E.e_no = S.e_no
                  AND S.amount > 10000);

The EXISTS clause tests whether the subquery result set is not empty for each record in the employee table. Each sales table record is checked to see if the sales e_no matches the current employee e_no, and also that the sales amount is greater than 10,000. If at least one such record is returned from the sales table, then the EXISTS clause evaluates to TRUE for the current employee record, and the selected columns of that employee record are included in the result set.


Other Predicates: