ALL / NOT IN Predicate (Subquery)

The ALL or NOT IN predicate indicates that the operands on the left side of the comparison must compare in the same way with all of the values that the subquery returns. The ALL predicate evaluates to TRUE if the expression or list of expressions relates to all rows returned by the subquery as specified by the comparison operator. Similarly, the NOT IN predicate evaluates to TRUE if the expression or list of expressions does not equal the value returned by the subquery.

SQL syntax

RowValueConstructor {CompOp ALL | NOT IN} (Subquery)

The syntax for RowValueConstructor:

RowValueConstructorElement | (RowValueConstuctorList) | 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 the syntax. Both numeric and non-numeric expressions are allowed for `ALL` 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 the syntax.

Description

The ALL predicate, which returns zero or more rows, uses a comparison operator modified with the keyword ALL. See "Numeric Data Types" for information about how TimesTen compares values of different but compatible types.
If RowValueConstructorList is specified only the operators = and <> are allowed.

Examples

Examples of NOT IN with subqueries:

SELECT * FROM customers 
WHERE cid NOT IN
(SELECT cust_id FROM returns)
AND cid > 5000;

SELECT * FROM customers 
WHERE cid NOT IN
(SELECT cust_id FROM returns)
AND cid NOT IN
(SELECT cust_id FROM complaints);

SELECT COUNT(*) From customers 
WHERE cid NOT IN
(SELECT cust_id FROM returns)
AND cid NOT IN
(SELECT cust_id FROM complaints);

Select all books that are not from exclBookList or if the price of the book is higher than $20.

SELECT * FROM books 
 WHERE id NOT IN (SELECT id FROM exclBookList) OR books.price>20;

The following query returns the employee_id and job_id from the job_history table. It illustrates use of expression list and subquery with the NOT IN predicate.

Command> SELECT employee_id, job_id FROM job_history 
         WHERE (employee_id, job_id)
         NOT IN (SELECT employee_id, job_id FROM employees);
< 101, AC_ACCOUNT >
< 101, AC_MGR >
< 102, IT_PROG >
< 114, ST_CLERK >
< 122, ST_CLERK >
< 176, SA_MAN >
< 200, AC_ACCOUNT >
< 201, MK_REP >
8 rows found.