SOQL NOT IN and NOT EQUAL (!=): Exclusion Syntax and Null Rows

SOQL gives you three ways to exclude records: NOT IN for a list or a subquery, != (or <>) for a single value, and (NOT field LIKE ...) for a pattern. The detail that catches people coming from SQL is null handling. In SOQL, every one of these returns rows where the field is null.

Every query on this page was run against a live org with 8 accounts, 4 of them with a blank Industry. The counts below are what came back.

NOT IN: exclude a list of values

// Literal list
List<Account> accounts = [
  SELECT Id, Name FROM Account
  WHERE Industry NOT IN ('Banking', 'Technology')
];

// Apex collection bind: the form you will write most often
Set<Id> excludedIds = new Set<Id>{ '001000000000001AAA', '001000000000002AAA' };
List<Contact> contacts = [
  SELECT Id FROM Contact WHERE AccountId NOT IN :excludedIds
];

The collection type has to match the field: Set<Id> or List<Id> for ID and lookup fields, Set<String> or List<String> for text and picklists.

In the test org, one account was in Technology and none in Banking. Industry NOT IN ('Banking', 'Technology') returned 7 of 8 accounts. That includes the 4 accounts with no Industry at all.

NOT IN with a subquery (anti-join)

To exclude records based on another object, put a subquery inside NOT IN:

// Accounts that have no contacts
List<Account> lonely = [
  SELECT Id, Name FROM Account
  WHERE Id NOT IN (SELECT AccountId FROM Contact)
];

Salesforce calls this an anti-join, and it has a few rules:

• The outer field must be an ID or a reference (lookup or master-detail) field.
• The subquery selects exactly one field, and it must be an ID or a reference field.
• Nulls are ignored on both sides. Contacts with no AccountId do not break the query the way a null does in SQL's NOT IN, and outer rows with a null value are returned.

That last rule is easy to test. The org had 11 contacts, 2 with no account. SELECT COUNT() FROM Contact WHERE AccountId NOT IN (SELECT Id FROM Account WHERE Name LIKE 'zzqq%') returned all 11, including the 2 orphans.

!= and <>: exclude one value

List<Account> accounts = [
  SELECT Id, Name FROM Account WHERE Industry != 'Banking'
];

<> is accepted too and returns the same rows. I use != because it matches Apex.

No account in the org was in Banking, and Industry != 'Banking' returned all 8, including the 4 with a blank Industry. SQL would have returned only the 4 that had a value.

NOT LIKE: exclude a pattern

SOQL has no NOT LIKE operator. Negate the whole LIKE condition and wrap it in parentheses:

// Accounts whose website does not contain "test"
List<Account> accounts = [
  SELECT Id, Name FROM Account
  WHERE (NOT Website LIKE '%test%')
];

WHERE Website NOT LIKE '%test%' is a parse error. Like the other two exclusions, the negated LIKE keeps null rows: 6 of the 8 test accounts had no website, and all 8 came back. Wildcards and escaping are covered in the SOQL LIKE and NOT LIKE guide.

Nulls: SOQL works the opposite way to SQL

In SQL, NULL never matches anything, so Industry <> 'Banking' silently drops blank rows. SOQL treats a blank field as a value that simply is not 'Banking', so the row stays in.

Filter	Returns rows where the field is null?
Industry != 'Banking'	Yes
Industry NOT IN ('Banking', 'Technology')	Yes
(NOT Website LIKE '%test%')	Yes
Id NOT IN (SELECT AccountId FROM Contact)	Yes (outer nulls kept, inner nulls ignored)
Industry = 'Banking' or Industry IN (...)	No

When you want only rows that have a value, say so:

List<Account> accounts = [
  SELECT Id, Name FROM Account
  WHERE Industry != 'Banking' AND Industry != null
];

Use = null and != null. SOQL rejects IS NULL and IS NOT NULL with a parse error, which is the second-most common mistake I see from SQL developers after expecting nulls to drop out.

Performance on large objects

The query optimizer looks for a selective filter it can drive from an index. Negative filters (!=, NOT IN, negated LIKE) are not selective, so on their own they push the optimizer toward a full scan. On an object with millions of rows that can mean a slow query or a non-selective query error in a trigger.

Two fixes work well:

1. Add a positive, indexed filter alongside the exclusion, such as a CreatedDate range, a status field, or a lookup to a specific parent. The optimizer narrows the set with the index first.
2. Split the work. Query the IDs you want to exclude, then filter the main result in Apex with Set.contains(). It is more code, but you can see the row count at each step.

Set<Id> staleAccountIds = new Set<Id>();
for (Contact c : [SELECT AccountId FROM Contact WHERE LastActivityDate < LAST_N_DAYS:90]) {
  staleAccountIds.add(c.AccountId);
}
List<Account> active = new List<Account>();
for (Account a : [SELECT Id FROM Account WHERE CreatedDate = LAST_N_DAYS:365]) {
  if (!staleAccountIds.contains(a.Id)) active.add(a);
}

Quick reference

You want to...	Write
Exclude one value	field != 'X'
Exclude several values	field NOT IN ('X', 'Y')
Exclude an Apex collection	field NOT IN :mySet
Exclude records found by another query	Id NOT IN (SELECT ParentId__c FROM Child__c)
Exclude a pattern	(NOT field LIKE '%X%')
Drop blank values too	add AND field != null
Find only blank values	field = null

Common mistakes

• Expecting SQL null behavior. Exclusions in SOQL keep blank rows. Add AND field != null when that matters.
• Writing IS NOT NULL. It does not parse. Use != null.
• Writing field NOT LIKE. It does not parse. Use (NOT field LIKE ...).
• Reaching for SELECT DISTINCT. SOQL has no DISTINCT. Use GROUP BY field to get unique values.
• Running an unfiltered NOT IN on a large object inside a trigger. Pair it with a selective filter.