fbpx

Free Salesforce Developers Tutorials >

Chapter 6 - SOQL >

SOQL Keywords

SOQL Keywords

What You’ll Learn


S2 Labs

 

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 StatusNumber of Records
Open24
Close13
On Hold7

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

Download Study Material

Get access to exclusive study material for Salesforce Certification and ace your exams!

Download Now

Our Salesforce Certification Courses

Hey there! Glad you made it through our Salesforce Developer Training for beginners . But wait! We've got some high-in-demand Salesforce courses for you to take your Salesforce skills to the next level, making you a desired professional in the Salesforce job market.

Post a Comment

Your email address will not be published. Required fields are marked *