Skip to main content

Subqueries

A subquery (or inner query) is a complete FOQL select statement nested inside the where clause of an outer query. It appears on the right-hand side of an in / not in predicate and returns a single column of values; the outer query keeps only the records whose field matches that set.

Subqueries let you filter records in one module by a condition that lives in a related module — without first fetching those related ids in a separate call. The inner query computes the set of matching ids, and the outer query filters against it in the same request.

Where subqueries are allowed#

A subquery may be used only as the value of an in or not in predicate:

field_path in ( <subquery> )field_path not in ( <subquery> )

The left operand (field_path) is compared against every value the subquery returns. It is typically a lookup field on the outer module, so the outer records are filtered by a condition on the module the lookup points to. See Lookup Traversal.

Syntax#

The subquery extends the in predicate defined in Syntax & Grammar. Instead of a literal value list, the parentheses contain a nested select:

predicate   ::= ...              | field_path [ "not" ] "in" "(" value_list ")"              | field_path [ "not" ] "in" "(" subquery ")" ;
subquery    ::= "select" field_path                "from" module_name                [ where_clause ]                [ limit_clause ]                [ offset_clause ] ;

The subquery is itself a valid FOQL query and follows all the same rules — it has its own from module and its own optional where, limit, and offset. The one restriction is that its select list must contain exactly one field (the column whose values are matched against the outer field).

Example#

Return the id and the parent work order's subject for the first 5 work order items whose work order has Medium priority:

select id, workorder.subjectfrom workorderItemwhere (workorder in ( select id from workorder where priority = 'Medium' ))limit 5

How it resolves#

PartRole
from workorderItemOuter (base) module — the records returned.
select id, workorder.subjectOuter select list: the item id, plus a lookup traversal into the related workorder for its subject.
workorder in ( ... )Predicate on the workorder lookup field of workorderItem; keeps items whose workorder id is in the subquery result.
select id from workorderSubquery select list — a single field (id), the value set matched against workorder.
where priority = 'Medium'Subquery filter — restricts the returned work order ids to those with Medium priority.
limit 5Applies to the outer query; caps the returned work order items at 5.

Read it inside-out: the subquery first produces the set of workorder ids whose priority is 'Medium'; the outer query then returns work order items whose workorder lookup falls in that set.

Response#

{  "data": [    { "id": 9001, "workorder.subject": "Quarterly HVAC inspection" },    { "id": 9002, "workorder.subject": "Replace air filter" },    { "id": 9003, "workorder.subject": "Chiller pressure check" }  ]}

Rules#

Subquery constraints
  • A subquery is valid only as the right-hand side of an in / not in predicate.
  • The subquery's select list must contain exactly one field. Selecting more than one column, or an aggregate, is rejected.
  • The subquery's selected field must be type-compatible with the outer field it is compared against (for example, an id column matched against a lookup field).
  • One level of nesting. A subquery cannot itself contain another subquery.
  • The subquery is a full FOQL query and is bound by every limit and validation in its own right (where conditions, joins, record cap, and so on).
tip

Because the outer left operand is usually a lookup field, subqueries pair naturally with lookup filtering. When you only need to filter by the related record's id — and already know it — a plain where vendor = 12345 (see Field Types & Comparators) is simpler than a subquery. Reach for a subquery when the related records are selected by a condition rather than by known ids.

Related#

On This Topic