Narcis Miclaus

8 min read

SQL Server, from the ground up Lesson 7 / 40

WHERE, NULL, and why your filters lie

Three-valued logic, IS NULL vs = NULL, ISNULL vs COALESCE, and the silent bug every developer writes once. With Runehold customer-support examples.

Here is the one SQL fact that every developer learns through pain and is then annoyed they had to learn through pain: SQL is not a two-valued logic system. It has three possible results from a comparison: TRUE, FALSE, and UNKNOWN. The third one is NULL, and if you don’t understand how it works, your queries will silently drop rows you wanted or silently keep rows you didn’t. No error. Just wrong answers.

Today we fix that for good.

What NULL actually means

In SQL, NULL is not zero. It’s not an empty string. It’s not “false.” It means “value unknown, or value absent, the database has no information here.”

Every data type in SQL Server can be NULL unless you declared the column NOT NULL. A row’s Email column might have a real email, or it might be NULL if the customer never gave you one. An OrderDate might be NULL because the shopping cart hasn’t been checked out yet. A ShippedAt is NULL until the warehouse scans the parcel.

This is useful. The trouble starts when you compare.

Comparisons with NULL return NULL, not FALSE

Try this in your head first, then run it:

SELECT
    1 = 1                           AS one_eq_one,          -- TRUE
    1 = 2                           AS one_eq_two,          -- FALSE
    1 = NULL                        AS one_eq_null,         -- NULL  (not FALSE!)
    NULL = NULL                     AS null_eq_null,        -- NULL  (not TRUE!)
    NULL <> NULL                    AS null_neq_null,       -- NULL
    1 <> NULL                       AS one_neq_null,        -- NULL
    NULL + 1                        AS null_plus_one;       -- NULL

The rule: any expression involving NULL evaluates to NULL. Not “false.” The database’s way of saying “I can’t tell you; one side is unknown.”

WHERE clauses only keep rows where the filter is TRUE. Rows where the filter is FALSE are excluded. Rows where the filter is NULL are also excluded. So “not FALSE” and “not TRUE” are actually the same from a filter perspective, but conceptually it matters when you reason about your query.

IS NULL, not = NULL

Because = NULL is NULL, not TRUE or FALSE, you can never use = to check for NULL. You have to use IS NULL and IS NOT NULL:

-- WRONG: returns zero rows even if there are customers with NULL emails
SELECT CustomerId FROM Sales.Customer WHERE Email = NULL;

-- RIGHT
SELECT CustomerId FROM Sales.Customer WHERE Email IS NULL;
SELECT CustomerId FROM Sales.Customer WHERE Email IS NOT NULL;

If I had €1 for every bug I’ve seen where someone wrote WHERE deleted_at = NULL and wondered why their soft-delete filter wasn’t working, I’d have enough to cover a Runehold Loremaster subscription.

The “not equal” trap, with data

Here’s a trap that bites everyone, including me last week.

Runehold’s customer support team wants to send a survey to every customer outside the Netherlands. The obvious query:

SELECT Email
FROM Sales.Customer
WHERE Country <> 'NL';

Looks right. Is wrong.

Because of three-valued logic, any row where Country is NULL does not match <> 'NL' — because NULL <> 'NL' is NULL, not TRUE. Customers with a missing country get silently excluded from the survey. You’ve missed them. Marketing sends out the campaign, and three weeks later somebody notices the “Other countries” segment had way fewer people than expected.

The safe form:

SELECT Email
FROM Sales.Customer
WHERE Country <> 'NL' OR Country IS NULL;

-- or, often clearer:
SELECT Email
FROM Sales.Customer
WHERE ISNULL(Country, '') <> 'NL';

ISNULL(col, default) replaces NULL with the default before the comparison. Now a NULL country is treated as '' and '' <> 'NL' is TRUE. The survey goes to everyone except NL customers.

Whenever you write <>, stop and ask: “can this column be NULL, and do I want those rows or not?” It takes two seconds. It saves campaigns.

