The NULL Bug That Haunted My First Production Query

I still remember the moment I discovered this one. It was 3 AM, the night before a client demo, and I was staring at a query that was supposed to pull all customers missing phone numbers. The table had 500 customers. My query returned zero. I could see the NULLs sitting right there in the database. Nothing crashed. No error message. Just silent failure.

That's when a senior developer I was working with pointed out what I'd done: WHERE phone = NULL. Two seemingly innocent characters that broke everything. I felt genuinely stupid for about ten seconds, then realized this wasn't about being dumb, it was about how SQL actually thinks about the absence of data. And once that clicked, everything changed about how I write queries.

The Real Problem: Unknown Isn't Nothing

Here's what actually happens when SQL encounters WHERE phone = NULL. The database doesn't evaluate it as "does this phone field equal nothing?" Instead, it asks: "does this unknown value equal this other unknown value?" The answer is always "unknown." And SQL's WHERE clause has a strict rule: only rows where the condition is TRUE pass through.

Unknown doesn't pass through. FALSE doesn't pass through. Only TRUE does.

This is the counterintuitive bit. Even NULL = NULL returns unknown. The logic isn't broken, it's actually consistent. NULL represents uncertainty, not emptiness. You can't compare something uncertain to something else uncertain using equality. That's like asking "does the unknown answer to question A equal the unknown answer to question B?" There's no way to answer that fairly.

The moment I understood this wasn't a SQL bug but a feature of how databases think about missing data, I stopped fighting it.

The Actual Solution (And the Trap)

The fix is straightforward: use IS NULL and IS NOT NULL instead. These are dedicated operators built for exactly this job, checking whether data exists or doesn't, rather than comparing values.

-- What doesn't work
SELECT name FROM customers WHERE phone = NULL;
-- Returns 0 rows (wrong)

-- What actually works
SELECT name FROM customers WHERE phone IS NULL;
-- Returns all customers with missing phone numbers (correct)

-- And the opposite
SELECT name FROM customers WHERE phone IS NOT NULL;
-- Returns all customers with phone numbers on file

But here's where people trip up. They think != is the opposite of =, so they try WHERE phone != NULL. This fails for the exact same reason, it's still a comparison operator being asked a question it can't answer. NULL != NULL is also unknown, so it filters out nothing.

I've caught this in code review more than once. It's a sneaky bug because the query runs fine. You only notice when your data is silently incomplete.

Preventing This Upstream

Here's my take: the real solution starts at the schema level, not the query level.

If a phone number is genuinely optional, fine, allow NULL and handle it deliberately in queries. But if it's not actually optional, enforce that with a NOT NULL constraint when you define the table. I've started treating this the same way I handle undefined in JavaScript or None in Python. Missing data is either intentional and managed, or it's a bug waiting to happen.

The other debugging trick I use: when a query returns fewer rows than expected, I never immediately rewrite the logic. I first run a plain SELECT * on the table with no filters and look for NULL in any column involved in the WHERE clause. Nine times out of ten, that's where the problem is.

I also check the gap between COUNT(*) and COUNT(phone). If they're different, it tells me immediately how much missing data I'm dealing with before it breaks something in production.

What This Actually Changes

This isn't just database theory. It affects how I design schemas, how I write queries, and how I debug. I'm more careful about which columns actually need to be nullable. I'm more intentional about NULL handling in complex queries. And I'm less surprised when data behaves unexpectedly.

The big lesson for me: SQL's three-valued logic (true, false, unknown) isn't a flaw. It's actually the right way to think about data when you're dealing with missing information. Once you're in that mindset, NULL stops feeling like a gotcha and starts feeling like a feature you're just using wrong.

Source: This post was inspired by "Why 'WHERE x = NULL' Never Works in SQL (And What to Use Instead)" by Dev.to. Read the original article