SOQL Null Values: Test What Your Filters Drop

SOQL null values are where a correct-looking query quietly returns the wrong records. You write WHERE Industry != 'Banking', or WHERE Services__c EXCLUDES ('Onboarding'), and you get a list back that looks right. What you don’t see is whether the records with nothing in that field made the list or fell off it.

My position is simple: don’t memorize the rule, and don’t trust a blog post (this one included) to tell you the rule. Count it. Four COUNT() queries tell you exactly how your org treats blank values for any filter, and they take less time than reading a forum thread that disagrees with the last one you read.

This tutorial walks through that test, the handful of null behaviors Salesforce does document, and how to write filters whose meaning doesn’t depend on anyone’s memory.

Why blank and null are the same thing in Salesforce

Start with the part that trips up people coming from SQL. In a regular database, an empty string '' and NULL are two different values. In Salesforce, a text field left empty is stored as null. There is no separate “empty string” state to filter on.

That’s good news, because it means you only have one kind of “blank” to reason about. When someone searches for how SOQL handles blank values, the real question is how SOQL handles null. The syntax for that is documented and short: you compare against the null keyword directly.

SELECT Id, Name FROM Account WHERE Industry = null
SELECT Id, Name FROM Account WHERE Industry != null

The first returns accounts with no industry. The second returns accounts that have one. Salesforce’s own reference covers this under using null in WHERE clauses, and it’s the one piece of null handling nobody argues about.

The arguments start the moment you filter on a value instead of on null.

The question the docs don’t answer

Here is the query that causes the trouble:

SELECT Id FROM Account WHERE Industry != 'Banking'

Does that include accounts where Industry is empty? In standard SQL the answer is no, because any comparison with NULL is unknown and the row drops out. SOQL is not SQL, and the official comparison operators reference defines != as “doesn’t equal the specified value” without saying what happens when there’s no value at all.

Third-party references fill the gap, and they don’t agree with each other. I found one widely indexed guide stating that != and NOT IN silently exclude nulls, and another stating that a negated LIKE returns null records. Both can’t be describing the same intuition, and you shouldn’t be the one who finds out which is right by mailing a campaign to the wrong 3,000 contacts.

Multi-select picklists are worse. The multi-select picklist reference explains that INCLUDES and EXCLUDES take a list where a semicolon means AND and a comma means OR:

WHERE Services__c INCLUDES ('Onboarding;Training', 'Support')

That matches records with both Onboarding and Training selected, or with Support selected. What the page doesn’t say is whether a record with nothing selected counts as “excluding” Onboarding. Logically it does. Whether the query engine agrees is exactly the thing people keep searching for.

So test it.

The four-query count test

The test works because counts have to add up. Pick the object, the field, and the value you care about, then run four queries:

-- 1. Everything
SELECT COUNT() FROM Account

-- 2. The blanks
SELECT COUNT() FROM Account WHERE Industry = null

-- 3. The value
SELECT COUNT() FROM Account WHERE Industry = 'Banking'

-- 4. The negation you actually want to use
SELECT COUNT() FROM Account WHERE Industry != 'Banking'

Now do the arithmetic.

• If query 3 + query 4 = query 1, your negation includes the blanks.
• If query 3 + query 4 = query 1 − query 2, your negation drops the blanks.
• If it’s neither, something else is going on (sharing rules, a field-level security gap for the running user, or records changing under you mid-test) and you’ve just learned that before it mattered.

For a multi-select picklist, swap in the operators you plan to use:

SELECT COUNT() FROM Contact
SELECT COUNT() FROM Contact WHERE Services__c = null
SELECT COUNT() FROM Contact WHERE Services__c INCLUDES ('Onboarding')
SELECT COUNT() FROM Contact WHERE Services__c EXCLUDES ('Onboarding')

Same rule. INCLUDES plus EXCLUDES should cover every record that has a value. Whether they also cover the blanks is answered by whether the total lands on query 1 or on query 1 minus query 2.

Run it as the user whose results matter. COUNT() respects the running user’s visibility, so an admin’s numbers and an integration user’s numbers can legitimately differ. If an automation will run the real query, test as that automation’s user.

