SOQL — Salesforce Object Query Language
Complete Guide

SELECT Field1, Field2, Field3
FROM   ObjectApiName
WHERE  Condition
ORDER BY FieldName ASC|DESC
LIMIT  200

SELECT Id, Name, Industry, AnnualRevenue, Phone
FROM   Account
LIMIT  200

SELECT Id, Name, Project_Status__c, Budget__c, Deadline__c
FROM   Project__c
WHERE  Project_Status__c = 'Active'
ORDER BY Deadline__c ASC
LIMIT  50

SELECT Id, FirstName, LastName, Email, Title, Department,
       AccountId, Account.Name, LeadSource
FROM   Contact
WHERE  Email != null
ORDER BY LastName ASC, FirstName ASC
LIMIT  500

-- AND: both conditions must be true
SELECT Id, Name, Amount
FROM   Opportunity
WHERE  StageName = 'Closed Won'
  AND  Amount > 100000
  AND  CloseDate = THIS_YEAR

-- OR: at least one condition must be true
SELECT Id, Name, Industry
FROM   Account
WHERE  Industry = 'Technology'
  OR   Industry = 'Finance'

-- NOT: negates a condition
SELECT Id, LastName, Email
FROM   Contact
WHERE  NOT Email = null

-- Starts with 'Acme'
SELECT Id, Name FROM Account WHERE Name LIKE 'Acme%'

-- Contains 'cloud' anywhere in the name
SELECT Id, Name FROM Account WHERE Name LIKE '%cloud%'

-- Email domain matches (ends with @salesforce.com)
SELECT Id, Email FROM Contact
WHERE  Email LIKE '%@salesforce.com'

-- Opportunities closing in the next 30 days
SELECT Id, Name, StageName, CloseDate, Amount
FROM   Opportunity
WHERE  CloseDate = NEXT_N_DAYS:30
  AND  IsClosed = false

-- Cases created in the last 7 days that are still open
SELECT Id, CaseNumber, Subject, Status, CreatedDate
FROM   Case
WHERE  CreatedDate >= LAST_N_DAYS:7
  AND  Status != 'Closed'

-- Leads created this month
SELECT Id, FirstName, LastName, Company, LeadSource
FROM   Lead
WHERE  CreatedDate = THIS_MONTH
ORDER BY CreatedDate DESC

-- Query Contacts and include their parent Account's name and industry
SELECT Id, FirstName, LastName, Email,
       Account.Name,          -- parent Account Name
       Account.Industry,      -- parent Account Industry
       Account.BillingCity
FROM   Contact
WHERE  Account.Industry = 'Technology'

-- Query Opportunities and walk up to the Account's Owner
SELECT Id, Name, StageName, Amount,
       Account.Name,
       Account.Owner.Name,    -- two hops up the relationship
       Account.Owner.Email
FROM   Opportunity
WHERE  StageName = 'Closed Won'
  AND  CloseDate = THIS_YEAR

-- Custom relationship: Case with custom lookup to Product__c
SELECT Id, Subject, Related_Product__r.Name, Related_Product__r.Price__c
FROM   Case
WHERE  Related_Product__r.IsActive__c = true

-- Accounts with their related Contacts
SELECT Id, Name, Industry,
  (SELECT Id, FirstName, LastName, Email
   FROM   Contacts)           -- 'Contacts' is the child relationship name
FROM   Account
WHERE  Industry = 'Technology'
LIMIT  50

-- Accounts with their Opportunities that closed this year
SELECT Id, Name,
  (SELECT Id, Name, StageName, Amount, CloseDate
   FROM   Opportunities
   WHERE  StageName = 'Closed Won'
   AND    CloseDate = THIS_YEAR
   ORDER BY Amount DESC)
FROM   Account
WHERE  Name LIKE '%Acme%'

