EXISTS predicate

An EXISTS predicate checks for the existence or nonexistence of a table subquery. The predicate evaluates to TRUE if the subquery returns at least one row for EXISTS and no rows for NOT EXISTS

SQL syntax

[NOT] EXISTS (Subquery)

Parameters

The EXISTS predicate has the following parameter:

Description

• When a subquery is introduced with EXISTS, the subquery functions as an existence test. EXISTS tests for the presence or absence of an empty set of rows. If the subquery returns at least one row, the subquery evaluates to true.

• When a subquery is introduced with NOT EXISTS, the subquery functions as an absence test. NOT EXISTS tests for the presence or absence of an empty set of rows. If the subquery returns no rows, the subquery evaluates to true.

• If join order is issued using the ttOptSetOrder built-in procedure that conflicts with the join ordering requirements of the NOT EXISTS subquery, the specified join order is ignored, TimesTen issues a warning and the query is executed.

• The following table describes supported and unsupported usages of EXISTS and NOT EXISTS in TimesTen;

  Query/subquery description	Not Exists	Exists
  Aggregates in subquery	Supported	Supported
  Aggregates in main query	Supported	Supported
  Subquery in OR clause	Supported	Supported
  Join ordering using the ttOptSetOrder built-in procedure	Limited support	Supported

  
  

Examples

Get a list of customers having at least one unshipped order.

SELECT customers.name FROM customers
WHERE EXISTS (SELECT 1 FROM orders 
WHERE customers.id = orders.custid 
AND orders.status = 'un-shipped');

Get a list of customers having at no unshipped orders.

SELECT customers.name FROM customers
WHERE NOT EXISTS (SELECT 1 FROM orders 
WHERE customers.id = orders.custid 
AND orders.status = 'un-shipped');