Soql Fundamentals

SOQL Fundamentals

This skill activates when a practitioner needs to write, debug, or understand Salesforce Object Query Language (SOQL) queries — covering SELECT syntax, filtering, sorting, pagination, relationship traversal, and aggregate functions. It does NOT cover SOQL injection prevention (see soql-security) or query plan optimization (see soql-query-optimization).

Before Starting

Gather this context before working on anything in this domain:

• Confirm whether the query runs in Apex (requires [ ] bracket syntax) or an external API context (REST, SOAP, CLI).
• Identify whether the query needs to traverse a relationship (child-to-parent via dot notation or parent-to-child via subquery).
• Check the governor limit context: synchronous Apex allows 100 SOQL queries per transaction; each query can return up to 50,000 rows.
• SOQL does not support arbitrary SQL JOINs, wildcards in field lists (SELECT *), or calculation expressions — operations that are standard in SQL but unavailable in SOQL.

Core Concepts

1. SELECT … FROM … WHERE Syntax

SOQL queries follow this structure (clauses in square brackets are optional):

SELECT fieldList [subquery] [...]
FROM objectType [,...]
[USING SCOPE filterScope]
[WHERE conditionExpression]
[WITH [DATA CATEGORY] filteringExpression]
[GROUP BY { fieldGroupByList | ROLLUP(...) | CUBE(...) }
  [HAVING havingConditionExpression]]
[ORDER BY fieldOrderByList {ASC|DESC} [NULLS {FIRST|LAST}]]
[LIMIT numberOfRowsToReturn]
[OFFSET numberOfRowsToSkip]
[FOR VIEW | FOR REFERENCE]
[FOR UPDATE]

In Apex, inline SOQL is wrapped in square brackets:

List<Account> accounts = [SELECT Id, Name FROM Account WHERE Industry = 'Media' LIMIT 100];

Dynamic SOQL uses Database.query():

String query = 'SELECT Id, Name FROM Account WHERE Industry = :ind LIMIT 100';
List<Account> accounts = Database.query(query);

Key constraint: SOQL statements cannot exceed 100,000 characters in length. The 100,000-character limit does not apply to dynamic SOQL in Apex.

2. WHERE Clause — Filters and Date Literals

The WHERE clause supports these comparison operators: =, !=, <, <=, >, >=, LIKE, IN, NOT IN, INCLUDES, EXCLUDES.

String filtering with LIKE: % matches zero or more characters; _ matches exactly one character. Comparisons on standard fields are case-insensitive.

Date literals allow relative date filtering without hardcoded dates:

Example — open opportunities closing this quarter:

SELECT Id, Name, CloseDate FROM Opportunity
WHERE CloseDate = THIS_FISCAL_QUARTER AND IsClosed = false

IN operator for multi-value filters:

SELECT Name FROM Account WHERE BillingState IN ('California', 'New York')

Strings in WHERE clauses cannot exceed 4,000 characters (this limit does not apply to Apex IN clauses).

3. Relationship Queries

SOQL supports two types of relationship traversal:

Child-to-parent (dot notation): Traverse up to five levels. Use the relationship name with dot notation:

SELECT Id, Name, Account.Name, Account.BillingCity FROM Contact WHERE Account.Industry = 'Media'

For custom relationship fields, append __r instead of __c:

SELECT Id, Mother_of_Child__r.FirstName__c FROM Daughter__c

Parent-to-child (subquery): Query related child records in a nested SELECT. Use the child relationship name (plural for standard, __r for custom):

SELECT Name, (SELECT LastName, Email FROM Contacts) FROM Account WHERE Industry = 'Media'

Custom child relationship:

SELECT Name, (SELECT Name FROM Line_Items__r) FROM Merchandise__c WHERE Name LIKE 'Acme%'

Relationship query limits (from SOQL and SOSL Reference):

• Maximum relationship levels in a query: 5 (child-to-parent traversal)
• Maximum parent-to-child subqueries per query: 20
• A subquery cannot itself contain a subquery

4. Aggregate Functions and GROUP BY

SOQL aggregate functions: COUNT(), COUNT(fieldName), COUNT_DISTINCT(), AVG(), MIN(), MAX(), SUM().

All aggregate functions ignore null values, except COUNT() and COUNT(Id).

-- Count records matching criteria
SELECT COUNT() FROM Account WHERE Industry = 'Media'

-- Aggregate by group
SELECT LeadSource, COUNT(Name) FROM Lead GROUP BY LeadSource

-- Filter groups with HAVING (like a WHERE for aggregates)
SELECT LeadSource, COUNT(Name) FROM Lead
GROUP BY LeadSource HAVING COUNT(Name) > 100

-- Find duplicates
SELECT Name, COUNT(Id) FROM Account GROUP BY Name HAVING COUNT(Id) > 1

Key rule: you cannot use LIMIT in a query that uses an aggregate function without also using GROUP BY. MAX(CreatedDate) FROM Account LIMIT 1 is invalid. Adding GROUP BY SomeField LIMIT 5 is valid.

5. ORDER BY, LIMIT, and OFFSET

