SfSkills Domains Docs GitHub

Home / apex / soql-outer-join-null-semantics

soql-outer-join-null-semantics

Use when a SOQL child-to-parent relationship query returns more records than expected because relationship queries behave like an outer join — rows with a null foreign key still come back, `WHERE Parent.Field = null` returns children even when the parent record does not exist, Boolean fields compare as false instead of null, and ORDER BY / OR clauses keep the null-foreign-key rows.

Open the skill folder on GitHub

SOQL Outer-Join & Null Semantics

This skill activates when a practitioner is surprised by a SOQL relationship query's result set: child records show up even though the lookup is empty, a WHERE Parent.Field = null filter returns rows whose parent record doesn't exist, a Boolean filter against null returns every "false" record, or Apex throws a NullPointerException while walking a parent relationship that came back on an outer-joined row. It explains the documented outer-join behavior of relationship queries and how to write filters that isolate the rows you actually want.

Before Starting

Gather this context before working on anything in this domain:

Maturity note: the SOQL and SOSL Reference documents this as standard query-language behavior. It does not stamp the behavior with a GA / Beta / Pilot maturity level — do not assert one.

Core Concepts

SOQL filters null with = null / != null, not IS NULL

SOQL has no IS NULL / IS NOT NULL operator. You compare a field to the null keyword directly:

SELECT Id FROM Event   WHERE ActivityDate != null   -- rows that have a value
SELECT Id FROM Account WHERE Test__c = null          -- rows where the field is unset

The Comparison Operators reference lists only =, !=, <, <=, >, >=, LIKE, IN, NOT IN, INCLUDES, and EXCLUDES — there is no IS NULL keyword, so a filter pasted in from SQL (WHERE Field IS NULL) is a query syntax error, not a working filter.

Relationship queries are outer joins

A child-to-parent relationship query returns the child row even when the foreign key is null. Per the reference: "Relationship SOQL queries return records, even if the relevant foreign key field has a null value, as with an outer join." So this query returns every Case, including Cases with no AccountId, and the parent columns come back null for those rows:

SELECT Id, CaseNumber, Account.Id, Account.Name
FROM Case
ORDER BY Account.Name

ORDER BY on a relationship field does not filter — "the record is returned even if the foreign key value in a record is null." A Case with an empty AccountId still appears when ordering by Account.Name.

A parent-field null check does not mean "no parent"

Testing a parent field for null returns the child row even if the parent record does not exist:

SELECT Id FROM Case WHERE Contact.LastName = null

The reference is explicit: "In a WHERE clause that checks for a value in a parent field, the record is returned even if the parent does not exist." That means this filter returns Cases whose ContactId is empty and Cases whose Contact was deleted or is otherwise unresolvable. You cannot use it to isolate one from the other.

To reliably select rows whose lookup is unset, filter the foreign-key field itself (a real field on the base object), not the traversed parent field:

-- Rows with an empty lookup:
SELECT Id FROM Case WHERE ContactId = null
-- Rows with a populated lookup:
SELECT Id FROM Case WHERE ContactId != null

Boolean fields coerce null to false

Boolean fields don't store null — per the reference, on a Boolean field "null matches FALSE values." When a Boolean lives on the outer-joined (parent) side and no matching record exists, it is treated as false. Consequently, comparing a Boolean to null is the same as comparing it to a literal:

So a Boolean null check never returns an empty set — it returns all the false (or all the true) rows. Always compare Boolean fields to true / false explicitly so the intent is unambiguous.

OR keeps the null-foreign-key rows

In a WHERE clause that uses OR, a row is returned if it satisfies any branch — even if the foreign key is null:

SELECT Id FROM Contact WHERE LastName = 'Young' OR Account.Name = 'Quarry'

A Contact with LastName = 'Young' but a null AccountId is still returned, because it matches the first branch. The relationship branch does not silently exclude parent-less rows.

Common Patterns

Isolate records with an empty lookup — filter the foreign key, not the parent field

When to use: you want "all Cases with no Account," "all Contacts not linked to an Account," etc.

How it works: filter the foreign-key Id column directly: WHERE AccountId = null (empty) or WHERE AccountId != null (populated). The FK is a scalar field on the base object, so its null test means exactly "unset."

Why not the alternative: WHERE Account.Name = null traverses the relationship and returns rows even when the parent doesn't exist, mixing "no lookup" with "unresolvable parent." It is a superset of what you asked for.

Filter Boolean fields with an explicit true/false, never null

When to use: any filter on a checkbox / Boolean field, especially one reached through a relationship.

How it works: write WHERE Active__c = false (or = true). This reads as intended and is exactly what the platform evaluates.

Why not the alternative: WHERE Active__c = null looks like an "unset" filter but is evaluated as = false, so it silently returns every inactive row. Reviewers and future maintainers misread it, and on an outer-joined parent Boolean the false-coercion widens the set further.

Add an explicit != null guard to help query performance

