Subqueries in WHERE clauses

This section describes subqueries that occur as a SELECT statement that is nested in the WHERE clause of another SELECT statement.

You can use any relational operator with ALL and ANY to compare something to every one of (ALL) or to any one of (ANY) the values that the subquery produces. You can use the keyword SOME in place of ANY. The operator IN is equivalent to = ANY. To create the opposite search condition, use the keyword NOT or a different relational operator.

The EXISTS operator tests a subquery to see if it found any values; that is, it asks if the result of the subquery is not null. You cannot use the EXISTS keyword in a subquery that contains a column with a TEXT or BYTE data type.

For the syntax that you use to create a condition with a subquery, see the HCL OneDB™ Guide to SQL: Syntax.

The following keywords introduce a subquery in the WHERE clause of a SELECT statement.