SOQL Keywords
Chapter Topics
- SOQL Basics (Salesforce Object Query Language)
- How to Write SOQL in APEX
- SOQL Variable Binding in APEX
- SOQL Keywords
- SOQL Date Literals In Salesforce
- SOQL Aggregate Query
- Child to Parent Relationship
- Parent to Child – Relationship Queries in SOQL
- SOQL Multi level Relationships
- SOQL Return Type
- Salesforce Dynamic SOQL
- SOQL ’for’ Loops
Keywords Used In SOQL Queries
Some Of The Major Keywords Used In SOQL Queries are:
1. IN
The IN keyword is used to query bulk data, which means the WHERE condition involves a collection of records. The collection of records can be a Set, a List of IDs, or sObjects with populated ID fields.
Ex:
Set < Id > positionIdSet = new Set < Id > {
'a056000000WZEpq',
'a056000000WZYey'
}; // Note: Hardcoding IDs is not a best practice.
List < Position__c > positionList = [SELECT Name
FROM Position__c
WHERE Id IN: positionIdSet
];
Note: A Map cannot be used directly with the IN keyword. However, you can use Map.keySet() (returns a Set) or Map.values() (returns a List) with IN.
2. LIKE
LIKE keyword allows selective queries using wildcards. For example, to query all the position records where the Name starts with ‘John’, you can use a query such as the one given in the code here:
List < Position__c > positionList = [SELECT Name
FROM Position__c
WHERE Name LIKE 'John%'
];
3. AND/OR
With AND/OR keywords, you can apply AND/OR logic to your query conditions. For example, to query all the position records whose name starts with John and whose Status is open, use the query syntax as given here:
List < Position__c > positionList = [SELECT name
FROM Position__c
WHERE Name LIKE 'John%'
AND Status__c = 'Open'
];
4. NOT
The NOT keyword is used for negation. For example, if you want to query all the Position records apart from the two Position Salesforce IDs in a given set, use NOT syntax as given here.
Set < Id > positionIdSet = new Set < Id > {
'a056000000WZEpq',
'a056000000WZYey'
}; // Note: Hardcoding IDs is not a best practice.
List < Position__c > positionList = [SELECT name
FROM Position__c
WHERE Id NOT IN: positionIdSet
];
5. ORDER BY
ORDER BY is used to sort the records in ascending(ASC) or descending(DESC) order. It is used after the WHERE clause. Also, you can specify if null records should be ordered at the beginning (NULLS FIRST) or end (NULLS LAST) of the results. By default, null values are sorted first.
List < Position__c > positionList = [SELECT Type__c, Comments__c
FROM Position__c
WHERE Status__c = 'Open'
ORDER BY Type__c ASC NULLS LAST
];
6. GROUP BY
GROUP BY is used with Aggregate functions to group the result set by single or multiple columns. This keyword also groups results for a particular field with minimal code.
SELECT Status__c, COUNT(Name)
FROM Position__c GROUP BY Status__c;
| Position Status | Number of Records |
|---|---|
| Open | 24 |
| Close | 13 |
| On Hold | 7 |
7. HAVING
HAVING is an optional clause that can be used in a SOQL query to filter results that Aggregate functions return.
You can use a HAVING clause with a GROUP BY clause to filter the results returned by aggregate functions.
List < AggregateResult > agrRes = [SELECT LeadSource, COUNT(Name)
FROM Lead
GROUP BY LeadSource HAVING COUNT(Name) > 100
];
8. LIMIT
The LIMIT keyword restricts the number of records returned by a SOQL query and is generally placed at the end of the query.
Student__c st1 = [SELECT Name FROM Student__c
WHERE student_name__c = 'shrey' LIMIT 1];
9. FOR UPDATE
The FOR UPDATE keyword locks the queried records from being updated by any other Apex Code or Transaction. When the records are locked, other users cannot update them.
List < Position__c > positionList = [SELECT Name, Comments__c
FROM Position__c
WHERE Status__c = 'Open'
FOR UPDATE
];
10. ALL ROWS
ALL ROWS keyword in a SOQL query retrieves all the records from the database, including those that have been deleted. This keyword is mostly used to undelete records from the Recycle Bin.
Need more support?
Get a head start with our FREE study notes!
Learn more and get all the answers you need at zero cost. Improve your skills using our detailed notes prepared by industry experts to help you excel.
