Subquery processing and transformations

Subqueries are notoriously expensive to evaluate. This section describes some of the transformations that Derby makes internally to reduce the cost of evaluating them.

• Materialization
  Materialization means that a subquery is evaluated only once. Several types of subqueries can be materialized.
• Flattening a subquery into a normal join
  Subqueries are allowed to return more than one row when used with IN, EXISTS, and ANY. However, for each row returned in the outer row, Derby evaluates the subquery until it returns one row; it does not evaluate the subquery for all rows returned.
• Flattening a subquery into an EXISTS join
  An EXISTS join is a join in which the right side of the join needs to be probed only once for each outer row.
• Flattening VALUES subqueries
  Derby flattens VALUES subqueries to improve performance.
• DISTINCT elimination in IN, ANY, and EXISTS subqueries
  An IN, ANY, or EXISTS subquery evaluates to true if there is at least one row that causes the subquery to evaluate to true. These semantics make a DISTINCT within an IN, ANY, or EXISTS subquery unnecessary.
• IN/ANY subquery transformation
  An IN or ANY subquery that is guaranteed to return at most one row can be transformed into an equivalent expression subquery (a scalar subquery without the IN or ANY). The subquery must not be correlated.