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 or returns no rows for NOT EXISTS.

SQL syntax

[NOT] EXISTS (Subquery)

Parameters

The EXISTS predicate has the following parameter:

Parameter Description

Subquery

See "Subqueries" for information on syntax.

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 = 'unshipped');

Get a list of customers having no unshipped orders.

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