Write filters that mean one thing

Once you know how your org behaves, you’d think you could rely on it. Don’t. The next person to read the query — a new admin, a consultant, or an AI — won’t have run your test. Write the null handling into the query so it says what you mean regardless of engine behavior.

If you want the blanks included:

SELECT Id FROM Account
WHERE (Industry != 'Banking' OR Industry = null)

If you want the blanks excluded:

SELECT Id FROM Account
WHERE (Industry != 'Banking' AND Industry != null)

One of those clauses is redundant in your org. That’s the point. A redundant clause costs nothing and it turns an unwritten assumption into a visible decision. The same pattern works for NOT IN, NOT LIKE, and EXCLUDES.

Always parenthesize when you mix AND and OR. Even where the engine would read it the way you intended, the human after you might not.

There’s a performance argument too. Salesforce’s Apex Developer Guide notes that explicitly filtering out null values lets the platform improve query performance. On a large object, AND Field != null is not just clearer, it can be faster.

Three null behaviors Salesforce does document

A few edge cases are written down, and they’re worth knowing because they surprise people in the opposite direction.

Checkboxes: null means false

A Boolean field never really holds null the way a text field does. Per the SOQL reference, filtering a checkbox with = null behaves like = false, and != null behaves like = true. If you meant “records where nobody touched this checkbox,” there’s no such thing to query — unchecked is unchecked.

Parent fields: missing parents count as null

Filter on a parent’s field through a relationship and records with no parent at all come back too. The reference’s example is a Case query on Contact.LastName = null, which returns cases whether or not the contact exists.

That matters when you mean something narrower:

-- Contacts whose account has no industry
-- (without the first clause, contacts with no account sneak in)
SELECT Id FROM Contact
WHERE AccountId != null AND Account.Industry = null

Text comparisons ignore case

Comparisons on strings are case-insensitive except for fields marked unique and case-sensitive. Industry = 'banking' finds ‘Banking’. That’s unrelated to nulls but it’s the other assumption people import from SQL, and it changes what your counts in query 3 mean.

Where this breaks real work

The query itself is rarely the problem. The problem is what someone does with its results.

Bulk updates. If you’re selecting records to update and the filter silently drops blanks, the records most in need of cleanup — the ones with nothing in the field — are exactly the ones your update skips. That’s the first thing to check before a bulk update that skips the Data Loader.

Duplicate and matching logic. Matching rules that compare fields have to decide what two blanks mean. Two contacts with no phone number are not a match, and a matching query that treats them as one merges strangers. Getting duplicate management rules right is mostly a null-handling exercise.

Reports and exports. An “everyone not in Banking” list that quietly omits every account without an industry is a list that’s wrong in a way nobody will notice for a quarter. When the numbers matter, the count test is also how you sanity-check the reports Salesforce won’t build for you.

Hand the counting to your AI

None of this requires you to write SOQL. If your AI can query your org, the whole test is one sentence:

“Before you give me accounts that aren’t in Banking, count the total, the blank Industry records, the Banking records, and your != query. Tell me whether the blanks are included, and write the final query so it’s explicit either way.”

That’s the workflow in SOQL without writing code: you describe the data, the AI writes the query, and you check its reasoning before you act. The count test is the check. It turns “trust me” into four numbers you can add up yourself.

The prerequisite is an AI that’s actually connected to your org — connecting Claude to Salesforce covers the setup. On Sentinel, read access is its own key, separate from write access, so the counting can happen long before anyone decides to change a record. When it’s time to act on the results, write access is a deliberate, separate step, and every change is logged with a snapshot taken before deploys.

The takeaway

Null handling in SOQL is a small topic with an outsized blast radius. Blank text is null. Checkboxes treat null as false. Missing parents count as null. And for !=, NOT IN and EXCLUDES, the honest answer to “are blanks included?” is: count and see, then write the query so the answer is on the page.

Four COUNT() queries. Two minutes. Every filter you act on after that means what you think it means.

Want an AI that runs this check against your own org before it touches a single record? Book a Demo Call and bring the query you’re least sure about.