Writing SOQL queries is mechanically simple. Writing queries that scale to production data volumes, pass security review, and avoid governor limits takes practice. Build queries in Developer Console with the Query Editor, profile them with the Query Plan tool, and reduce them to bulk-friendly patterns before embedding in Apex.
- Open the Developer Console Query Editor
Setup > Developer Console > Query Editor tab. The editor provides syntax checking, autocomplete on object and field names, and instant execution. Use it to draft and refine queries before pasting them into Apex.
- Start with the SELECT clause and explicit field list
SELECT Id, Name, AccountId, StageName FROM Opportunity. List every field you need. There is no SELECT * in SOQL, and pulling unnecessary fields increases CPU and heap usage in Apex.
- Add the WHERE clause with selective filters
WHERE StageName = ''Closed Won'' AND CloseDate >= LAST_QUARTER. Filters on indexed fields (Id, Name, ForeignKey, External ID) are fast. Avoid filters on non-indexed fields with high cardinality unless the object is small.
- Add relationship traversal as needed
Child-to-parent: SELECT Id, Account.Industry FROM Contact. Parent-to-child: SELECT Id, (SELECT Id FROM Opportunities) FROM Account. Up to five levels deep. Use the relationship API name for custom relationships.
- Test the query in the Developer Console
Run with Execute. Check the result count, look at the data shape, and refine the filters. Use the Query Plan tool (Tools menu) to see how the platform will execute the query at scale.
- Embed in Apex with bind variables for security
List<Account> accts = [SELECT Id, Name FROM Account WHERE Industry = :industryParam]. Bind variables prevent SOQL injection and let the platform optimize the query plan.
- Use Database.query only when truly dynamic
Database.query(''SELECT Id FROM '' + objectName) works for runtime-determined object names. Always wrap user input with String.escapeSingleQuotes to prevent injection.
- Verify selectivity with the Query Plan tool
Query Plan flags non-selective queries that would scan too many rows. Selective queries use an index and run in milliseconds; non-selective queries can blow the timeout limit on large objects.
Determines which records the query returns. Filtering on indexed fields keeps the query selective and fast.
Dot notation for child-to-parent, sub-selects for parent-to-child. Up to five levels of depth supported per query.
The :variable syntax for embedding Apex values in SOQL. Prevents injection and improves query plan optimization.
- Standard SOQL caps at 50,000 records per query. Larger result sets need queryMore, Bulk API 2.0 query, or batch processing. Hitting the cap returns a partial result without warning.
- Non-selective queries on large objects throw NonSelectiveQueryException at runtime. Test queries with realistic production data volumes, not sandbox dev data, to catch this in pre-production.
- Each SOQL query in Apex counts against the 100-query governor limit per transaction. Queries inside loops are the leading cause of LimitException; always move queries outside loops.
- String-concatenated SOQL is a security hazard. Use bind variables in static SOQL, or String.escapeSingleQuotes on dynamic strings in Database.query. Security review flags every instance of concatenation.
- Aggregate queries return AggregateResult objects, not sObjects. Access fields by alias: ar.get(''totalAmount'') rather than ar.Amount. The mistake is common and confusing on first encounter.