ISNULL vs COALESCE

Two functions that do almost the same thing. Both return the first non-NULL value from their arguments. Differences that matter:

-- ISNULL: exactly two arguments, T-SQL-specific
SELECT ISNULL(Phone, 'no phone') FROM Sales.Customer;

-- COALESCE: two or more arguments, standard SQL
SELECT COALESCE(Phone, MobilePhone, 'no phone') FROM Sales.Customer;

COALESCE is the right default. It’s the SQL-standard spelling, works across databases if you ever port your queries, and takes any number of arguments. ISNULL is shorter for the two-argument case and is slightly faster in some micro-benchmarks, but nobody notices that outside of the query optimizer’s dreams.

Subtle gotcha that’s caused me pain:

-- ISNULL returns the data type of the first argument
SELECT ISNULL(NULL, 'fallback value that is too long');
-- This returns 'fallback v' — truncated to 1 character because
-- ISNULL thought the type was implied by the NULL literal.
-- In modern SQL Server this warns; older versions silently truncated.

-- COALESCE uses rules from the SQL standard: it picks the type with
-- the highest precedence from all arguments. Safer.
SELECT COALESCE(NULL, 'fallback value that is too long');
-- Returns the full string.

Type precedence is a rabbit hole. When in doubt, cast explicitly.

NULL and arithmetic

Arithmetic with NULL propagates NULL:

SELECT 5 + NULL;               -- NULL
SELECT 10 / NULL;              -- NULL
SELECT 'foo' + NULL;           -- NULL  (string concat)

So if you have a Discount column that can be NULL, don’t write:

SELECT Total - Discount AS NetTotal FROM Sales.Orders;

…because for any row where Discount is NULL, NetTotal will also be NULL. Probably not what you wanted. Use COALESCE:

SELECT Total - COALESCE(Discount, 0) AS NetTotal FROM Sales.Orders;

This pattern — “if the value is NULL, treat it as zero for the math” — comes up constantly.

NULL and aggregates

Aggregate functions (SUM, AVG, COUNT, MAX, MIN) generally ignore NULLs. They behave like “give me the aggregate of the non-NULL values.”

-- Table has 5 rows: Total values 10, 20, NULL, 30, 40
SELECT SUM(Total) AS total_sum,        -- 100 (10+20+30+40)
       AVG(Total) AS total_avg,        -- 25  (100 / 4, not 100 / 5)
       COUNT(Total) AS count_non_null,  -- 4
       COUNT(*)     AS count_rows       -- 5
FROM Sales.Orders;

COUNT(*) counts rows regardless of NULL. COUNT(column) counts only rows where column is not NULL. This is a common interview trap.

The WHERE clause and index-friendliness

WHERE drives which index SQL Server picks (if any). Writing a WHERE that’s SARGable — short for “Search ARGument-able”, meaning SQL Server can seek an index for it — is half the battle in writing fast queries.

SARGable (index can seek):

WHERE CountryCode = 'IT'
WHERE OrderDate >= '2026-01-01' AND OrderDate < '2026-04-01'
WHERE Total BETWEEN 50 AND 500
WHERE CustomerId IN (1, 2, 3, 4)
WHERE Email LIKE 'anne%'     -- prefix search: OK

Not SARGable (index has to scan):

WHERE YEAR(OrderDate) = 2026           -- function on the column
WHERE UPPER(Name) = 'ANNE DE VRIES'    -- function on the column
WHERE Email LIKE '%@gmail.com'          -- leading wildcard
WHERE CAST(OrderId AS NVARCHAR) = '42'  -- explicit conversion
WHERE Total + Discount > 100            -- arithmetic on the column
WHERE OrderDate = @date  -- (if @date is a different type, implicit conversion)

The rule: don’t put the indexed column inside a function, cast, or expression. Move the transformation to the other side of the comparison when possible:

