Skip to content

Subqueries

Subqueries are scalar expressions comprised of another query.

Forms

Scalar

Scalar subqueries are subqueries that return one row and one column.

Property Description Required
Input Input relation Yes

IN predicate

An IN subquery predicate checks that the left expression is contained in the right subquery.

Examples

SELECT *
FROM t1
WHERE x IN (SELECT * FROM t2)

SELECT *
FROM t1
WHERE (x, y) IN (SELECT a, b FROM t2)
Property Description Required
Needles Expressions whose existence will be checked Yes
Haystack Subquery to check Yes

Set predicates

A set predicate is a predicate over a set of rows in the form of a subquery.

EXISTS and UNIQUE are common SQL spellings of these kinds of predicates.

Property Description Required
Operation The operation to perform over the set Yes
Tuples Set of tuples to check using the operation Yes

Set comparisons

A set comparison subquery is a subquery comparison using ANY or ALL operations.

Examples

SELECT *
FROM t1
WHERE x < ANY(SELECT y from t2)
Property Description Required
Reduction operation The kind of reduction to use over the subquery Yes
Comparison operation The kind of comparison operation to use Yes
Expression Left-hand side expression to check Yes
Subquery Subquery to check Yes

Outer References in Subqueries

Subqueries may contain outer references, which are field references that reach outside the subquery boundary to access records from an enclosing relation. The OuterReference root type provides two resolution fields:

  • steps_out: Resolves the reference by counting subquery boundaries upward. This works correctly when the plan is a tree (each relation has a single parent).

  • rel_reference: Resolves the reference by naming the binding relation via its plan-wide unique RelCommon.rel_anchor. Must be used instead of steps_out when an outer reference appears inside a relation shared via

ReferenceRel and that shared relation can be reached through multiple

paths with different subquery depths, making steps_out ambiguous.

Exactly one of these fields must be set. See Field References — Outer References for details.

message Subquery {
  oneof subquery_type {
    // Scalar subquery
    Scalar scalar = 1;
    // x IN y predicate
    InPredicate in_predicate = 2;
    // EXISTS/UNIQUE predicate
    SetPredicate set_predicate = 3;
    // ANY/ALL predicate
    SetComparison set_comparison = 4;
  }

  // A subquery with one row and one column. This is often an aggregate
  // though not required to be.
  message Scalar {
    Rel input = 1;
  }

  // Predicate checking that the left expression is contained in the right
  // subquery
  //
  // Examples:
  //
  // x IN (SELECT * FROM t)
  // (x, y) IN (SELECT a, b FROM t)
  message InPredicate {
    repeated Expression needles = 1;
    Rel haystack = 2;
  }

  // A predicate over a set of rows in the form of a subquery
  // EXISTS and UNIQUE are common SQL forms of this operation.
  message SetPredicate {
    enum PredicateOp {
      PREDICATE_OP_UNSPECIFIED = 0;
      PREDICATE_OP_EXISTS = 1;
      PREDICATE_OP_UNIQUE = 2;
    }
    // TODO: should allow expressions
    PredicateOp predicate_op = 1;
    Rel tuples = 2;
  }

  // A subquery comparison using ANY or ALL.
  // Examples:
  //
  // SELECT *
  // FROM t1
  // WHERE x < ANY(SELECT y from t2)
  message SetComparison {
    enum ComparisonOp {
      COMPARISON_OP_UNSPECIFIED = 0;
      COMPARISON_OP_EQ = 1;
      COMPARISON_OP_NE = 2;
      COMPARISON_OP_LT = 3;
      COMPARISON_OP_GT = 4;
      COMPARISON_OP_LE = 5;
      COMPARISON_OP_GE = 6;
    }

    enum ReductionOp {
      REDUCTION_OP_UNSPECIFIED = 0;
      REDUCTION_OP_ANY = 1;
      REDUCTION_OP_ALL = 2;
    }

    // ANY or ALL
    ReductionOp reduction_op = 1;
    // A comparison operator
    ComparisonOp comparison_op = 2;
    // left side of the expression
    Expression left = 3;
    // right side of the expression
    Rel right = 4;
  }
}