Unlike SQL, SOQL is a language exclusively for querying the database rather than modifying data like in traditional SQL. There are no INSERT or UPDATE statements. In SOQL, Salesforce Objects are represented as SQL tables. A SOQL query is the equivalent of a SELECT SQL statement and searches the org database. SOSL is a programmatic way of performing a text-based search against the search index. Whether you use SOQL or SOSL depends on whether you know which objects or fields you want to search, plus other considerations. Both SOQL WHERE filters and SOSL search queries can specify the text you should look for. When a given search can use either language, SOSL is generally faster than SOQL if the search expression uses a CONTAINS term.
SOQL | SOSL |
SOQL (Salesforce Object Query Language ) retrieves the records from the database by using the “SELECT” keyword. | SOSL(Salesforce Object Search Language) retrieves the records from the database by using the “FIND” keyword. |
By Using SOQL we can know in Which objects or fields the data resides. | By using SOSL, we don’t know in which object or field the data resides. |
We can retrieve data from a single object or from multiple objects that are related to each other. | We can retrieve multiple objects and field values efficiently when the objects may or may not be related to each other. |
We can Query on only one table. | We can query on multiple tables. |
Difference between SOQL and SOSL in Salesforce
SOQL in Salesforce
- SOQL stands for” Salesforce Object Query Language”.
- It returns Records.
- Records are stored in the collection. Records are pertaining to a single sObject.
- Search in single sObject and it retains records.
- SOQL retrieves the data from the database using the “SELECT” keyword.
- It works on multiple objects at the same time.
- SOQL is not used in Triggers and can be used only in Apex classes and anonymous blocks.
- SOQL against the same field will be slow.
SOSL in Salesforce
- SOSL stands for “Salesforce Object Search language”.
- It returns fields.
- Records are pertaining to different sObjects.
- Search in entire Org and returns fields.
- SOSL retrieves the data from the database using the keyword “FIND“.
- It doesn’t work on multiple objects at the same time and needs to write different SOQL queries for different objects.
- SOSL can be used in Triggers and Apex classes.
- All fields are already text indexed.
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.
Questions on DML, SOQL, and SOSL.
1. What are the DML statements available in Apex?
- Insert
- Update
- Delete
- Undelete
- Upsert (Combination of insert and update)
- Merge (Combination of update and delete)
2. Governor Limits for DML statements?
Number of DML statements per transaction: 150 (as a whole including insert, update, delete and undelete)
Number of rows processed per DML stmt: 10000
3. What is SOQL?
SOQL: Salesforce Object Query Language
SOQL Purpose: To fetch info. from an object and related objects.
We can write queries on one object while querying on those objects we can fetch the child object info. or parent object info. (we cannot capture unrelated objects' info.)
- SOQL queries per transaction: 100.
- SOQL query rows returned: 50000.
4. What is SOSL?
SOSL: Salesforce Object Search Language
SOSL Purpose: We can search for a value in multiple objects (no need for any relationship).
Results of the SOSL query can be stored in the List of List.
- SOSL queries per transaction: 20.
- SOSL query rows returned: 2000.
Dev 401 Salesforce (SFDC) Certification Question Answers
5. There is a Queue with the name MyQueue. How to query it using SOQL query from the database?
Queues will store in the Group object. To query for MyQueue from the database using SOQL, we should use the following syntax -
Group grp = [select Id, Name from Group where Name = 'MyQueue' and Type = 'Queue' Limit 1];