-- Bad: function on column
WHERE CAST(OrderDate AS DATE) = '2026-03-15'

-- Good: rewrite as a range
WHERE OrderDate >= '2026-03-15' AND OrderDate < '2026-03-16'

-- Bad: arithmetic on column
WHERE Total + 10 > @threshold

-- Good: move arithmetic to the other side
WHERE Total > @threshold - 10

This one habit is worth more than any indexing advice. An otherwise perfect index is useless against a non-SARGable WHERE.

IN, NOT IN, and the NULL trap strikes again

IN checks membership in a list:

SELECT Name FROM Sales.Customer WHERE Country IN ('IT', 'ES', 'FR');

Straightforward. NOT IN is where it gets weird:

-- Looks like "customers not in NL, DE, or IT"
SELECT Name FROM Sales.Customer
WHERE Country NOT IN ('NL', 'DE', 'IT');

This is fine unless the list ('NL', 'DE', 'IT') contains a NULL, or comes from a subquery that can return NULL. Then the whole NOT IN evaluates to NULL for every row, and you get zero results. The cause is three-valued logic applied to the negation.

Safer modern pattern when a subquery is involved:

-- Say we're looking for customers who haven't placed an order
-- NOT IN form, dangerous if CustomerId can ever be NULL in Orders
SELECT Name FROM Sales.Customer
WHERE CustomerId NOT IN (SELECT CustomerId FROM Sales.Orders);

-- NOT EXISTS form, safe against NULL
SELECT Name FROM Sales.Customer c
WHERE NOT EXISTS (
    SELECT 1 FROM Sales.Orders o WHERE o.CustomerId = c.CustomerId
);

Use NOT EXISTS instead of NOT IN with subqueries. It’s equivalent logic, safe against NULL, and usually produces an identical or better execution plan.

Run this on your own machine

USE Runehold;
GO

-- Let's make one Customer row have NULL Country to demonstrate
INSERT INTO Sales.Customer (Name, Country)
VALUES ('Mysterious Stranger', NULL);

-- Query 1: the "<>" trap
-- How many customers are NOT in Netherlands?
SELECT COUNT(*) AS wrong_count
FROM Sales.Customer
WHERE Country <> 'NL';
-- Returns 3 — missing the mysterious stranger.

SELECT COUNT(*) AS right_count
FROM Sales.Customer
WHERE Country <> 'NL' OR Country IS NULL;
-- Returns 4.

-- Query 2: safe fallback
SELECT Name,
       ISNULL(Country, '??') AS CountryOrMystery
FROM Sales.Customer
ORDER BY Country;

-- Query 3: NULL propagation in arithmetic
-- Add a nullable column Discount to Orders for the demo
ALTER TABLE Sales.Orders ADD Discount DECIMAL(19, 4) NULL;

-- Some orders have discount, some don't
UPDATE Sales.Orders SET Discount = 5.00 WHERE OrderId IN (1, 3);

SELECT OrderId,
       Total,
       Discount,
       Total - Discount               AS NetWrong,   -- NULL for rows with no discount
       Total - COALESCE(Discount, 0)  AS NetRight
FROM Sales.Orders
ORDER BY OrderId;

-- Query 4: aggregate ignores NULL
SELECT
    COUNT(*)         AS rows_total,     -- all rows
    COUNT(Discount)  AS rows_with_disc, -- only non-NULL
    SUM(Discount)    AS total_discount, -- NULL rows skipped
    AVG(Discount)    AS avg_discount    -- average over non-NULL only
FROM Sales.Orders;

-- Clean up
ALTER TABLE Sales.Orders DROP COLUMN Discount;
DELETE FROM Sales.Customer WHERE Country IS NULL;

Run it line-by-line. Read every result. Predict each number before looking at it. When you’re surprised, re-read the relevant section above.

Next lesson: ORDER BY, TOP, and paging with OFFSET ... FETCH. Plus the one mistake every developer makes when trying to “get the top N rows per group” for the first time.

Search