-- Custom parent-to-child: Project__c with its Tasks__r
SELECT Id, Name, Project_Status__c,
  (SELECT Id, Name, Due_Date__c, Assigned_To__r.Name
   FROM   Tasks__r          -- __r for custom relationships
   WHERE  Is_Complete__c = false)
FROM   Project__c

List<Account> accounts = [
    SELECT Id, Name,
        (SELECT Id, LastName, Email FROM Contacts)
    FROM Account
    WHERE Industry = 'Technology'
    LIMIT 20
];

for (Account acc : accounts) {
    // getSObjects() returns the child list — may be null
    List<Contact> contacts = acc.getSObjects('Contacts');
    if (contacts != null) {
        for (Contact c : contacts) {
            System.debug(acc.Name + ' — ' + c.LastName);
        }
    }
}

-- Count of Opportunities by Stage
SELECT StageName, COUNT(Id) opptyCount
FROM   Opportunity
GROUP BY StageName

-- Total and average opportunity amount by Account
SELECT AccountId, Account.Name,
       SUM(Amount) totalRevenue,
       AVG(Amount) avgDeal,
       COUNT(Id)   dealCount,
       MAX(Amount) largestDeal
FROM   Opportunity
WHERE  StageName = 'Closed Won'
  AND  CloseDate = THIS_YEAR
GROUP BY AccountId, Account.Name
ORDER BY SUM(Amount) DESC
LIMIT  20

-- Cases per priority this month
SELECT Priority, COUNT(Id) caseCount,
       MIN(CreatedDate) earliestCase
FROM   Case
WHERE  CreatedDate = THIS_MONTH
GROUP BY Priority

-- Accounts with more than 10 open opportunities
SELECT AccountId, COUNT(Id) openDeals
FROM   Opportunity
WHERE  IsClosed = false
GROUP BY AccountId
HAVING COUNT(Id) > 10

-- Product categories with total sales above $500,000
SELECT Category__c, SUM(Amount__c) totalSales
FROM   Order_Line__c
GROUP BY Category__c
HAVING SUM(Amount__c) > 500000
ORDER BY SUM(Amount__c) DESC

List<AggregateResult> results = [
    SELECT StageName, COUNT(Id) cnt, SUM(Amount) total
    FROM Opportunity
    WHERE CloseDate = THIS_YEAR
    GROUP BY StageName
];

for (AggregateResult ar : results) {
    String stage  = (String)  ar.get('StageName');
    Integer count = (Integer) ar.get('cnt');
    Decimal total = (Decimal) ar.get('total');
    System.debug(stage + ': ' + count + ' deals — $' + total);
}

-- Sort by close date descending, then by amount descending
SELECT Id, Name, CloseDate, Amount
FROM   Opportunity
ORDER BY CloseDate DESC, Amount DESC
LIMIT  100

-- Put records where Email is null at the end
SELECT Id, LastName, Email
FROM   Contact
ORDER BY Email ASC NULLS LAST

-- Page 1: records 1–20
SELECT Id, Name, Industry FROM Account
ORDER BY Name ASC
LIMIT  20 OFFSET 0

-- Page 2: records 21–40
SELECT Id, Name, Industry FROM Account
ORDER BY Name ASC
LIMIT  20 OFFSET 20

-- Page 3: records 41–60
SELECT Id, Name, Industry FROM Account
ORDER BY Name ASC
LIMIT  20 OFFSET 40

-- Contacts whose Account has at least one Closed Won Opportunity
SELECT Id, FirstName, LastName, Email
FROM   Contact
WHERE  AccountId IN (
    SELECT AccountId
    FROM   Opportunity
    WHERE  StageName = 'Closed Won'
)

-- Users who are members of a specific Permission Set
SELECT Id, Name, Email
FROM   User
WHERE  Id IN (
    SELECT AssigneeId
    FROM   PermissionSetAssignment
    WHERE  PermissionSet.Name = 'Salesforce_API_Access'
)

