Relationships, semi-joins, and anti-joins

SOQL relationships are not SQL joins. Use dotted paths to move from a child record to a parent, relationship subqueries to select children, and semi-joins or anti-joins when related records should decide whether a root record matches.

Generate every object you traverse in the examples below.

Child-to-parent pathsCopy Anchor Link

import { db } from "./db";

const contacts = await db
  .selectFrom("Contact")
  .select(["Id", "LastName", "Account.Name"])
  .where("Account.Name", "like", "Acme%")
  .orderBy("Account.Name")
  .limit(25)
  .execute();

for (const contact of contacts) {
  console.log(contact.LastName, contact.Account?.Name);
}

Use Salesforce relationship API names. For a custom lookup, that often means a relationship such as Account__r rather than the foreign-key field Account__c.

A nullable lookup makes the nested parent value nullable in the result type. Filtering on a parent field does not generally remove that nullability from the selected relationship type.

Kysoql supports up to five child-to-parent relationship levels.

Parent-to-child subqueriesCopy Anchor Link

Use selectSubquery() with the child relationship name:

import { db } from "./db";

const query = db
  .selectFrom("Account")
  .select(["Id", "Name"])
  .selectSubquery("Contacts", (contacts) =>
    contacts
      .select(["Id", "LastName", "CreatedBy.Alias"])
      .where("LastName", "like", "A%")
      .orderBy("LastName")
      .limit(10),
  )
  .limit(25);

Contacts is the relationship name on Account. Guessing the object name Contact is not equivalent.

The child builder supports normal selections, filters, sorting, limits, composition helpers, and further child subqueries where Salesforce allows them. Root-only features such as direct execution and semi-joins are not exposed there.

Read child resultsCopy Anchor Link

import { db } from "./db";

const accounts = await db
  .selectFrom("Account")
  .select("Id")
  .selectSubquery("Contacts", (contacts) =>
    contacts.select(["Id", "LastName"]).limit(10),
  )
  .limit(25)
  .execute();

for (const account of accounts) {
  for (const contact of account.Contacts.records) {
    console.log(contact.Id, contact.LastName);
  }
}

The relationship value uses SalesforceQueryResult<Row> with totalSize, done, and records.

With @kysoql/rest, selected child relationship pages are completed automatically up to the child query's LIMIT. You do not need to follow nextRecordsUrl yourself.

The shared core type still includes an optional locator because raw Salesforce responses and other executors can expose one. The JSforce adapter follows root pagination only.

For very large child collections, a direct query against the child object can be easier to stream and control than an unbounded relationship subquery.

Semi-joins and anti-joinsCopy Anchor Link

Use in to keep records that have a related match and not in to exclude them:

import { db } from "./db";

const accountsWithOpenOpportunities = db
  .selectFrom("Account")
  .select(["Id", "Name"])
  .where("Id", "in", (subquery) =>
    subquery
      .selectFrom("Opportunity")
      .select("AccountId")
      .where("IsClosed", "=", false),
  );

const accountsWithoutContacts = db
  .selectFrom("Account")
  .select(["Id", "Name"])
  .where("Id", "not in", (subquery) =>
    subquery.selectFrom("Contact").select("AccountId"),
  );

The outer field and the subquery's selected field must be compatible Salesforce ID or reference fields. Kysoql uses generated referenceTo metadata instead of assuming every ID-shaped string is interchangeable.

A semi-join changes which root records match. It does not add the child records to the returned result.

Salesforce also places tight restrictions on these subqueries. Kysoql enforces the main ones, including:

• at most two semi-joins or anti-joins per query
• top-level WHERE placement rather than inside OR or NOT
• no nested semi-joins
• no ordering, limit, or offset inside the semi-join subquery
• exactly one compatible direct ID or reference field selected by the subquery
• no semi-joins inside relationship subqueries or HAVING

Use not in for the anti-join form instead of wrapping an in semi-join with eb.not().

Query-wide relationship limitsCopy Anchor Link

Kysoql counts relationships across the whole query. The compiler allows at most 20 parent-to-child relationships and 55 child-to-parent relationships.

Reusing the same parent path does not count it again, while traversing a deeper relationship adds another level. TYPEOF targets also contribute to relationship accounting.

These compiler checks do not guarantee that every transport and Salesforce object supports the same maximum depth. See support boundaries and limits for context-specific restrictions.