SOQL Keywords

 

Keywords Used In SOQL Queries

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, 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: 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 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 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'
}; // 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 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

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

9. FOR UPDATE

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

List < Position__c > positionList = [SELECT Name FROM Position__c WHERE isDeleted = true ALL ROWS ];