Some of the major keywords used in SOQL queries are:
1. IN:
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, List of IDs, or sObjects which have populated ID fields.
Ex:
Set<Id> positionIdSet =
new Set<Id>{‘a056000000WZEpq’, ‘a056000000WZYey’};
List<Position__c> positionList = [SELECT name
FROM Position__c
WHERE Id IN : positionIdSet];
Note: Map cannot be used with IN keyword because it is not an iterable collection.
2. LIKE:
LIKE keyword allows selective queries using wildcards. For example, to query all the position records where 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 query syntax as given here:
List<Position__c> positionList = [SELECT name
FROM Position__c
WHERE Name LIKE ‘John%’ AND Status__c = ‘Open’];
4. NOT:
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’};
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
GROUP BY Status__c;
| Position Status | Number of Records |
| Open | 24 |
| Close | 13 |
| On Hold | 7 |
7. LIMIT:
LIMIT keyword puts a cap on the number of records a SOQL query should return. This keyword should always be used at the end of the SOQL query.
Student__c st1 = [SELECT name FROM student__c
WHERE student_name__c =’shrey’ limit 1];
FOR UPDATE keyword locks the queried records from being updated by any other Apex Code or Transaction. When the records are locked, other User cannot update them.
List<Position__c> positionList = [SELECT Name, Comments__c
8. FOR UPDATE:
FROM Position__c
WHERE Status__c = ‘Open’ FOR UPDATE];
9. 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.
List<Position__c> positionList = [SELECT Name
FROM Position__c
WHERE isDeleted = true ALL ROWS];
