sObjects, SOQL and SOSL · Lesson 15 of 41
SOQL Queries in Apex
SOQL (Salesforce Object Query Language) reads records. It looks like SQL's SELECT, but it's designed around Salesforce objects and relationships: there's no SELECT *, no free-form JOIN, and every query counts against a governor limit. In Apex you write it directly in square brackets and get typed records back.
In this lesson
- The sample data for this chapter
- SELECT, WHERE and comparison operators
- LIKE, IN and NULL
- Bind variables
- Date literals, ORDER BY, LIMIT and OFFSET
The sample data
All examples in this chapter query the same small dataset, created by this class. It's a normal Apex class you can deploy to your own practice org:
// SampleData.cls
public with sharing class SampleData {
public static List<Account> create() {
List<Account> accounts = new List<Account>{
new Account(Name = 'Acme Corp', Industry = 'Energy', BillingCity = 'Pune', BillingCountry = 'India',
AnnualRevenue = 5000000, Rating = 'Hot', NumberOfEmployees = 1200),
new Account(Name = 'Globex', Industry = 'Retail', BillingCity = 'Mumbai', BillingCountry = 'India',
AnnualRevenue = 1200000, Rating = 'Warm', NumberOfEmployees = 300),
new Account(Name = 'Initech', Industry = 'Technology', BillingCity = 'Berlin', BillingCountry = 'Germany',
AnnualRevenue = 800000, Rating = 'Cold', NumberOfEmployees = 85),
new Account(Name = 'Umbrella Health', Industry = 'Healthcare', BillingCity = 'Delhi', BillingCountry = 'India',
NumberOfEmployees = 40)
};
insert accounts;
insert new List<Contact>{
new Contact(FirstName = 'Asha', LastName = 'Verma', Title = 'CTO', Email = 'asha@acme.example', AccountId = accounts[0].Id),
new Contact(FirstName = 'Ravi', LastName = 'Kumar', Title = 'Buyer', Email = 'ravi@acme.example', AccountId = accounts[0].Id),
new Contact(FirstName = 'Neha', LastName = 'Rao', Title = 'CFO', Email = 'neha@globex.example', AccountId = accounts[1].Id),
new Contact(FirstName = 'Jonas', LastName = 'Weber', Title = 'Engineer', AccountId = accounts[2].Id),
new Contact(FirstName = 'Meera', LastName = 'Iyer', Title = 'Consultant')
};
Date today = Date.today();
insert new List<Opportunity>{
new Opportunity(Name = 'Acme renewal', AccountId = accounts[0].Id, StageName = 'Negotiation/Review', Amount = 250000, CloseDate = today.addDays(20)),
new Opportunity(Name = 'Acme add-on', AccountId = accounts[0].Id, StageName = 'Closed Won', Amount = 40000, CloseDate = today.addDays(-10)),
new Opportunity(Name = 'Globex pilot', AccountId = accounts[1].Id, StageName = 'Prospecting', Amount = 15000, CloseDate = today.addDays(45)),
new Opportunity(Name = 'Globex rollout', AccountId = accounts[1].Id, StageName = 'Closed Won', Amount = 120000, CloseDate = today.addDays(-40)),
new Opportunity(Name = 'Initech upgrade', AccountId = accounts[2].Id, StageName = 'Closed Lost', Amount = 60000, CloseDate = today.addDays(-5))
};
insert new List<Case>{
new Case(Subject = 'Login issue', Priority = 'High', Status = 'New', AccountId = accounts[0].Id),
new Case(Subject = 'Invoice copy', Priority = 'Low', Status = 'Closed', AccountId = accounts[0].Id),
new Case(Subject = 'Delivery delayed', Priority = 'Medium', Status = 'Working', AccountId = accounts[1].Id)
};
return accounts;
}
}
SELECT and WHERE
SampleData.create();
List<Account> indian = [
SELECT Name, BillingCity, AnnualRevenue
FROM Account
WHERE BillingCountry = 'India' AND AnnualRevenue >= 1000000
ORDER BY AnnualRevenue DESC
];
for (Account a : indian) {
System.debug(a.Name + ' (' + a.BillingCity + ') ' + a.AnnualRevenue);
}
// OR needs parentheses when mixed with AND
List<Account> mixed = [
SELECT Name FROM Account
WHERE BillingCountry = 'India' AND (Rating = 'Hot' OR NumberOfEmployees < 100)
ORDER BY Name
];
System.debug('Hot or small Indian accounts: ' + mixed.size());
for (Account a : mixed) System.debug(' - ' + a.Name);
Acme Corp (Pune) 5000000 Globex (Mumbai) 1200000 Hot or small Indian accounts: 2 - Acme Corp - Umbrella Health
| Operator | Example |
|---|---|
= != < <= > >= | Amount >= 50000 |
LIKE with % and _ | Name LIKE 'Acme%' (case-insensitive) |
IN / NOT IN | StageName IN ('Prospecting', 'Qualification') |
= null / != null | Email = null |
INCLUDES / EXCLUDES | for multi-select picklists |
LIKE, IN, NULL and bind variables
A bind variable is an Apex variable used inside a query with a colon. It's the safe way to put values into SOQL – no string building, no injection risk – and it works with single values and collections:
SampleData.create();
String prefix = 'Glo';
String pattern = prefix + '%';
System.debug([SELECT Name FROM Account WHERE Name LIKE :pattern].size() + ' account(s) starting with ' + prefix);
Set<String> openStages = new Set<String>{ 'Prospecting', 'Negotiation/Review' };
for (Opportunity o : [SELECT Name, StageName FROM Opportunity WHERE StageName IN :openStages ORDER BY Name]) {
System.debug(o.Name + ' – ' + o.StageName);
}
List<Contact> noEmail = [SELECT FirstName, LastName FROM Contact WHERE Email = null ORDER BY LastName];
System.debug('Without email: ' + noEmail.size());
Decimal minAmount = 50000;
Integer bigDeals = [SELECT COUNT() FROM Opportunity WHERE Amount > :minAmount];
System.debug('Deals over ' + minAmount + ': ' + bigDeals);
// binding an expression and a record's Id
Account acme = [SELECT Id FROM Account WHERE Name = 'Acme Corp' LIMIT 1];
System.debug('Acme contacts: ' + [SELECT COUNT() FROM Contact WHERE AccountId = :acme.Id]);
1 account(s) starting with Glo Acme renewal – Negotiation/Review Globex pilot – Prospecting Without email: 2 Deals over 50000: 3 Acme contacts: 2
Date literals
SOQL has built-in relative dates, evaluated in the running user's time zone. They save you from computing date ranges by hand:
SampleData.create();
System.debug('Closing in next 30 days: ' + [SELECT COUNT() FROM Opportunity WHERE CloseDate = NEXT_N_DAYS:30]);
System.debug('Closed in last 30 days: ' + [SELECT COUNT() FROM Opportunity WHERE CloseDate = LAST_N_DAYS:30]);
System.debug('Close date in the past: ' + [SELECT COUNT() FROM Opportunity WHERE CloseDate < TODAY]);
System.debug('Created today: ' + [SELECT COUNT() FROM Opportunity WHERE CreatedDate = TODAY]);
Date cutoff = Date.today().addDays(30); // or bind your own dates
System.debug('Before cutoff: ' + [SELECT COUNT() FROM Opportunity WHERE CloseDate <= :cutoff]);
Closing in next 30 days: 1 Closed in last 30 days: 2 Close date in the past: 3 Created today: 5 Before cutoff: 4
Other useful literals: YESTERDAY, THIS_WEEK, LAST_MONTH, THIS_QUARTER, THIS_YEAR, LAST_N_MONTHS:6, THIS_FISCAL_YEAR. Datetime fields like CreatedDate compare against literals too.
ORDER BY, LIMIT, OFFSET and single records
SampleData.create();
List<Account> page1 = [SELECT Name FROM Account ORDER BY AnnualRevenue DESC NULLS LAST LIMIT 2];
List<Account> page2 = [SELECT Name FROM Account ORDER BY AnnualRevenue DESC NULLS LAST LIMIT 2 OFFSET 2];
System.debug('Page 1: ' + page1[0].Name + ', ' + page1[1].Name);
System.debug('Page 2: ' + page2[0].Name + ', ' + page2[1].Name);
// assigning to a single record: exactly one row, or an exception
Account top = [SELECT Name FROM Account ORDER BY AnnualRevenue DESC NULLS LAST LIMIT 1];
System.debug('Top account: ' + top.Name);
// safer when zero rows is possible
List<Account> found = [SELECT Name FROM Account WHERE Name = 'Nobody Ltd' LIMIT 1];
System.debug(found.isEmpty() ? 'Not found' : found[0].Name);
// field values come back; fields you didn't select are not available
Account partial = [SELECT Name FROM Account WHERE Name = 'Globex'];
try {
System.debug(partial.Industry);
} catch (SObjectException e) {
System.debug(e.getMessage());
}
Page 1: Acme Corp, Globex Page 2: Initech, Umbrella Health Top account: Acme Corp Not found SObject row was retrieved via SOQL without querying the requested field: Account.Industry
SOQL habits of experienced developers
- Select only the fields you use – it's faster and uses less heap.
- Filter on indexed fields (Id, Name, lookups, external Ids, CreatedDate) for large objects.
- Add
LIMITwhenever you only need some rows. - Never query inside a loop; one query with
IN :idsreplaces hundreds. - Add
WITH USER_MODEto enforce the user's permissions (see security).
Practice
Try it yourself
Write a query for open Cases with High priority on accounts in India, newest first.
Show answer
[SELECT Subject, CreatedDate FROM Case WHERE IsClosed = false AND Priority = 'High' AND Account.BillingCountry = 'India' ORDER BY CreatedDate DESC]– filtering on a parent field with dot notation is covered in the next lesson.Why prefer
WHERE Name = :searchTermoverDatabase.query('… WHERE Name = \'' + searchTerm + '\'')?Show answer
Bind variables are automatically escaped, so a value like
' OR Name != 'can't change the query (SOQL injection), and the code is checked at compile time.
Quick check
Is LIKE case-sensitive in SOQL?
No.
How do you use an Apex variable in SOQL?
As a bind variable with a colon: WHERE Id IN :accountIds.
What happens when a single-record query finds no rows?
A QueryException: List has no rows for assignment to SObject.
Summary
- SOQL selects named fields from one object, with WHERE, ORDER BY, LIMIT and OFFSET.
- Use bind variables for values and collections – never concatenate input.
- Date literals like LAST_N_DAYS:30 and THIS_QUARTER simplify date filters.
- Query into lists when zero rows is possible; select only the fields you need.
Finished this lesson?
Mark it complete to track your progress, then carry on with the next lesson.