.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
Updated and tested 11 October 2026
NULL marks the absence of a value. Every comparison you write against it comes back unknown; true and false are both off the table. That is why WHERE region = NULL matches nothing at all on a table where hundreds of customers have no region recorded, and why a filter that gets refactored into that shape can strip every non-EU row out of a regional report while the query still reports success and the job still goes green. SQL Server is doing exactly what the standard tells it to do. It will not complain once.
NULL handling produces that class of bug more than anything else in T-SQL, and the reason it survives so long in a code base is that nothing crashes; the number is simply wrong, and wrong numbers get believed. What follows is the set of functions you use to deal with it, worked against the same four tables as the rest of this series: dbo.customers with a nullable region and credit_limit, plus dbo.orders, dbo.order_items and dbo.employees.
Three-valued logic and IS NULL
SQL does not have true and false. It has true, false and unknown, and NULL is the thing that drags an expression into that third state, because NULL means "no value here" and a comparison against no value cannot produce a verdict. A WHERE clause only keeps the rows where the predicate came out true, and unknown is not true. Simpler version, and the one I would give someone at a whiteboard: if SQL Server cannot tell, the row does not come back.
SELECT COUNT(*) FROM dbo.customers WHERE region = NULL; -- always 0
SELECT COUNT(*) FROM dbo.customers WHERE region <> NULL; -- also always 0
SELECT COUNT(*) FROM dbo.customers WHERE region IS NULL; -- the real answerBoth of the first two return zero on any modern instance, and they return zero even on the rows where region really is NULL, which is the part that makes the first two look like they might be doing something. That is SET ANSI_NULLS ON behaviour, and it stopped being optional a while ago. Microsoft's wording: "SET ANSI_NULLS OFF and the ANSI_NULLS OFF database option are deprecated. Starting with SQL Server 2017 (14.x), ANSI_NULLS is always set to ON." If you turn up a page saying the default is OFF, that is a legacy line (the same page says the ODBC driver and OLE DB provider set it ON when they connect) and it does not describe what your server does today.
Small aside while we are here, because the vocabulary trips people up in code review. The third truth value has a name of its own, unknown, and it is not the same thing as NULL even though everyone says both words as if they were interchangeable; NULL is a marker sitting in a column and unknown is what a comparison against that marker evaluates to. The standards documents are careful about the distinction and nobody in a stand-up ever is. Back to the query.
The same logic bites through NOT IN, and this one is nastier because it looks like it is working:
SELECT c.customer_id, c.company_name
FROM dbo.customers AS c
WHERE c.customer_id NOT IN (SELECT o.customer_id FROM dbo.orders AS o);One NULL customer_id anywhere in dbo.orders and that query returns nothing at all, forever, because the predicate expands into a chain of <> comparisons and one of them is against NULL, so the whole conjunction lands on unknown for every row you test. Microsoft documents it on the IN reference page. Write NOT EXISTS instead, which handles NULLs the way you assumed NOT IN did, and since how the WHERE clause evaluates predicates is where half the NULL surprises in any code base end up living, that is the next thing to read after this section.
ISNULL versus COALESCE
They look interchangeable, they are not, and every difference between them is written down on the COALESCE reference page for anyone who reads that far down. MSSQL COALESCE is the portable one of the pair, which is usually where the argument starts and almost never where it should end.
The five documented differences
- Standards.
ISNULLis T-SQL only.COALESCEis ANSI SQL and works in Postgres, MySQL, Oracle and everything else. - Argument count.
ISNULLtakes exactly two.COALESCEtakes as many as you like and returns the first non-NULL one. - Return type.
ISNULLreturns the data type of the first argument.COALESCEreturns the highest-precedence type among all arguments, which is a different rule with different consequences. - Evaluation count.
ISNULLevaluates its input once.COALESCEinputs "can be evaluated multiple times" because the engine rewrites the whole thing as aCASEexpression. - Nullability. The result of
ISNULLis treated as NOT NULL. The result ofCOALESCEmay still be nullable.
That last one has a consequence you meet the first time you build a computed column:
-- works
CREATE TABLE dbo.t1 (col1 int NULL, col2 AS ISNULL(col1, 0) PRIMARY KEY);
-- fails: the computed column is nullable, so it cannot be a primary key
CREATE TABLE dbo.t2 (col1 int NULL, col2 AS COALESCE(col1, 0) PRIMARY KEY);Same intent, same arithmetic, and one of them builds while the other does not. It is also the trick for forcing NOT NULL metadata onto a column coming out of a recursive common table expression, where every output column is nullable regardless of what the base table declares, so if you have ever tried to put a primary key on a SELECT ... INTO from a recursive CTE and been refused, this is why.
ISNULL silently truncates the replacement value
Here is the one I would put on a poster. Microsoft's wording is blunt: "replacement_value can be truncated if replacement_value is longer than check_expression." Because ISNULL returns the type of the first argument, the replacement gets cut down to fit it, and no warning is raised anywhere in the process.
CREATE TABLE dbo.sku_notes (sku varchar(5) NULL);
INSERT INTO dbo.sku_notes (sku) VALUES (NULL);
SELECT ISNULL(sku, 'unassigned') AS with_isnull,
COALESCE(sku, 'unassigned') AS with_coalesce
FROM dbo.sku_notes;with_isnull with_coalesce
----------- -------------
unass unassigned'unassigned' is ten characters and sku is varchar(5), so ISNULL hands back unass, while COALESCE applies type precedence across both arguments and returns the string intact. Checking that a replacement fits is its own small trap, since LEN lies to you about trailing spaces and DATALENGTH does not, and on a padded char column those two disagree by exactly the amount you care about. I watched this land in a customer-facing export where a status column was char(3). Every missing value arrived as unk while the code plainly said unknown. Nobody queried it for months because it looked deliberate.
When COALESCE evaluates its arguments twice
The rewrite-as-CASE behaviour stays invisible right up until one of the arguments has a cost or a side effect. This is the shape that bites:
SELECT c.customer_id,
COALESCE((SELECT MAX(o.total_amount)
FROM dbo.orders AS o
WHERE o.customer_id = c.customer_id), 0) AS biggest_order
FROM dbo.customers AS c;That subquery can run twice per customer row, once to test the value for NULL and once to return it, so on ten thousand customers you get twenty thousand executions of an aggregate you meant to run ten thousand times. The same thing happens with NEWID(), RAND() and CRYPT_GEN_RANDOM, except there it is worse, because the two evaluations return different values and you can end up with a non-NULL test followed by a completely different non-NULL result.
A few ways out of it, and I reach for the last one:
- Swap in
ISNULL, which evaluates once by definition. Fine for two arguments, useless past that. - Compute the expensive expression into a variable or a temp column first, then coalesce the cheap thing.
- Lift the aggregate into a CTE that computes it once for the whole set, then coalesce its column in the outer query.
- Move the subquery into an
OUTER APPLYand coalesce the resulting column, which evaluates it once and reads better than any of the others. That rewrite gets a step-by-step treatment in CROSS APPLY and OUTER APPLY.
None of this matters for a plain literal default, so COALESCE(region, 'unknown') is fine and always will be. Literals, use whichever you like. Anything with a function call or a subquery inside it, keep it out of COALESCE. I have not benchmarked what a twelve-argument COALESCE does to the size of a plan, and I am not going to invent a number for it. My own lists stop at four or five arguments; past that I would move the fallbacks into a lookup table.
NULLIF and the divide by zero guard
NULLIF(a, b) returns NULL when the two arguments are equal and returns a otherwise, which is exactly what the longhand CASE does:
NULLIF(a, b) = CASE WHEN a = b THEN NULL ELSE a ENDWritten like that it sounds like a solution looking for a problem, and it stays sounding like one until the morning you meet Msg 8134, Level 16, State 1: Divide by zero error encountered. in a report that had run without complaint for a year. That is the real use for it:
SELECT o.order_id,
o.total_amount,
SUM(i.quantity) AS units,
o.total_amount / NULLIF(SUM(i.quantity), 0) AS price_per_unit
FROM dbo.orders AS o
JOIN dbo.order_items AS i ON i.order_id = o.order_id
GROUP BY o.order_id, o.total_amount;Zero units turns the divisor into NULL. The division returns NULL and the statement completes. Wrap the whole expression in ISNULL(..., 0) if the report wants a zero in that cell, though I would argue a blank is the more honest output for "we cannot compute this". Backfilling those NULLs permanently in the table is a separate decision, and UPDATE statements in SQL Server covers doing it without a non-deterministic FROM clause, which is the way that job usually goes wrong.
NULLIF returns the data type of its first expression.
And keep nondeterministic functions out of it, for the same reason you keep them out of COALESCE: the arguments can be evaluated more than once, because it is the same CASE machinery underneath.
