Salesforce Query Language (SQL)

Author - SfdcpandaSchool
July 06, 202324 minute read
0
Salesforce Query Language (SQL)
Chapter 1:

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.
Chapter 2:

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).
Chapter 3:

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&lt;list&lt;SObject&gt;> 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)
Chapter 4:

SOQL Group By Clause

GROUP BY clause is used in SOQL query to group set of records by the values specified in the field. We can perform aggregate functions using GROUP BY clause.

Aggregated functions for GROUP BY clause:

  • COUNT ()
  • COUNT (FIELD_NAME)
  • COUNT_DISTINCT ()
  • SUM ()
  • MIN ()
  • MAX ()

Example:

SELECT Industry, COUNT(Id) From Account GROUP BY Industry

GROUP BY With HAVING Clause:
GROUP BY HAVING Clause is used in SOQL to apply a condition based on a group field values.

Example:

SELECT Industry, COUNT(Id) From Account GROUP BY Industry HAVING Industry IN ('Agriculture','Manufacturing','Construction')

GROUP BY ROLLUP Clause:
GROUP BY ROLLUP Clause is used to add subtotals to get aggregates data in the query results. It returns multiple levels of subtotal rows. We can add up to three fields in a comma-separated list in GROUP BY ROLLUP Clause statement.

Example:

SELECT Industry, Type, COUNT(Id) From Account GROUP BY ROLLUP (Industry, Type)

GROUP BY CUBE Clause:
GROUP BY CUBE clause is used in SOQL query to add subtotals for all combinations of a grouped field in the query results.

Example:
The following query returns subtotals of accounts for each combination of Type and BillingCountry.

SELECT Type, BillingCountry,
GROUPING(Type) grpType, GROUPING(BillingCountry) grpCity,
COUNT(Id) accnts
FROM Account
GROUP BY CUBE(Type, BillingCountry)
ORDER BY GROUPING(Type), GROUPING(BillingCountry)

Some object fields have a field type that does not support grouping. You can’t include fields with these field types in a GROUP BY clause.

Following are the list of Groupable & Non-Groupable field types:

Groupable Field Types:

  • Id (Id)
  • Lookup (Id)
  • Checkbox (Boolean)
  • Phone (String)
  • Picklist (String)
  • Email (String)
  • Text (String)
  • Text Area (String)
  • URL (String)
  • Number (Int). Does not include custom fields, only standard Number fields with SOAP type int, like Account.NumberOfEmployees.
  • Date (date)
  • Direct cross-object references to groupable fields, up to 5 levels from the root object (SOQL limit), as in SELECT count(Id) FROM Contact GROUP BY Account.Parent.Parent.Parent.Parent.Name. Both custom and standard references are groupable.
  • Formulas of type Checkbox and Date, including cross-object formulas across standard and custom relationships.

Non-Groupable Field Types:

  • Auto Number (string)
  • Address Compound Fields
  • Number (double), including custom Number fields with or without decimal and regardless of scale.
  • Percent (double), including custom Percent fields with or without decimal and regardless of scale.
  • Currency (double), including custom Currency fields with or without decimal and regardless of scale.
  • Components of Address compound fields are groupable if their types otherwise allow it.
  • Geolocations, both custom and standard, and whether or not defined as having decimal places, including the compound field and components (location/double)
  • Long Text (string)
  • Rich Text (string)
  • Multi-Select Picklist (string)
  • Roll-Up Summary Fields (double), including COUNT rollups.
  • Encrypted Text Fields (Classic Encryption; string)
  • Date/Time (dateTime)
  • Time (time)
  • Formulas of types other than Checkbox and Date, including the otherwise-groupable String type.

Aggregate Functions Supported Field Types:

Data Type AVG() COUNT() COUNT_DISTINCT() MIN() MAX() SUM()
base64 No No No No No No
boolean No No No No No No
byte No No No No No No
date No Yes Yes Yes Yes No
dateTime No Yes Yes Yes Yes No
double Yes Yes Yes Yes Yes Yes
int Yes Yes Yes Yes Yes Yes
string No Yes Yes Yes Yes No
time No No No No No No
address No No No No No No
anyType No No No No No No
calculated Depends on data type* Depends on data type* Depends on data type* Depends on data type* Depends on data type* Depends on data type*
combobox No Yes Yes Yes Yes No
currency** Yes Yes Yes Yes Yes Yes
DataCategoryGroupReference No Yes Yes Yes Yes No
email No Yes Yes Yes Yes No
encryptedstring No No No No No No
location No No No No No No
ID No Yes Yes Yes Yes No
masterrecord No Yes Yes Yes Yes No
multipicklist No No No No No No
percent Yes Yes Yes Yes Yes Yes
phone No Yes Yes Yes Yes No
picklist No Yes Yes Yes Yes No
reference No Yes Yes Yes Yes No
textarea No Yes Yes Yes Yes No
url No Yes Yes Yes Yes No

Using AggregateResult in Salesforce

The aggregate functions COUNT(fieldname), COUNT_DISTINCT(), SUM(), AVG(), MIN() and MAX() in SOQL return an AggregateResult object or a List of AggregateResult objects. We can use aggregate functions result in apex by using AggregateResult object.

Here is an example to use AggregateResult in Salesforce. In below example I’m using COUNT(fieldname) aggregate function in SOQL to show Account record respective number of Contacts.

Visualforce Page:

