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

Predicates

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:

Example

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
    WHERE EXISTS (SELECT *
                  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:

• Simple Comparison Predicates
• Compound Predicates
• BETWEEN Predicate
• IN Predicate
• NULL Predicate
• LIKE Predicate
  • SOUNDS LIKE
  • SPELLED LIKE
• MATCHES Predicate
  • Enabling and Disabling Text Analytics Functionality
  • Command Syntax for Text Analytics
  • Text Search Types
  • Filtering Text Searches on Relevance ("Bag of Words" Search)
  • Behavioral Notes
• Quantified Comparisons