-- Accounts that have NO Opportunities at all
SELECT Id, Name, Industry, CreatedDate
FROM   Account
WHERE  Id NOT IN (
    SELECT AccountId
    FROM   Opportunity
    WHERE  AccountId != null
)

-- Contacts who have no open Cases
SELECT Id, Name, Email
FROM   Contact
WHERE  Id NOT IN (
    SELECT ContactId
    FROM   Case
    WHERE  Status != 'Closed'
    AND    ContactId != null
)

// Single variable binding
String targetIndustry = 'Technology';
List<Account> techAccounts = [
    SELECT Id, Name, Phone
    FROM   Account
    WHERE  Industry = :targetIndustry
    ORDER BY Name
    LIMIT  200
];

// List/Set binding — great for IN clauses
Set<Id> accountIds = new Set<Id>{'001xx000001A2bCAAE', '001xx000001B3cDAAE'};
List<Account> specificAccounts = [
    SELECT Id, Name, Industry, AnnualRevenue
    FROM   Account
    WHERE  Id IN :accountIds
];

// Integer/Decimal binding
Decimal minimumAmount = 50000;
Date   cutoffDate    = Date.today().addDays(-30);
List<Opportunity> bigRecentDeals = [
    SELECT Id, Name, Amount, CloseDate, StageName
    FROM   Opportunity
    WHERE  Amount >= :minimumAmount
    AND    CloseDate >= :cutoffDate
    ORDER BY Amount DESC
];

// Pattern 1: Iterate one record at a time (200 fetched per batch internally)
for (Contact c : [
    SELECT Id, Email, DoNotContact__c
    FROM   Contact
    WHERE  HasOptedOutOfEmail = false
]) {
    // process each contact
    c.Last_Contacted__c = Date.today();
}

// Pattern 2: Chunked list — process 200 records at a time explicitly
List<Account> toUpdate = new List<Account>();
for (List<Account> chunk : [
    SELECT Id, Name, Rating
    FROM   Account
    WHERE  Industry = 'Technology'
]) {
    for (Account acc : chunk) {
        acc.Rating = 'Hot';
        toUpdate.add(acc);
    }
    if (toUpdate.size() >= 200) {
        update toUpdate;
        toUpdate.clear();
    }
}
if (!toUpdate.isEmpty()) { update toUpdate; }

String objectName = 'Account';
String filterField = 'Industry';
// ALWAYS escape user-supplied strings
String filterValue = String.escapeSingleQuotes(userInput);

String soql = 'SELECT Id, Name FROM '
            + objectName
            + ' WHERE ' + filterField
            + ' = \'' + filterValue + '\' LIMIT 200';

List<SObject> results = Database.query(soql);

// BAD: SOQL inside a loop — hits governor limits fast
for (Opportunity opp : opportunities) {
    Account acc = [SELECT Id, Name FROM Account WHERE Id = :opp.AccountId]; // WRONG
}

// GOOD: One query outside the loop, Map lookup inside
Set<Id> accountIds = new Set<Id>();
for (Opportunity opp : opportunities) {
    accountIds.add(opp.AccountId);
}
Map<Id, Account> accountMap = new Map<Id, Account>([
    SELECT Id, Name, Industry
    FROM   Account
    WHERE  Id IN :accountIds
]);
for (Opportunity opp : opportunities) {
    Account acc = accountMap.get(opp.AccountId); // safe O(1) lookup
}

SELECT Id, Name, Salary__c
FROM   Employee__c
WITH SECURITY_ENFORCED
WHERE  Department__c = 'Engineering'
LIMIT  100

// RISKY: throws QueryException if no record found
Account acc = [SELECT Id, Name FROM Account WHERE Name = 'Acme' LIMIT 1];

// SAFE: use a List, check size first
List<Account> accs = [SELECT Id, Name FROM Account WHERE Name = 'Acme' LIMIT 1];
if (!accs.isEmpty()) {
    Account acc = accs[0];
    // safe to use acc here
}