<apex:page controller="SampleController" action="{!getData}">
    <apex:form >
        <apex:pageBlock >
            <apex:pageblockTable value="{!accWrapList}" var="acc">            
                <apex:column headerValue="Account Name" value="{!acc.AccountName}"/>
                <apex:column headerValue="Number of Contacts" value="{!acc.TotalContact}"/>
            </apex:pageblockTable>
        </apex:pageBlock>
    </apex:form>
</apex:page>

Apex Class:

public with sharing class SampleController {
    public List<AggregateResult> result {get;set;}    
    public List<AccWrapper> accWrapList {get;set;}
    public List<Account> accList;
    public Map<Id, Account> accMap;
    List<Id> idList;
    
    public void getData() {
        accWrapList = new List<AccWrapper>();
        result = new List<AggregateResult>();
        idList = new List<Id>();
        accList = new List<Account>();
        accMap = new Map<Id, Account>();
        
        result = [SELECT COUNT(Id) Total, AccountId
            FROM Contact WHERE AccountId != null GROUP BY AccountId];               
        
        for(AggregateResult a : Result) {   
            idList.add((Id)a.get('AccountId'));
        }
        
        accList = [SELECT Id, Name FROM Account WHERE Id IN : idList];
        for(Account a : accList) {
            accMap.put(a.Id, a);            
        }
        
        for(AggregateResult aResult : result) {
            Account acc = accMap.get((Id)(aResult.get('AccountId')));
            accWrapList.add(new AccWrapper(aResult, acc.Name)); 
        }
    }
    
    public class AccWrapper {
        public Integer TotalContact {get;set;}
        public String AccountName {get;set;}
        
        public AccWrapper(AggregateResult a, String AccountName) {
            this.TotalContact =  (Integer)a.get('Total');
            this.AccountName = AccountName;
        }
    }
}

Output:

Aggregate Functions in SOQL

In database management an aggregate function is a function where the values of multiple rows are grouped together to form a single value. In Salesforce SOQL aggregate functions are same as SQL aggregate function.

The following aggregate functions are provided by SOQL:

  • AVG()
  • COUNT() and COUNT(fieldName)
  • COUNT_DISTINCT()
  • MIN()
  • MAX()
  • SUM()

AVG(): Returns the average value of a numeric field.

Example:

SELECT CampaignId, AVG(Amount)
FROM Opportunity
GROUP BY CampaignId

COUNT() and COUNT(fieldName): Returns the number of rows matching the query criteria. COUNT(Id) in SOQL is equivalent to COUNT(*) in SQL. COUNT(fieldName) available in API version 18.0 and later. If you are using a GROUP BY clause, use COUNT(fieldName) instead of COUNT().

Example using COUNT():

SELECT COUNT()
FROM Account
WHERE Name LIKE 'a%'

Example using COUNT(fieldName):

SELECT COUNT(Id)
FROM Account
WHERE Name LIKE 'a%'

COUNT_DISTINCT(): Returns the number of distinct non-null field values matching the query criteria. COUNT_DISTINCT(fieldName) in SOQL is equivalent to COUNT(DISTINCT fieldName) in SQL. To query for all the distinct values, including null, for an object, see GROUP BY. Available in API version 18.0 and later.

Example:

SELECT COUNT_DISTINCT(Company)
FROM Lead

MIN(): Returns the minimum value of a field. If you use the MIN() or MAX() functions on a picklist field, the function uses the sort order of the picklist values instead of alphabetical order. Available in API version 18.0 and later.

Example:

SELECT MIN(CreatedDate), FirstName, LastName
FROM Contact
GROUP BY FirstName, LastName

MAX(): Returns the maximum value of a field.
Example:

SELECT Name, MAX(BudgetedCost)
FROM Campaign
GROUP BY Name

SUM(): Returns the total sum of a numeric field.
Example:

SELECT SUM(Amount)
FROM Opportunity
WHERE IsClosed = false AND Probability > 60

Note: You can’t use a LIMIT clause in a query that uses an aggregate function. The following query is invalid:

SELECT MAX(CreatedDate)
FROM Account LIMIT 1

The aggregate functions COUNT(fieldname), COUNT_DISTINCT(), SUM(), AVG(), MIN() and MAX() in SOQL return an AggregateResult object or a List of AggregateResult objects. You can use aggregate functions result in apex by using AggregateResult object.

Example:

List<AggregateResult> result = [SELECT COUNT(Id) Total, AccountId 
FROM Contact WHERE AccountId != null GROUP BY AccountId];
Chapter 5:

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:

ApproachBounded – FIELDS(STANDARD)Unbounded – FIELDS(ALL) and FIELDS(CUSTOM)
Apex (inline and dynamic)SupportedNot supported
Bulk API 2.0SupportedNot supported
CLISupportedSupported only if the result rows are limited.
SOAP API and REST APISupportedSupported 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
Chapter 6:
Date Formats and Date Literals in WHERE Clause in Salesforce
Chapter 7:

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

Chapter 8:
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
Chapter :
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

Conclusion :

Happy!! To help you add to your knowledge. You can leave a comment to help me understand how the blog helped you. If you need further assistance, please leave a comment or contact us at: Contact us. You can click on "contact Us" on the website and share the issue with me.

Tags

You May Like

Show more

Post a Comment

0Comments
Post a Comment (0)
Share to other apps
Copy Post Link