Home / apex / soql-fundamentals

soql-fundamentals

Use this skill when writing or debugging SOQL queries: SELECT syntax, WHERE filters, ORDER BY, LIMIT, OFFSET, relationship queries (child-to-parent and parent-to-child), aggregate functions (COUNT, SUM, AVG, MIN, MAX), and date literals. Trigger keywords: soql, query, SELECT FROM WHERE. NOT for SOQL injection or CRUD/FLS enforcement — use apex/soql-security. NOT for query performance and index tuning — use data/soql-query-optimization. NOT for SOSL cross-object / full-text search — use data/sosl-search-patterns.

• v1.0.0
• Salesforce Spring '25+
• Updated 2026-04-04
• Security
• Reliability

• soql
• query
• apex
• data-access
• relationship-queries

Open the skill folder on GitHub

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.

In Apex, only FIELDS(STANDARD) is available. The documentation splits the keyword into bounded and unbounded forms and supports them in different places:

// Legal in Apex — bounded, no LIMIT required
List<Contact> cons = [SELECT FIELDS(STANDARD) FROM Contact];

// NOT legal in Apex at any LIMIT — unbounded forms are unsupported here,
// so this fails to compile / fails at Database.query() time regardless of LIMIT
// List<Account> accts = [SELECT FIELDS(ALL) FROM Account LIMIT 200];

Adding LIMIT 200 does not make FIELDS(ALL) work in Apex. The LIMIT n where n <= 200 rule is what unlocks the unbounded forms in the REST/SOAP APIs and the CLI — contexts where they are supported in the first place. Apex is simply not one of those contexts. If you need every field of a record in Apex, enumerate the fields explicitly, or build the field list at run time from Schema describes and use Database.query() (see the apex/dynamic-apex skill, and note that a describe-built field list does not inherit FIELDS()'s automatic FLS filtering — in a class saved at API 66.0 or earlier you must enforce FLS yourself with WITH USER_MODE or Security.stripInaccessible, while at API 67.0+ Database.query() defaults to user mode and throws QueryException on the first inaccessible field rather than filtering it out).

FIELDS() respects field-level security in every context where it is supported: the docs state it "only shows the fields that you have permission to access."

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. Writing FIELDS(ALL) or FIELDS(CUSTOM) in Apex at all — the unbounded forms are documented as "Not supported" for Apex, inline and dynamic. No LIMIT value rescues them; LIMIT 200 is the rule for REST/SOAP/CLI, which are separate contexts. In Apex use FIELDS(STANDARD) (which needs no LIMIT) or enumerate fields explicitly. Where the unbounded forms are supported, violating the row-limit rule returns MALFORMED_QUERY — there is no QUERY_TOO_LARGE status code in the API's StatusCode enumeration.
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 enforce FLS and CRUD in Apex queries with WITH USER_MODE (WITH SECURITY_ENFORCED is the legacy ≤56.0 idiom and does not compile at API 67.0+)
• 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

Package files

• references/examples.md
• references/gotchas.md
• references/llm-anti-patterns.md
• references/well-architected.md
• templates/soql-fundamentals-template.md
• scripts/check_soql_fundamentals.py