ORDER BY — Results are not guaranteed to be in any consistent order unless you include ORDER BY. For stable pagination, always include a tiebreaker (e.g., Id):

SELECT Name, Industry FROM Account ORDER BY Industry ASC, Id NULLS LAST

NULLS FIRST (default) and NULLS LAST control where null values appear.

LIMIT — Returns at most n rows. Maximum 50,000 records per query in most contexts.

SELECT Name FROM Account WHERE Industry = 'Media' LIMIT 125

OFFSET — Skips the first n rows for server-side pagination. Maximum offset is 2,000 rows. Always pair with ORDER BY for consistent results:

SELECT Name, Id FROM Merchandise__c ORDER BY Name LIMIT 100 OFFSET 100

OFFSET is NOT allowed in Bulk API or Streaming API queries.

Common Patterns

Pattern 1 — Semi-Join (IN Subquery)

When to use: Filter parent records based on a condition on child records, without loading all child data.

-- Accounts that have at least one closed-lost opportunity
SELECT Id, Name FROM Account
WHERE Id IN (
  SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Lost'
)

Constraint: The subquery must return a single foreign key or ID field. The selected column cannot use dot notation (no relationship traversal inside a subquery field list).

Pattern 2 — Anti-Join (NOT IN Subquery)

When to use: Find records that do NOT have matching child/related records.

-- Accounts with no open opportunities
SELECT Id, Name FROM Account
WHERE Id NOT IN (
  SELECT AccountId FROM Opportunity WHERE IsClosed = false
)

Pattern 3 — FIELDS() Keyword for Schema Exploration

When to use: Discover all fields on an object without knowing field names. Available since API v51.0.

SELECT FIELDS(ALL) FROM Account LIMIT 200
SELECT FIELDS(STANDARD) FROM Contact
SELECT FIELDS(CUSTOM) FROM Account LIMIT 200

FIELDS(ALL) respects FLS — it only returns fields the running user can read. Always pair with LIMIT when using FIELDS(ALL) or FIELDS(CUSTOM) to avoid hitting governor limits.

Decision Guidance

Recommended Workflow

Step-by-step instructions for an AI agent or practitioner activating this skill:

1. Gather context — confirm the org edition, relevant objects, and current configuration state
2. Review official sources — check the references in this skill's well-architected.md before making changes
3. Implement or advise — apply the patterns from Core Concepts and Common Patterns sections above
4. Validate — run the skill's checker script and verify against the Review Checklist below
5. Document — record any deviations from standard patterns and update the template if needed

Review Checklist

Run through these before marking SOQL work complete:

• Every field in SELECT has at least read-level FLS for the running user (consider WITH USER_MODE in Apex)
• WHERE clause fields are indexed or selective to avoid full table scans
• Relationship traversal does not exceed 5 levels (child-to-parent) or 20 subqueries (parent-to-child)
• LIMIT is present on queries that could return large result sets inside Apex triggers
• ORDER BY tiebreaker (e.g., Id) added when using OFFSET for pagination
• Date literals used instead of hardcoded date strings for relative date filtering
• SOQL injection risk reviewed if any part of the query is dynamically constructed (see soql-security skill)

Salesforce-Specific Gotchas

Non-obvious platform behaviors that cause real production problems:

1. Results are unordered without ORDER BY — The Salesforce query engine does not guarantee a consistent row order unless ORDER BY is specified. Code that relies on "first record returned" without ORDER BY can return different records in different transactions or releases. Always add ORDER BY + Id as a tiebreaker.

2. OFFSET max is 2,000 rows — Requesting OFFSET beyond 2,000 raises NUMBER_OUTSIDE_VALID_RANGE. For result sets larger than 2,000, use queryMore() (SOAP) or nextRecordsUrl (REST) instead of incrementing OFFSET. OFFSET is not a replacement for cursor-based pagination on large data sets.

3. Aggregate functions cannot use LIMIT without GROUP BY — SELECT MAX(CreatedDate) FROM Account LIMIT 1 is invalid. The pattern that looks like "get the most recent record" does not work this way in SOQL — use ORDER BY CreatedDate DESC LIMIT 1 on a full query instead.

4. FIELDS(ALL) without LIMIT causes row-count failures — SELECT FIELDS(ALL) FROM Account returns all fields for every record. Without LIMIT, this easily blows the 50,000-row limit or produces a QUERY_TOO_LARGE error. Always pair FIELDS() with LIMIT.

5. Custom relationship names use __r not __c — Traversing a custom lookup field Parent__c in a query uses Parent__r.FieldName__c, not Parent__c.FieldName__c. Using __c in dot notation causes a MALFORMED_QUERY error that is easy to misread.

Output Artifacts

Related Skills

• soql-security — Use alongside this skill to add WITH USER_MODE or WITH SECURITY_ENFORCED to enforce FLS and CRUD in Apex queries
• soql-query-optimization — Use when the query is correct but slow or hitting CPU limits due to non-selective filters or missing indexes
• apex-cpu-and-heap-optimization — Use when SOQL governor limits (100 queries, 50,000 rows) are being hit in a transaction