SOQL and SOSL Reference

1 SOQL and SOSL ReferenceVersion , Spring 22 @salesforcedocsLast updated: March 1, 2022 Copyright 2000 2022 , inc. All rights reserved. Salesforce is a registered trademark of , inc.,as are other names and marks. Other marks appearing herein may be trademarks of their respective 1: Introduction to SOQL and SOSL.. 1 Chapter 2: Salesforce object Query Language (SOQL).. 3 Typographical Conventions in This Document.. 5 Quoted String Escape Sequences.. 5 Reserved Characters.. 6 Alias Notation.. 7 SOQL SELECT Syntax.. 7 Condition Expression Syntax (WHERE Clause).. 9fieldExpression Syntax.. 13 USING SCOPE.. 26 ORDER BY.. 27 LIMIT.

2 28 OFFSET.. 29 FIELDS().. 31 Update an Article s Keyword Tracking with SOQL.. 35 Update an Article Viewstat with SOQL.. 35 WITH filteringExpression.. 35 GROUP BY.. 40 HAVING.. 47 TYPEOF.. 48 FORMAT ().. 51 FOR VIEW.. 51 FOR Reference .. 52 FOR UPDATE.. 52 Aggregate Functions.. 53 Date Functions.. 58 Querying Currency Fields in Multi-Currency Orgs.. 60 Example SELECT Clauses.. 62 Relationship Queries.. 63 Understanding Relationship Names.. 64 Using Relationship Queries.. 65 Understanding Relationship Names, Custom Objects, and Custom Fields.. 66 Understanding Query Results.. 68 Lookup Relationships and Outer Joins.

3 70 Identifying Parent and Child Relationships.. 70 Understanding Relationship Fields and Polymorphic Fields.. 72 Understanding Relationship Query Limitations.. 77 Using Relationship Queries with History Objects.. 77 Using Relationship Queries with Data Category Selection Objects.. 78 Using Relationship Queries with the Partner WSDL.. 78 Change the Batch Size in Queries.. 78 SOQL Limits on Objects.. 79 SOQL with Big Objects.. 83 Syndication Feed SOQL and Mapping Syntax.. 84 Location-Based SOQL Queries.. 84 Async SOQL.. 88 Chapter 3: Salesforce object Search Language (SOSL).. 91 Typographical Conventions in This Document.

4 93 SOSL Limits on Search Results.. 93 SOSL Limits on External object Search Results.. 95 SOSL Syntax.. 95 Example Text Searches.. 98convertCurrency().. 99 FIND {SearchQuery}.. 100 FORMAT().. 104IN SearchGroup.. 104 LIMIT n.. 105 OFFSET n.. 106 ORDER BY Clause.. 107 RETURNING FieldSpec.. 107toLabel(fields).. 110 Update an Article s Keyword Tracking with SOSL.. 111 Update an Article s Viewstat with SOSL.. 111 USING Listview=.. 112 WHERE conditionExpression.. 112 WITH DATA CATEGORY DataCategorySpec.. 116 WITH DivisionFilter.. 118 WITH HIGHLIGHT.. 118 WITH METADATA.. 120 WITH NETWORK NetworkIdSpec.. 120 WITH PricebookId.

5 121 WITH SNIPPET.. 121 WITH SPELL_CORRECTION.. 124 Index.. 125 ContentsCHAPTER 1 Introduction to SOQL and SOSLIf you ve built a custom UI for Salesforce, you can use the Salesforce object Query Language (SOQL) andSalesforce object Search Language (SOSL) APIs to search your organization s Salesforce guide explains when to use SOQL and SOSL and outlines the syntax, clauses, limits, and performanceconsiderations for both languages. It is intended for developers and assumes knowledge and experienceworking with APIs to interact with Which to UseA SOQL query is the equivalent of a SELECT SQL statement and searches the org database.

6 SOSL is aprogrammatic way of performing a text-based search against the search you use SOQL or SOSL depends on whether you know which objects or fields you want tosearch, plus other SOQL when you know which objects the data resides in, and you want to: Retrieve data from a single object or from multiple objects that are related to one another. Count the number of records that meet specified criteria. Sort results as part of the query. Retrieve data from number, date, or checkbox SOSL when you don t know which object or field the data resides in, and you want to: Retrieve data for a specific term that you know exists within a field.

7 Because SOSL can tokenizemultiple terms within a field and build a search index from this, SOSL searches are faster and canreturn more relevant results. Retrieve multiple objects and fields efficiently where the objects might or might not be related toone another. Retrieve data for a particular division in an organization using the divisions feature. Retrieve data that s in Chinese, Japanese, Korean, or Thai. Morphological tokenization for CJKT termshelps ensure accurate : SOSL doesn t support big ConsiderationsTo increase the efficiency of queries and searches, keep in mind: Both SOQL WHERE filters and SOSL search queries can specify text you should look for.

8 When agiven search can use either language, SOSL is generally faster than SOQL if the search expressionuses a CONTAINS term. SOSL can tokenize multiple terms within a field (for example, multiple words separated by spaces)and builds a search index off this. If you re searching for a specific distinct term that you know existswithin a field, you might find SOSL is faster than SOQL for these searches. For example, you might1use SOSL if you were searching for John against fields that contained values like Paul and JohnCompany . Keep the number of fields to be searched or queried to a minimum. Using many fields leads to manypermutations, which can be difficult to more information, see Best Practices for Deployments with Large Data Queries and SearchesConstruct and execute queries and searches using one of several environments, including SOAP API,REST API, Apex, and more.

9 For more information on which environments support queries and searches,see Salesforce object Query Language (SOQL) on page 3 and Salesforce object Search Language(SOSL) on page to SOQL and SOSLCHAPTER 2 Salesforce object Query Language (SOQL)Use the Salesforce object Query Language (SOQL) to search your organization s Salesforce data forspecific information. SOQL is similar to the SELECT statement in the widely used Structured QueryLanguage (SQL) but is designed specifically for Salesforce this chapter .. TypographicalConventions in ThisDocumentWith SOQL, you can construct simple but powerful query strings in several environments.

10 Using the queryString parameter of a SOAP API query() call. See query() in the SOAP APID eveloper Guide. Quoted String EscapeSequences Using the q parameter of a REST API query request. See Query in the REST API Developer Guide. Reserved Characters Using Apex statements. See SOQL and SOSL Queries in the Apex Developer Guide. Alias Notation SOQL SELECT Syntax Using Visualforce controllers and getter methods. See Controller Methods in the Visualforce DeveloperGuide. Relationship Queries Using the Salesforce CLI. See force:data:soql:query in the data Commands topic of the Salesforce CLIC ommand Reference . Change the BatchSize in Queries SOQL Limits onObjects Using an extension for Visual Studio Code.