SOQL and SOSL Reference

Introduction to SOQL and SOSL
Salesforce Object Query Language (SOQL)
Typographical Conventions in This Document
Quoted String Escape Sequences
Reserved Characters
Alias Notation
SOQL SELECT Syntax
SOQL SELECT Examples
SOQL SELECT Functions
Relationship Queries
Understanding Relationship Names
Using Relationship Queries
Understanding Relationship Names, Custom Objects, and Custom Fields
Understanding Query Results
null Values in Lookup Relationships and Outer Joins
Identifying Parent and Child Relationships
Understanding Relationship Fields and Polymorphic Fields
Understanding Relationship Query Limitations
Using Relationship Queries with History Objects
Using Relationship Queries with Data Category Selection Objects
Using Relationship Queries with the Partner WSDL
Change the Batch Size in Queries
SOQL Object Limits and Limitations
Syndication Feed SOQL and Mapping Syntax
Location-Based SOQL Queries
Considerations for Querying Currency Data Using SOQL
Salesforce Object Search Language (SOSL)
PDF

null Values in Lookup Relationships and Outer Joins

Relationship SOQL queries return records, even if the relevant foreign key field has a null value, as with an outer join.
  • In an ORDER BY clause, the record is returned even if the foreign key value in a record is null. For example: 
    1SELECT Id, CaseNumber, Account.Id, Account.Name
2FROM Case
3ORDER BY Account.Name

    Any case record for which AccountId is empty is returned.

    The following example uses custom objects:

    1SELECT ID, Name, Parent__r.id, Parent__r.name
2FROM Child__c
3ORDER BY Parent__r.name

    This query returns the Id and Name values of the Child object and the Id and name of the Parent object referenced in each Child, and orders the results by the parent name. Records are returned even if Parent__r.id or Parent__r.name are null.

  • In a WHERE clause that uses OR, records are returned even if the foreign key value in a record is null. For example, if your organization has one contact with the value of its LastName field equal to Young and the value of its AccountId field equal to null, and another contact with a different last name and a parent account named Quarry, the following query returns both contacts:
    1SELECT Id FROM Contact WHERE LastName = 'Young' or Account.Name = 'Quarry'
  • In a WHERE clause that checks for a value in a parent field, the record is returned even if the parent does not exist.. For example:
    1SELECT Id
2FROM Case
3WHERE Contact.LastName = null

    Case record Id values are returned.

  • In a WHERE clause that uses a Boolean field, the Boolean field never has a null value. Instead, null is treated as false. Boolean fields on outer-joined objects are treated as false when no records match the query.