Stand-Alone Condition

A stand-alone condition can be any expression that is not explicitly listed in the syntax for the comparison condition. Such an expression is valid as a condition only if it returns a BOOLEAN value. For example, the following example returns a value of the BOOLEAN data type:

Condition with Subquery:
 
                         (1)
|--+-| EXISTS Subquery |--------------+-------------------------|
   |                 (2)              |
   +-| IN Subquery |------------------+
   |                             (3)  |
   '-| ALL, ANY, SOME Subquery |------'

Notes:

You can include a SELECT statement within a condition; this combination is called a subquery. You can use a subquery in a SELECT statement to perform the following functions:

Compare an expression to the result of another query.
Determine if an expression is included in the results of another query.
Ask whether another query selects any rows.

The subquery can depend on the current row that the outer SELECT statement is evaluating; in this case, the subquery is called a correlated subquery.

The following sections describe subquery conditions and their syntax. For a discussion of types of subquery conditions in the context of the SELECT statement, see Using a Condition in the WHERE Clause.

A subquery can return a single value, no value, or a set of values, depending on its context. If a subquery returns a value, it must select only a single column. If the subquery simply checks whether a row (or rows) exists, it can select any number of rows and columns.

A subquery cannot contain BYTE or TEXT data types, nor can it contain an ORDER BY clause. For a complete description of SELECT syntax and usage, see SELECT.