Micro Focus Enterprise Developer 3.0 for Eclipse > Programming > Mainframe Programming > SQL Option for DB2 > Reference > SQL Reference > Clauses > WHERE Clause Syntax and Description > Nested Queries

Nested Queries

Restriction: This topic applies to Windows environments only.

A nestedquery consists of two or more ordinary queries nested in such a way that the results of each inner query (subselect) are used in the comparison test for the selection clause of the next outer query (or another command statement). The three basic formats for nested queries (also called subqueries) are shown below:

WHERE expression [NOT] IN subselect
WHERE expression operator [ALL | ANY] subselect
WHERE [NOT] EXISTS subselect

where:

`expression`	Consists of any valid expression.
`subselect`	Consists of any valid subselect (ORDER BY and UNION clauses not allowed).
`operator`	Is any of the standard relational operators ( =, <>, !=, ^=, <, <=, >, >= ).
IN	Evaluates to true if at least one value retrieved by the subselect matches the predicate condition (operates the same as the =ANY predicate).
NOT IN	Evaluates to true if no matching values are retrieved by the subselect (operates the same as the !=ALL or ^=ALL predicates).
ALL	Is used with the standard relational operators ( =, >, >=, <, <=, <>, != or ^= ), and evaluates to true if every record retrieved by the subselect satisfies the condition.
ANY	Is used with the standard relational operators ( =, >, >=, <, <=, <>, != or ^= ), and evaluates to true if at least one record retrieved by the subselect satisfies the condition.
EXISTS	Evaluates as true if the subselect retrieves at least one value.
NOT EXISTS	Evaluates as true if the subselect retrieves no values.

The number of levels of subselect nesting permitted by the XDB Server is limited only by system storage. The actual number of nested queries that can be executed depends on the complexity of the queries, and the number of concurrently open queries. When accessing the XDB Server, the number of concurrently opened queries can be set with the XDB Server Configuration Utility.

During subselect execution, the innermost query is executed first, with that result determining the outcome of the comparison test in the next higher level query -- and so on for each nested query level. There are a number of comparison tests that can be performed using nested queries.

Subselects and Locations

IN Special Operator

ALL Special Operator

ANY vs. SOME Operators

Single Value Comparisons

EXISTS Special Operator

Multiple Level Nesting