Salesforce Query Language (SQL)

Introduction to SOQL and SOSL - Salesforce Developers

To use SOQL and SOSL and outlines the syntax, clauses, limits, and performance considerations for both languages. It is intended for developers and assumes knowledge and experience working with APIs to interact with data.

You can use the Salesforce Object Query Language (SOQL) and Salesforce Object Search Language (SOSL) APIs to search your organization’s Salesforce data.

Which to Use:

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.

Use 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 fields.

Use 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. Because SOSL can tokenize multiple terms within a field and build a search index from this, SOSL searches are faster and can return more relevant results.
• Retrieve multiple objects and fields efficiently where the objects might or might not be related to one another.
• Retrieve data for a particular division in an organization using the divisions feature.

Salesforce Object Query Language (SOQL)

Salesforce Object Query Language (SOQL): Use this Salesforce Object Query Language (SOQL) to construct simple but powerful query strings in the queryString parameter in the query() call, in Apex statements, in Visualforce controllers and getter methods, or in the Schema Explorer of the Force.com IDE.

Salesforce Object Query Language is used to query that records from the database.com based on the requirement.

There are 2 types of SOQL Statements.

• Static SOQL
• Dynamic SOQL

Static SOQL:Static SOQL means you are using some SOQL with any user input or hard code values like below query view source

The Static SOQL Statement is written in [] (Array Brackets)

This statements are similar to IINQ(Ion Integrated Query)

Example: String search for ='varma'; Contact[] contacts=[select testfield__c, FirstName, LastName from Contact Where Last Name=:search for];

Dynamic SOQL:Dynamic SOQL refers to the creation of a SOQL string at run time with Apex code. Dynamic SOQL enables you to create more flexible application. To create Dynamic SOQL query at run time use Database.Query() method, in one of the following ways. Return a single sObjects when the query returns a single record. sObjects s = Database. Query(String_limit_l);
For example, you can create a search based on input from an end user or update records with varying field names.

String name = 'Varma';

List sobjList = Database.query('SELECT Id FROM CustomObject__c WHERE Name = : name');

Dynamic SOQL can't use bind variable fields in the query string.

You can instead resolve the variable field into a string and use the string in your dynamic SOQL query:

String field = myVariable.field__c;
List sobjList = Database.query('SELECT Id FROM CustomObject__c WHERE field__c = :field'); SOQL Limits on Big Object

• When building an index query, do not leave gaps between the first and last fields in the query.
• The !=, LIKE, NOT IN, EXCLUDES, and INCLUDES operators are not valid in any query.
• Aggregate functions are not valid in any query.
• To retrieve a list of results, do not use the Id field in a query. Including Id in a query returns only results that have an empty ID (000000000000000 or 000000000000000AAA).

Salesforce Object Search Language (SOSL)

Salesforce Object Search Language is a search language of salesforce and the important feature is that Unlike SOQL, we can search in multiple objects at same time using single SOSL. In SOQL, we can query only one object at a time but in SOSL, we can search for some specified string like ‘anyString’ in multiple objects at the same time.

• We can search for some specified string like ‘anyString’ in multiple objects at the same time.
• We can mention in which fields of all the sObjects,we want to search for the specified string.
• The SOSL query start with ‘FIND’ keyword .
• You can also specify, which fields to return for each object mentioned in SOSL query. Suppose you need to perform search on three objects like Account, Contact &38; Opportunity then you can mention like, for list returned with Account results only (Name, Industry) fields should be returned, and for Contact results (firstName, lastName) should be returned and similarly for Opportunity too.
• The final result of SOSL is a list of lists of sObjects.
• The returned result contains the list of sObjects in the same order as the order you defined in SOSL query. If a SOSL query does not return any records/values for a specified sObject, then search results include an empty list for that sObject type. The search string should be at least of two characters long.

List<list<SObject>> searchList = [FIND ‘map*’ IN ALL FIELDS RETURNING Account (Id, Name), 
                Contact, Opportunity];
