Some of the major keywords used in SOQL queries are:

1. IN:

IN keyword is used to query on 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;

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];