Soql Outer Join Null Semantics

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:

• Confirm the query traverses a relationship. Outer-join semantics apply to child-to-parent relationship queries (dot notation such as Account.Name, Parent__r.Name, Contact.LastName). A flat query over a single object's own fields does not exhibit them.
• Name the exact goal for the filter. "Records with an empty lookup," "records with a populated lookup," and "records whose parent field has a specific value" are three different result sets — and the naive filter usually returns a superset. Decide which one you need before writing the WHERE clause.
• Beware the most common wrong assumption. Practitioners assume a relationship query, an ORDER BY on a related field, or a WHERE Parent.Field = null acts like an inner join that drops rows with a null foreign key. It does not — the official reference states relationship queries return records "even if the relevant foreign key field has a null value, as with an outer join."
• Remember Booleans are never null. A Boolean field cannot hold null; the platform treats null as false. So WHERE Flag__c = null does not return "unset" rows — it returns every false row.
• SOQL has no IS NULL / IS NOT NULL. Filter null by comparing to the null keyword directly — = null for unset rows, != null for populated ones (see Core Concepts for the operator list).

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:

• WHERE Flag__c = null is equivalent to WHERE Flag__c = false
• WHERE Flag__c != null is equivalent to WHERE Flag__c = true

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

Recommended Workflow

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:

• Null filters use = null / != null — no SQL-style IS NULL / IS NOT NULL
• "No lookup" / "has lookup" filters test the foreign-key Id field, not a traversed parent field
• No WHERE Parent.Field = null is being used to mean "the parent doesn't exist"
• Selective equality filters that shouldn't return null rows include an explicit != null guard
• Every Boolean filter compares to an explicit true / false, not null
• OR clauses and ORDER BY on a related field don't silently include null-foreign-key rows
• Apex that reads a parent field null-guards the relationship object first
• The expected row count was confirmed against data that actually contains parent-less rows

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

Related Skills

• apex/apex-soql-relationship-queries — the mechanics of child-to-parent dot notation and parent-to-child subqueries this skill reasons about the null behavior of.
• apex/soql-null-ordering-patterns — NULLS FIRST / NULLS LAST sort placement of null rows; complementary to this skill's focus on which rows are returned.
• apex/soql-fundamentals — general SELECT / WHERE / ORDER BY syntax if the query itself needs building, not just its null handling.
• apex/soql-security — enforce CRUD/FLS and prevent injection when the corrected filter is assembled dynamically.