SOQL NOT IN, NOT EQUAL & NOT LIKE: Exclusion Patterns Explained

Excluding records is where SOQL gets quietly weird. Each of the three exclusion operators — NOT IN, != / <>, and NOT LIKE — picks the same answer for non-null data and a different answer for null. This guide is the cheat sheet I wish someone had handed me on day one.

NOT IN: the multi-value exclusion

Use NOT IN when you have a list of values (or a subquery returning a list) to exclude:

// Static list
List<Account> nonBanking = [
  SELECT Id, Name FROM Account
  WHERE Industry NOT IN ('Banking', 'Finance', 'Insurance')
];

// Subquery — every Account with no contact at example.com
List<Account> noExampleContacts = [
  SELECT Id FROM Account
  WHERE Id NOT IN (
    SELECT AccountId FROM Contact WHERE Email LIKE '%@example.com'
  )
];

// Bind variable list (the most common in real code)
Set<Id> excludeIds = new Set<Id>{'001xxx...', '001yyy...'};
List<Contact> results = [
  SELECT Id FROM Contact WHERE AccountId NOT IN :excludeIds
];

Three rules:

• The list type must match the field type. Set<Id> works for ID columns, List<String> for picklists/text.
• Subquery must return a single field.
• Null rows are excluded by default — see the gotcha section.

!= and <>: single-value comparison

When excluding just one value, use != or <> (both are accepted, both behave identically):

// Find every Account that isn't in Banking
List<Account> nonBanking = [
  SELECT Id, Name FROM Account WHERE Industry != 'Banking'
];

// <> works the same
List<Account> sameResult = [
  SELECT Id, Name FROM Account WHERE Industry <> 'Banking'
];

Both compile to the same query plan, so pick whichever style matches your team's conventions. I prefer != because it matches Apex/Java conventions — <> is a SQL-ism that doesn't exist anywhere else in the Salesforce stack.

NOT LIKE: pattern exclusion

For excluding partial-string matches, wrap a LIKE in parens with NOT:

// Accounts whose name doesn't contain "test"
SELECT Id, Name FROM Account
WHERE (NOT Name LIKE '%test%')

The parentheses around NOT Name LIKE '%test%' are required — Name NOT LIKE is a syntax error. See the deeper SOQL LIKE & NOT LIKE guide for wildcard rules and escaping.

The null gotcha (the most common bug)

All three exclusion operators silently exclude rows where the column is null. This trips up every Salesforce developer at least once.

// Looks like it returns every Contact except those at example.com.
// Actually returns Contacts whose Email is set AND not at example.com.
// Contacts with NULL email are NOT in the result.
SELECT Id FROM Contact WHERE Email != '[email protected]'

To include null rows, add an explicit OR clause:

SELECT Id FROM Contact
WHERE Email != '[email protected]' OR Email = NULL

Same for NOT IN:

SELECT Id FROM Account
WHERE Industry NOT IN ('Banking', 'Finance') OR Industry = NULL

If you want only rows where the field has a value, use IS NOT NULL (or != NULL) explicitly:

SELECT Id FROM Contact WHERE Email IS NOT NULL

Performance: NOT IN is harder to index

Salesforce's query optimizer handles IN efficiently — it uses indexes, breaks the work into bucketed lookups, and converges quickly. NOT IN is the inverse: the optimizer often has to scan more of the table to determine which rows don't match. This matters at scale.

Two mitigations for large data volumes:

1. Tighten the WHERE clause. Add a date filter, a status filter — anything that shrinks the candidate set before the NOT IN evaluates.
2. Split into two queries. Fetch the inclusion set first, then filter in Apex. The round-trip is often faster than a slow NOT IN on millions of rows.

// Slow on LDV: NOT IN against a 50k-row subquery
List<Account> slow = [
  SELECT Id FROM Account
  WHERE Id NOT IN (SELECT AccountId FROM Contact WHERE LastActivityDate < LAST_N_DAYS:90)
];

// Faster: two queries, set difference in Apex
Set<Id> staleAccounts = new Set<Id>();
for (Contact c : [SELECT AccountId FROM Contact WHERE LastActivityDate < LAST_N_DAYS:90]) {
  staleAccounts.add(c.AccountId);
}
List<Account> active = new List<Account>();
for (Account a : [SELECT Id FROM Account]) {
  if (!staleAccounts.contains(a.Id)) active.add(a);
}

The two-query version is also more debuggable — you can see exactly how many records each step produces.

Quick decision table

You want to...	Use
Exclude one value	field != 'X' or field <> 'X'
Exclude multiple discrete values	field NOT IN ('X', 'Y', 'Z')
Exclude based on another query	field NOT IN (SELECT ... FROM ...)
Exclude pattern matches	(NOT field LIKE '%X%')
Include null rows in any of above	OR field = NULL
Only rows where field has value	field IS NOT NULL

Common mistakes

• Forgetting nulls. Easily 80% of "where did those rows go?" bugs trace to this.
• Using NOT IN on huge subqueries. Rewrite as two queries if the inner set exceeds a few thousand rows.
• Wrapping a non-existent NOT LIKE. It's (NOT field LIKE pattern), not field NOT LIKE pattern.
• Mixing != with text picklists. Picklist API names are case-sensitive in some contexts. Always verify with SELECT DISTINCT field FROM Object first.

Master these three operators plus the null caveat and your reports, list views, and Apex queries will start matching expectations the first time — instead of after three rounds of "wait, where are those rows?".