SOQL Fundamentals

SOQL (Salesforce Object Query Language) reads records from one object at a time — plus related records through relationships. It looks like SQL but there are no joins, no SELECT * and every query counts toward governor limits.

Basic shape

SELECT Id, Name, AnnualRevenue
FROM Account
WHERE Industry = 'Technology' AND AnnualRevenue > 500000
ORDER BY AnnualRevenue DESC NULLS LAST, Name
LIMIT 50

• Field names are case-insensitive, string comparison with = is case-insensitive too.
• Strings use single quotes; numbers, booleans (true/false), null and dates are unquoted.
• != also matches records where the field is null — a common interview trap.
• Default sort: ASC NULLS FIRST, DESC NULLS LAST.

Filtering operators

SELECT Id, Name FROM Opportunity
WHERE IsClosed = false
  AND (Amount > 100000 OR Probability >= 80)
  AND CloseDate = NEXT_N_DAYS:30

Mixing AND and OR requires parentheses in SOQL.

Child-to-parent (dot notation)

Traverse up a lookup / master-detail with the relationship name (Account, Owner, custom Parent_Account__r), up to 5 levels.

SELECT FirstName, LastName, Account.Name, Account.Owner.Email
FROM Contact
WHERE Account.Industry = 'Banking'

Parent-to-child (subquery)

Traverse down using the child relationship name — usually the plural: Contacts, Opportunities, Cases, custom Invoices__r.

SELECT Name,
       (SELECT LastName, Email FROM Contacts ORDER BY LastName LIMIT 5)
FROM Account
WHERE Rating = 'Hot'

In Apex the child rows arrive as a list on each parent:

for (Account acc : [SELECT Name, (SELECT LastName FROM Contacts) FROM Account LIMIT 10]) {
    System.debug(acc.Name + ' has ' + acc.Contacts.size() + ' contacts');
}

Semi-joins and anti-joins

-- Accounts that HAVE at least one closed-won opportunity
SELECT Id, Name FROM Account
WHERE Id IN (SELECT AccountId FROM Opportunity WHERE IsWon = true)

-- Accounts WITHOUT any opportunities
SELECT Id, Name FROM Account
WHERE Id NOT IN (SELECT AccountId FROM Opportunity)

SOQL in Apex

// Static SOQL with a bind variable (safe from injection)
String industry = 'Energy';
List<Account> accs = [SELECT Id, Name FROM Account WHERE Industry = :industry];

// Bind a whole collection
Set<Id> ids = new Map<Id, Account>(accs).keySet();
List<Contact> cons = [SELECT Id, AccountId FROM Contact WHERE AccountId IN :ids];

// Single row: throws QueryException if 0 rows
Account a = [SELECT Id FROM Account WHERE Name = 'Acme' LIMIT 1];

SOQL vs SOSL

Interview questions

1. How do you query contacts with their account's name? → SELECT LastName, Account.Name FROM Contact.
2. What is a child relationship name and where do you find it? → On the lookup/master-detail field definition (Object Manager → field → "Child Relationship Name").
3. Why can != 'X' return more rows than you expect? → It includes nulls.
4. What happens when [SELECT … LIMIT 1] returns nothing into an sObject variable? → System.QueryException: List has no rows for assignment to SObject.