Differences between SOQL and SOSL in Salesforce?
SOQL (Salesforce Object Query Language)
Let’s you query a object in salesforce. You can have nested queries. Returns a list of sobject records.
Example: List<Account> aa = [SELECT Id, Name FROM Account WHERE Name = ‘Acme’];SOSL (Salesforce Object Search Language)
Let’s you search for a term in multiple objects. Return a list of list of sobject records.
Example:List<List<SObject>> searchList = [FIND ‘map*’ IN ALL FIELDS RETURNING Account (Id, Name), Contact, Opportunity, Lead];
SOQL and SOSL have different indexes:
SOQL indexes are:
- Primary keys (Id, Name and Owner fields)
- Foreign keys (lookup or master-detail relationship fields)
- Audit dates (such as LastModifiedDate)
- Custom fields marked as External ID or Unique.
Fields that can’t be indexed in SOQL are:
- Multi-select picklists
- Currency fields in a multicurrency organization
- Long text fields
- Some formula fields
- Binary fields (fields of type blob, file, or encrypted text.)
Note that new data types, typically complex ones, may be added to Salesforce and fields of these types may not allow custom indexing.
SOSL indexes are:
This is the one point where my discussion is weak. I simply can’t seem to find Salesforce documentation on the SOSL indexes. I know there are standard fields like
Name that are indexed, but I can’t find the documentation for all of it. If anyone can post a comment and/or edit the post here to include that info, I would really appreciate it.
When to Use SOQL:
- you know in which object or fields the data resides.
- you want to retrieve data from a single object or from multiple objects that are related to one another.
- you want to count the number of records that meet specified criteria.
- you want to sort results as part of the query.
- you want to retrieve data from number, date or checkbox fields.
When to Use SOSL:
- you don’t know in which object or field the date resides and you want to find it in the most efficient way possible.
- you want to retrieve multiple objects and fields efficiently and the objects may or may not be related to one another.
- you want to retrieve data for a particular division in an organization using the divisions feature and you want to find it in the most efficient way possible.