# Relationships, semi-joins, and anti-joins (/docs/framework/guides/relationships)



<SalesforceReference title="Relationship queries" href="https://developer.salesforce.com/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-relationships.html" />

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 paths [#child-to-parent-paths]

```ts
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 subqueries [#parent-to-child-subqueries]

Use `selectSubquery()` with the child relationship name:

```ts
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 results [#read-child-results]

```ts
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-joins [#semi-joins-and-anti-joins]

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

```ts
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 limits [#query-wide-relationship-limits]

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](/docs/core/reference/limitations) for context-specific restrictions.