ANY / IN Predicate (Subquery)

An ANY predicate compares two expressions using a comparison operator. The predicate evaluates to TRUE if the first expression relates to anyrow returned by the subquery as specified by the comparison operator. Similarly, the IN predicate compares an expression or list of expressions with a table subquery. The IN predicate evaluates to TRUE if the expression or list of expressions is equal to a value returned by a subquery.

SQL syntax

RowValueConstructor {CompOp ANY | IN} (Subquery)

The syntax for RowValueConstructor:

RowValueConstructorElement | (RowValueConstructorList) | Subquery

The syntax for RowValueConstructorList:

RowValueConstructorElement[{, RowValueConstructorElement} ... ]

The syntax for RowValueConstructorElement:

Expression | NULL

The syntax for CompOp:

{= | <> | > | >= | < | <= }

Parameters

Component	Description
Expression	See "Expression Specification" for information on syntax. Both numeric and non-numeric expressions are allowed for ANY predicates, but both expression types must be compatible with each other.
=	Is equal to.
<>	Is not equal to.
>	Is greater than.
>=	Is greater than or equal to.
<	Is less than.
<=	Is less than or equal to.
Subquery	See "Subqueries" for information on the syntax for subqueries.

Description

The ANY predicate, which returns zero or more rows, uses a comparison operator modified with the keyword ANY. See "Numeric Data Types" for information about how TimesTen compares values of different but compatible types.

Examples

This example retrieves a list of customers having at least one unshipped order:

SELECT customers.name FROM customers 
WHERE customers.id = ANY 
(SELECT orders.custid FROM orders 
WHERE orders.status = 'unshipped');

This is an example of an IN predicate with subquery. It SELECTs customers having at least one unshipped order:

SELECT customers.name FROM customers 
WHERE customers.id IN 
(SELECT orders.custid FROM orders 
WHERE orders.status = 'unshipped');

This example uses an aggregate query that specifies a subquery with IN to find the maximum price of a book in the exclBookList:

SELECT MAX(price) FROM books WHERE id IN (SELECT id FROM exclBookList);

This example illustrates the use of a list of expressions with the IN predicate and a subquery.

SELECT * FROM t1 WHERE (x1,y1) IN (SELECT x2,y2 FROM t2);

This example illustrates the use of a list of expressions with the ANY predicate and a subquery.

SELECT * FROM t1 WHERE (x1,y1) < ANY (SELECT x2,y2 FROM t2);

The following example illustrates the use of a list of expressions with the ANY predicate.

Command> columnlabels on;
Command> SELECT * FROM t1;
X1, Y1
< 1, 2 >
< 3, 4 >
2 rows found.
Command> SELECT * FROM t2;
X2, Y2
< 3, 4 >
< 1, 2 >
2 rows found.