How do you debug a SOQL query returning unexpected results in Apex?

Common causes and the diagnostic order.

1. Sharing context. Are you running with sharing and the user lacks access to records you expect? Check by running the same query as System Administrator — if results differ, sharing is the cause.

2. Field-Level Security. The query selects fields the running user can't see. The query succeeds but those fields come back null on the result.

3. Query is non-selective on a large table — Salesforce optimizer can return wrong results or time out. Check via Query Plan (in Developer Console -> Query Editor -> Explain). Hit indexed fields in the WHERE clause.

4. Implicit limits.

• Apex SOQL returns up to 50,000 rows per query unless using Database.QueryLocator (Batch) or Database.getQueryLocator. Beyond 50k, query throws or truncates.
• Aggregate queries are subject to GROUP BY rules — SELECT COUNT(Id), Industry FROM Account GROUP BY Industry.

5. Filter logic. OR/AND parentheses — Apex's WHERE clause grammar is its own; "where A and B or C" without parentheses can yield surprising results.

6. Date semantics. SOQL date literals (TODAY, LAST_N_DAYS:30) are timezone-dependent on the running user's locale. A LAST_N_DAYS:7 query for a user in PST and a user in IST returns different rows on the same data.

7. Null and unset values. WHERE Field__c = '' matches empty strings but not nulls. To match both: WHERE Field__c = '' OR Field__c = null.

8. SOQL aliasing trap. If you alias a field, references to it in HAVING or ORDER BY use the alias.

9. Polymorphic relationships. WHAT.Type is needed to distinguish Account vs Opportunity vs Custom__c values.

Diagnostic tools:

• Developer Console -> Query Editor — run the query interactively as the running user.
• Debug log — add System.debug([SELECT...]) and inspect the log.
• Query Plan tool — verify selectivity and index usage.
• Reports as a comparison — build the same filter as a Salesforce report; if results differ, your SOQL filter doesn't match the report.

The most common surprise: thinking the running user can see records they can't. Always check sharing first.