Account [] acts = ((List<Account>)searchList[0]);
Contact [] cnts = ((List<Contact>)searchList[1]);
Opportunity [] oppties = ((List<Opportunity>)searchList[2]);

Example

FIND {SFDC} IN ALL FIELDS RETURNING Account(Name),Contact(FirstName,LastName)
We can use this in apex like this :
List<list<SObject>> searchList = [FIND 'SFDC' IN ALL FIELDS RETURNING Account(Name), Contact(FirstName,LastName)];

list<Account> accs = (list<Account>) searchList[0];
list<Contact> cons = (list<Contact>) searchList[1];

This SOSL query looks for the term ‘SFDC’ in the Account’s Name field and the Contact’s FirstName and LastName fields.

NOTE : While using an SOSL query in the editor, you need to enclose the search term in curly braces {<search term>}, but in the case of SOSL in apex you need to enclose the search term in single quotes like this, ‘<search term>’

Running SOSL Queries
Query Editor

While running SOSL queries in the developer console’s query editor, we need to use a single brace with our search term instead of using a single quote (‘) like in apex, so the same SOSL query as the last example will look like this when run in the query editor.

FIND {SFDC} IN ALL FIELDS RETURNING Account(Name), Contact(FirstName,LastName)

SOQL Field() Functions

The FIELDS() function lets us select groups of fields without knowing their names in advance. This function simplifies SELECT statements, avoids the need for multiple API calls, and provides a low-code method to explore the data in the org. This function is available in API version 51.0 and later.

We have three way to get Fields dynamically.

• FIELDS(ALL)—to select all the fields of an object.
• FIELDS(CUSTOM)—to select all the custom fields of an object.
• FIELDS(STANDARD)—to select all the standard fields of an object.

For example we can use below query to get all data

SELECT FIELDS(ALL) FROM Account LIMIT 200

We can follow the . notation to access the fields easily.

for(Account acc: [SELECT FIELDS(Standard) FROM Account LIMIT 200] ) {
    System.debug('============='+acc.Name);
}

But we have some limitations here.

1. If we try to use FIELDS(ALL) or FIELDS(CUSTOM) in Spring 21 we will get the error “The SOQL FIELDS function is not supported with an unbounded set of fields in this API”.
2. LIMIT n—where n is less than or equal to 200.

Note:FIELDS() respects field-level security, therefore only the fields that you have permission to access will be shown.

There is a limit of 200 records that can be retrieved in queries using the FIELDS() function.

FIELDS() can be used on subqueries as well, if you are querying the Account object and you want to query the associated contacts you could select all fields on the contact object.
SELECT Name, (SELECT FIELDS(ALL) FROM Account.Contacts LIMIT 200) FROM Account

FIELDS() can be used in apex but only if you are using FIELDS(STANDARD) as it is a bounded query and has a well-defined set of fields.

Unbounded queries are not supports - FIELDS(ALL) and FIELDS(CUSTOM) in apex.

Bounded and Unbounded Queries

Bounded queries have well-defined sets of fields. While unbounded queries have sets of fields that the API can’t determine in advance. For example, because the number of custom fields for an object is not predetermined, FIELDS(CUSTOM) and FIELDS(ALL) are considered unbounded. Below table shows the support for FIELDS() in bounded and unbounded queries:

Approach	Bounded – FIELDS(STANDARD)	Unbounded – FIELDS(ALL) and FIELDS(CUSTOM)
Apex (inline and dynamic)	Supported	Not supported
Bulk API 2.0	Supported	Not supported
CLI	Supported	Supported only if the result rows are limited.
SOAP API and REST API	Supported	Supported only if the result rows are limited.

So do we have any workaround here, yes. We can still use the Describe methods to get all fields and can make Dynamic SOQL.

public class ApexUtility {
    public static String AllFields(String ObjectName) {
        List<String> fields = 
        new List<String>
(Schema.getGlobalDescribe().get(ObjectName).getDescribe().fields.getMap().keySet());
        String query  = 'SELECT '+String.join(fields, ',')+' FROM '+ObjectName;
        return query;
    }
}

