SOQL IN Operator: Fix Too Many SOQL Queries Error | SalesforceTutorial

Understanding the SOQL IN Operator and Query Limits

The SOQL IN operator allows you to filter records against multiple values in a single query, making it essential for bulkified Apex code. When used incorrectly, it can trigger the “System.LimitException: Too many SOQL queries: 101” error that occurs when your code exceeds Salesforce’s governor limit of 100 SOQL queries per transaction context.

Error: System.LimitException: Too many SOQL queries : 101

“System.LimitException: Too many SOQL queries: 101” errors occur when you exceed SOQL queries governor limit. The actual limit is 100 SOQL queries in a single synchronous transaction context (200 for asynchronous contexts like Batch Apex).

How SOQL IN Operator Prevents Query Limit Errors

The SOQL IN operator is your primary tool for bulkification. Instead of running multiple queries inside loops, use IN to query multiple records at once:

// BAD: Multiple queries in loop (hits 101 limit quickly)
for(Account acc : accounts) {
    List<Contact> contacts = [SELECT Id FROM Contact WHERE AccountId = :acc.Id];
    // Process contacts
}

// GOOD: Single query with IN operator
Set<Id> accountIds = new Set<Id>();
for(Account acc : accounts) {
    accountIds.add(acc.Id);
}
List<Contact> allContacts = [SELECT Id, AccountId FROM Contact WHERE AccountId IN :accountIds];

// Governor Limits Check:
// - 1 SOQL query used (limit: 100 synchronous)
// - Up to 50,000 records returned (query row limit)

SOQL IN Operator Syntax and Examples

The IN operator accepts collections (List, Set) or subqueries. Here are production-ready patterns:

Basic SOQL IN Syntax

// Using Set of Ids (most common pattern)
Set<Id> accountIds = new Set<Id>{'001xx000003DHPh', '001xx000003DHPi'};
List<Account> accounts = [SELECT Id, Name FROM Account WHERE Id IN :accountIds];

// Using List of strings
List<String> industries = new List<String>{'Technology', 'Healthcare', 'Finance'};
List<Account> techAccounts = [SELECT Id, Name FROM Account WHERE Industry IN :industries];

SOQL IN with Subqueries

// Find accounts with contacts created in last 30 days
List<Account> accounts = [
    SELECT Id, Name, Industry 
    FROM Account 
    WHERE Id IN (
        SELECT AccountId 
        FROM Contact 
        WHERE CreatedDate = LAST_N_DAYS:30
        AND AccountId != null
    )
    AND IsDeleted = false
];

SOQL OR vs IN Operator: When to Use Each

While both SOQL OR and IN operators filter records, they serve different purposes:

// Use IN for single field, multiple values
SELECT Id FROM Account WHERE Industry IN ('Tech', 'Finance', 'Healthcare')

// Use OR for different fields or complex logic
SELECT Id FROM Account WHERE (Industry = 'Tech' AND AnnualRevenue > 1000000) 
                          OR (Type = 'Customer' AND Rating = 'Hot')

Resolving Too Many SOQL Queries Error

Follow these patterns to stay within the 100-query limit:

1. Bulkify with SOQL IN Collections

// Collect all IDs first, then query once
public static void processAccountContacts(List<Account> accounts) {
    Set<Id> accountIds = new Set<Id>();
    for(Account acc : accounts) {
        accountIds.add(acc.Id);
    }
    
    // Single query instead of loop queries
    Map<Id, List<Contact>> contactsByAccount = new Map<Id, List<Contact>>();
    for(Contact con : [SELECT Id, AccountId, Email FROM Contact WHERE AccountId IN :accountIds]) {
        if(!contactsByAccount.containsKey(con.AccountId)) {
            contactsByAccount.put(con.AccountId, new List<Contact>());
        }
        contactsByAccount.get(con.AccountId).add(con);
    }
    
    // Process using the map
    for(Account acc : accounts) {
        List<Contact> relatedContacts = contactsByAccount.get(acc.Id);
        if(relatedContacts != null) {
            // Process contacts for this account
        }
    }
}

2. Use Asynchronous Processing

For operations that might exceed limits, use @future or Queueable Apex (200 query limit):

@future
public static void processLargeDataSet(Set<Id> recordIds) {
    // 200 SOQL queries available in async context
    List<Account> accounts = [SELECT Id, Name FROM Account WHERE Id IN :recordIds];
    // Process accounts
}

SOQL Includes vs IN Operator

The SOQL includes operator works with multi-select picklists, while IN works with any field type:

// INCLUDES for multi-select picklists
SELECT Id FROM Account WHERE Products__c INCLUDES ('Product A', 'Product B')

// IN for regular fields
SELECT Id FROM Account WHERE Industry IN ('Technology', 'Finance')

// IN with multi-select picklists checks exact matches
SELECT Id FROM Account WHERE Products__c IN ('Product A;Product B', 'Product C')

Governor Limits and Best Practices

Apex runs on a multi-tenant platform where the runtime engine enforces limits to prevent code from monopolizing shared resources. Key limits for SOQL queries:

• Synchronous context: 100 SOQL queries per transaction
• Asynchronous context: 200 SOQL queries per transaction
• Query rows: 50,000 records per SOQL query
• Total rows: 50,000 records retrieved per transaction

Production Best Practices

• Always bulkify: collect IDs, then use SOQL IN operator for single queries
• Avoid SOQL queries inside for loops
• Use selective queries with indexed fields (Id, Name, Email, external IDs)
• Consider query plan and performance with large datasets
• Implement proper null checks and exception handling
• Use Database.query() for dynamic SOQL when needed

Common SOQL IN Operator Patterns

Trigger Bulkification Pattern

trigger AccountTrigger on Account (after insert, after update) {
    // Collect Account IDs
    Set<Id> accountIds = new Set<Id>();
    for(Account acc : Trigger.new) {
        if(acc.Industry == 'Technology') {
            accountIds.add(acc.Id);
        }
    }
    
    if(!accountIds.isEmpty()) {
        // Single query with IN operator
        List<Contact> contacts = [
            SELECT Id, AccountId, Email 
            FROM Contact 
            WHERE AccountId IN :accountIds 
            AND Email != null
        ];
        
        // Process contacts
        for(Contact con : contacts) {
            con.Description = 'Updated via trigger';
        }
        update contacts;
    }
}

Batch Apex with SOQL IN

global class ProcessAccountsBatch implements Database.Batchable<sObject> {
    global Database.QueryLocator start(Database.BatchableContext bc) {
        return Database.getQueryLocator([
            SELECT Id, Name, Industry 
            FROM Account 
            WHERE Industry IN ('Technology', 'Finance', 'Healthcare')
            AND IsDeleted = false
        ]);
    }
    
    global void execute(Database.BatchableContext bc, List<Account> accounts) {
        // Process up to 200 accounts per batch
        // 200 SOQL queries available in batch context
    }
}

Debugging SOQL Query Limits

Use these techniques to monitor query usage:

// Check current query count
System.debug('SOQL queries used: ' + Limits.getQueries() + '/' + Limits.getLimitQueries());

// Check query rows
System.debug('Query rows: ' + Limits.getQueryRows() + '/' + Limits.getLimitQueryRows());

// In test classes, use Test.startTest() and Test.stopTest() to reset limits
@isTest
static void testBulkProcessing() {
    // Setup data
    List<Account> accounts = TestDataFactory.createAccounts(200);
    
    Test.startTest(); // Resets governor limits
    
    // Your code here - fresh 100 query limit
    AccountProcessor.processAccounts(accounts);
    
    Test.stopTest();
    
    // Assertions
}