The Inequality Filter That Drops Your NULL Rows
You want a report of every product except the ones you discontinued in the big January cleanup. You write WHERE discontinued_on <> '2026-01-31', run it, and the active products are gone. The ones you sell every day, the ones with no discontinue date at all, none of them are in the result. Only a handful of products that were discontinued on other dates come back.
This is Part 3 of The UNKNOWN Problem, a series on NULL traps in T-SQL. Part 2 was a NOT IN that returned nothing. This one is smaller and more common: an inequality filter that drops the rows you most wanted to keep.
Here is the setup, using the product table from Part 1. An active product has discontinued_on set to NULL.
|
1 2 3 4 |
INSERT INTO dbo.product (product_id, sku, product_name, category_id, list_price, discontinued_on) VALUES (100, 'BOLT-08', 'M8 Hex Bolt', 1, 0.12, NULL) , (101, 'NUT-08', 'M8 Hex Nut', 1, 0.05, '2026-01-31') , (102, 'WSHR-08', 'M8 Washer', 1, 0.02, '2025-06-15'); |
Now the query. Give me every product that was not discontinued on the cleanup date. Product 100 is active and product 102 was discontinued on a different date, so both should come back.
|
1 2 3 4 |
SELECT product_id, product_name, discontinued_on FROM dbo.product WHERE discontinued_on <> '2026-01-31'; /* 102 M8 Washer 2025-06-15 */ |
Only product 102 comes back. Product 100, the active one, is missing.
The cause is three-valued logic, the same root as the rest of this series.[1] discontinued_on <> '2026-01-31' is an ordinary comparison, and for product 100 the left side is NULL. A comparison against NULL with =, <>, <, or > is UNKNOWN, not TRUE and not FALSE. WHERE keeps a row only when the predicate is TRUE, so the active product is dropped. The row was not rejected for failing the test. The comparison produced UNKNOWN, and WHERE drops anything that is not TRUE.
This one catches people because the intuition is that <> means "everything that is not equal". A NULL is not equal to '2026-01-31' in plain English, so it feels like it belongs in a <> result. SQL Server does not decide it that way. When discontinued_on is NULL there is no value to compare, so the comparison is UNKNOWN and the row is excluded. The same thing happens with <, >, <=, >=, and =.[2] Any of them against a column whose value is NULL yields UNKNOWN, so a range filter like WHERE list_price > 10 also omits the products whose price is not set, with no error.
The fix is to say what you mean about the NULL rows. If "not discontinued on the cleanup date" is meant to include the active products, add the NULL case.
|
1 2 3 4 5 6 |
SELECT product_id, product_name, discontinued_on FROM dbo.product WHERE discontinued_on <> '2026-01-31' OR discontinued_on IS NULL; /* 100 M8 Hex Bolt NULL 102 M8 Washer 2025-06-15 */ |
Now both come back. The OR discontinued_on IS NULL is a separate predicate that returns TRUE or FALSE, never UNKNOWN, so the active rows have a way to pass.
The point is not that you always want the NULL rows. Whether an unset value belongs on the keep side or the drop side depends on the report, and the two intentions need different queries. What you cannot do is leave it to a bare <> and assume the NULLs go where you meant, because a bare <> excludes them, so if you want them you have to ask. If you want the NULLs out, AND discontinued_on IS NOT NULL is not needed, since the inequality already excludes them, but writing it makes the intent readable to the next person.
One related point is that rewriting the comparison with NOT does not help. WHERE NOT (discontinued_on = '2026-01-31') still drops the active product, because NULL = '2026-01-31' is UNKNOWN and NOT UNKNOWN is still UNKNOWN. Negation does not turn UNKNOWN into TRUE. IS NULL and IS NOT NULL are the direct tests for nullness, and they always return TRUE or FALSE. SQL Server 2022 and later also add IS [NOT] DISTINCT FROM for NULL-safe comparison.[3]
One more note on the literal: '2026-01-31' is a string that SQL Server converts to date to match the column, and the result depends on session date-format settings. The unseparated ISO form '20260131' is interpreted the same way under any language setting, so it is the safer literal to write.
Next up is Part 4, a CHECK constraint that accepts the row it should reject, where a CHECK blocks a row only when its predicate is FALSE, and UNKNOWN is not FALSE.
If an inequality filter has ever shrunk a report on you without warning, I would like to hear the story. You can find me on Bluesky and LinkedIn.
References
- NULL Complexities, Part 1 - SQLPerformance, Itzik Ben-Gan. Explains three-valued logic and how comparisons against NULL evaluate to UNKNOWN, so rows drop from a filter. ↩
- Comparison Operators (Transact-SQL) - Microsoft Learn. Confirms that a comparison with a NULL operand yields UNKNOWN. ↩
- IS [NOT] DISTINCT FROM (Transact-SQL) - Microsoft Learn. The NULL-safe comparison predicate added in SQL Server 2022. ↩