for(Account acc: Database.query(ApexUtility.allFields('Account')) ) {
    System.debug('============='+acc.Name);
}

We can enhance this utility to apply different filters. Although this will enhance the CPU time in processing.

Considerations When Using FIELDS()

• If you know which fields you want to retrieve, reference the fields in the Select as you'll get better performance than using FIELDS()
• You can get errors if you use FIELDS() with operators that require aggregation
• If no fields are returned when using FIELDS() on its own in a SOQL query without any specific fields being selected you will get an error SELECT FIELDS(CUSTOM) on an object without any custom fields will error
• If you update it to have SELECT Id, FIELDS(CUSTOM) it will work now as it will return the id field
Date Formats and Date Literals in WHERE Clause in Salesforce
Date Formats and Date Literals in WHERE Clause in Salesforce
Semi-Join, Anti-Join Relationships in SOQL

Additional SOQL features

1. Inner Join and Outer Join.
2. Semi Join and Anti-Join.
3. Multi Select pick lists.

Inner Join Relationships in Salesforce.

In SOQL statements, Inner and Outer Joins are used to join related objects like parent object and child objects to retrieve all related fields from base objects including the records of non refer related object.

let us see an example :- SELECT NAME, ACCOUNT__r.NAME FROM PROJ__C.

From above SOQL statement, we are trying to retrieve Name and Account name from Project object(custom object). Here the parent object is

Account object and child object is project and the relation between two objects is Look up Relationship. __r keyword is added to custom objects for relationships in Salesforce.

SOQL statement consists of single base object and it is specified using the keyword called “FROM”. Here we using Force.com IDE’s schema explorer to run SOQL statements. Above shown statement is Outer join statement. Outer joins does not retrieve test project values because it does not match to any row in the account object tables.

SOQL Inner joins Relationships in Salesforce.

SOQL Inner Join statements are used to eliminate the records which records are not matched with related objects. In SOQL inner join statements we use filtering condition as shown below. Example :- SELECT NAME, ACCOUNT__r.NAME FROM PROJ__C WHERE ACCOUNT_c !=NULL.



From above screenshot we observe that the unmatched records are eliminated by filtering condition. Form this article we have successfully learned about SOQL Inner Join, Outer Join Relationships in Salesforce. In our next SOQL tutorial we learn about Semi Join and Anti Join Relationships in Salesforce.

Fetching Parent Object Information from the Child Object

Fetching Child Object records from the Parent Object

LIKE, IN, NOT IN, INCLUDES, EXCLUDES, Semi-Joins with IN and Anti-Joins with Not IN ,OrderBY, GroupBy,OFFSET in salesforce SOQL
LIKE, IN, NOT IN, INCLUDES, EXCLUDES, Semi-Joins with IN and Anti-Joins with Not IN ,OrderBY, GroupBy,OFFSET in salesforce SOQL
Reference Guide in salesforce SOQL and SOSL Reference - Salesforce Implementation guides
SOQL and SOSL Reference - Salesforce Implementation guides
Difference between SOQL and SOSL in Salesforce SOQL in Salesforce

• SOQL stands for”Salesforce Object Query Language”.
• It returns Records.
• Records are stores in collection. Records are pertaining to single sObject.
• Search in single sObject and it retains records.
• SOQL retrieves the data from the database using “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 block.
• SOQL against the same field will be slow.
• Using SOQL we can Search only on one object at a time.
• We can query on all fields of any datatype
• We can use SOQL in Triggers and classes.
• We can perform DML operation on query results.
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 need to write different SOQL queries for different objects.
• SOSL can be used in Triggers and Apex class.
• All fields are already text indexed.
• Using SOSL we can search on many objects at a time.
• We can query only on fields whose data type is text,phone and Email.
• We can use in calsses but not in Triggers.
• We cannot perform DML operation on search result