When to use: a WHERE clause that already constrains a field (an equality or bind-variable match) where null rows are not wanted — especially a selective lookup filter.

How it works: pair the value predicate with an explicit not-null term. The Apex Developer Guide states that "explicitly filtering out null values in the WHERE clause allows Salesforce to improve query performance," and its own example combines a bind-variable equality check with a not-null guard:

SELECT Id FROM MyObject__c WHERE Thread__c = :threadId AND Thread__c != null

Why not the alternative: returning null rows and dropping them in Apex forces the query to scan and return rows you immediately discard, and passes up the optimizer's chance to skip them. Filter them out in the WHERE clause rather than client-side.

Guard the parent relationship in Apex after an outer-join query

When to use: you iterate query results in Apex and read a parent field (c.Account.Name, child.Parent__r.Name).

How it works: because the query is an outer join, rows with a null foreign key come back with the parent relationship object set to null. Null-check the relationship before dereferencing it:

for (Case c : [SELECT Id, Account.Name FROM Case]) {
    String acctName = (c.Account != null) ? c.Account.Name : '(no account)';
    // ... use acctName
}

Why not the alternative: c.Account.Name on a Case with an empty AccountId throws a System.NullPointerException at run time — the row exists, but the parent object does not.

Decision Guidance

SituationRecommended ApproachReason
Want child rows whose lookup is emptyWHERE ForeignKeyId = null (e.g. AccountId = null)FK is a scalar field; its null test means "unset" precisely
Want child rows whose lookup is populatedWHERE ForeignKeyId != nullSymmetric to the above; does not depend on parent field values
Want rows where a parent field has a valueWHERE Parent.Field = 'x'Fine — but know parent-less rows are already excluded by the value match, not by the join
Tempted to use WHERE Parent.Field = null for "no parent"Filter the FK Id insteadParent-field null check also returns rows where the parent doesn't exist
Filtering a Boolean / checkbox fieldWHERE Flag__c = true or = falseBooleans are never null; = null is read as = false
Reading a parent field in Apex after the queryNull-guard the relationship (rec.Parent != null)Outer join returns null-FK rows; the parent object is null on those rows
Tempted to paste WHERE Field IS NULL from SQLRewrite as WHERE Field = null (or != null)SOQL has no IS NULL / IS NOT NULL; compare to the null keyword directly
Selective equality filter where null rows aren't wantedAdd AND Field != null alongside the value predicateExplicitly filtering nulls in the WHERE clause lets Salesforce improve query performance
Need sort placement of null rows (first/last)Use apex/soql-null-ordering-patterns (NULLS FIRST/LAST)That is ordering, not filtering; different concern
  1. Classify the query. Confirm it is a child-to-parent relationship query (dot notation) and note every foreign key and parent field it touches.
  2. State the intended result set. Decide precisely which rows you want — empty lookup, populated lookup, a specific parent value, a true/false flag — before touching the WHERE clause.
  3. Map each filter to the right column. For "no/has lookup," filter the foreign-key Id (AccountId), not the parent field (Account.Name). For Boolean intent, write an explicit = true / = false.
  4. Account for OR and ORDER BY. Verify that OR branches and ordering on a related field are not silently pulling in null-foreign-key rows you meant to exclude; add an explicit FK filter if they are.
  5. Null-guard the Apex traversal. Wherever the code reads a parent field, guard the relationship object (rec.Parent != null) so outer-joined null-FK rows don't throw a NullPointerException.
  6. Verify against data. Run the query (and the FK-filtered variant) against a sandbox or scratch org with known parent-less rows and confirm the counts match the intended result set.

Review Checklist

Run through these before marking work in this area complete:

Salesforce-Specific Gotchas

Non-obvious platform behaviors that cause real production problems:

  1. WHERE Parent.Field = null can't tell "no lookup" from "deleted parent" — it returns the child row in both cases, so it silently over-selects when you meant "records with an empty lookup." Filter the foreign-key Id instead.
  2. A Boolean = null filter returns all the false rows, not none — Boolean fields never hold null, so Flag__c = null is evaluated as Flag__c = false. Teams expecting an empty result set instead get every inactive record.
  3. Reading a parent field in Apex after a relationship query can throw NullPointerException — the outer join returns rows whose foreign key is null, and on those rows the parent relationship object is null; rec.Account.Name blows up unless you guard it.
  4. SOQL has no IS NULL / IS NOT NULL — a null filter pasted in from SQL fails to parse. Compare to the null keyword directly with = null / != null.

Output Artifacts

ArtifactDescription
Corrected WHERE clauseFilters the foreign-key field (not the parent field) and uses explicit true/false for Booleans
Null-guarded Apex traversalLoop that checks rec.Parent != null before dereferencing parent fields
Null-semantics review worksheettemplates/soql-outer-join-null-semantics-template.md — walks a query's outer-join and null behavior
Static check outputscripts/check_soql_outer_join_null_semantics.py findings for risky relationship/Boolean null checks

Package files