SOQL and SOSL are tools to search for records in Salesforce Organization. Both of them have their syntaxes and cases to use. Both of these tools are must-have skills to work on the Salesforce Platform. Because with SOQL and SOSL you can search, monitor, and delete any records on your organization. These possibilities will come in handy on every project. Today we are talking about the basics of these languages and tips on using them.
Where SOQL/SOSL could be used?
Log in on any Salesforce environment. Click on Settings and choose the Developer console. It will be opened in the new window, pick the Query Editor from there and you are ready to go! Both SOQL and SOSL queries can be written and executed from here. Now as we know where to use it, we should define the differences between SOQL and SOSL one by one.
1. SOQL (Salesforce Object Query Language) is the language used to search for specific records. A SOQL query begins with the required “SELECT” statement that helps select fields we are looking for and the “FROM” statement to define from which object we are taking these fields. Also, we can use the “WHERE” statement to put some filters to this search making it more concrete (each filter should be divided by “AND” or “OR”), for example, Select Id, Name FROM Lead WHERE IsDeleted = False AND Name = ‘Tom’.
Some useful tips on using SOQL:
- Add as many filters as you can with WHERE + AND/OR to make the search more concrete. Also, to make the query more readable it’s recommended to write each AND/OR statement in a new row.
- When you are looking for custom fields it's better to use Open Resource in File (or press CTRL+SHIFT+O) because from there you can find all objects and or fields that relate to them.
- Ctrl-click on fields list + Query can automatically create SOQL query on chosen fields.
- Every column on search results can be filtered alphabetically or by type.
- Use “%” inside the string to include every text that goes after your input, for example WHERE Name = ‘Test’ will result only in records with the name Test, but WHERE Name = ‘Test%’ will result in records with the name Test, Test 1, Test 2, etc.
- When all required fields are located in one object but one field locates in another object. In that case, it can be related through that object, use: the ObjName__r.required field.
2) SOSL (Salesforce Object Search Language) is the language used to perform text-based searches in records. Use SOSL when you don’t know which object or field contains the data but you know that this data exists somewhere within a field. A SOSL query begins with the required “FIND” statement with {SearchQuery} of what you are looking for. Then you can add an optional statement to filter the query by object type, fields, data categories, and more. You can also determine what is returned. For example, you can specify the order of the results and how many rows to return. Each filter should be put in “[]” and to make the query more readable it’s recommended to write each filter in the new row. While writing SOSL queries you should be aware that your filters are very specific because SOSL brings all data from all objects and fields which pass your filters (the result could be up to 2000 records max). To avoid this outcome I will show you some filters to start from (but there are much more of them).
- IN: Limits the types of fields to search, including email, name, or phone.
- LIMIT: Specifies the maximum number of rows to return.
- OFFSET: Displays the search results on multiple pages.
- RETURNING: Limits the objects and fields to return.
- WITH DATA CATEGORY: Specifies the data categories to return.
- WITH DivisionFilter: Specifies the division field to return.
- WITH NETWORK: Specifies the Experience Cloud site ID to return.
- WITH PricebookId: Specifies the price book ID to return.
We hope that this short guideline with basic principles of SOQL and SOSL was useful. After reading the article you will be able to write some simple queries but there is much more to learn. If you are interested in improving your skills in SOQL/SOSL, start by looking at Trailheads and developers. Salesforce for more information and study materials.
