Usage notes on subqueries

 

Here are some considerations for using subqueries to refine your search conditions.

  1. When nesting SELECT statements, you can use as many as satisfy your requirements (1 to 255 subqueries), although performance is slower for each additional subquery.

  2. For predicates that use the keywords ALL, ANY, SOME, or EXISTS, the number of rows returned from the subquery can vary from zero to many. For all other subqueries, the number of rows returned must be zero or one.

  3. For the following predicates, a row fullselect can be used for the subquery. This means that the subquery can return more than one value for a row.

    • Basic predicate with equal or not equal comparisons

    • Quantified predicates using =ANY, =ALL, and =SOME

    • IN and NOT IN predicates
    If a row fullselect is used:

    • The select list must not contain SELECT *. Explicit values must be specified.

    • A row fullselect must be compared to a row expression. A row expression is a list of values enclosed in parentheses. There must be the same number of values returned from the subquery as there are in the row expression.

    • The row expression for an IN or NOT IN predicate cannot contain an untyped parameter marker. Use CAST to supply a result data type for these parameter markers.

    • The subquery cannot contain UNION, EXCEPT, or INTERSECT or a correlated reference.

  4. A subquery cannot include the ORDER BY, FOR READ ONLY, FETCH FIRST n ROWS, UPDATE, or OPTIMIZE clause.

 

Parent topic:

Subqueries in SELECT statements