A question came up the other day: what is the best way to write a query that searches a nullable column but treats null = null, without setting ANSI_NULLS OFF? To answer this question, I set up a test table full of plenty of data to test query plans against. here is the SQL I used to generate the test table:

set nocount on go create table test (value int)
go declare @loop int = 0
declare @rand int while (@loop < 10000)
begin set @rand = rand() * 2000000
    if (@rand % 20 = 0) set @rand = null
    insert test values (@rand)
    set @loop += 1
end go create index ix_test_value on test (value)
go

The result is 10,000 rows of integer values between 0 and 2 million, with about 5% nulls on average.

There are two main ways that I know of to write a query that searches for a value in a nullable column and treats null = null. The first way is the uglier way:

select *
from test
where (value is null and @value is null)
or value = @value

I say that this version is ugly because it requires two AND comparisons surrounded by parentheses, and a third OR comparison, which may require yet another pair of surrounding parentheses if there are any other search conditions in the WHERE clause.

The second way I know of is much nicer to write:

select *
from test
where isnull(value, 0) = isnull(@value, 0)

I say that this version is nicer, because there is only one comparison and no messy ORs that need surrounding by futher parentheses. But how will the two queries perform? To answer this, I loaded the following batch into Management Studio:

declare @value int set @value = rand() * 2000000
if (@value % 20) = 0 set @value = null select *
from test
where (value is null and @value is null)
or value = @value select *
from test
where isnull(value, 0) = isnull(@value, 0)

And here is the estimated execution plan:

The “ugly” version of the query is able to use the index to seek the matching rows, but the “nice” version has to perform an index scan. In this estimation, the “ugly” version “costs” 10x less than the “nice” version. Query costs don’t correlate 1:1 with actual execution time, but they are an indicator.

So in summary, when performing a search on a nullable column when you want null = null to be true, use the “ugly” version of the query given above, being careful to include an additional set of surrounding parentheses if there are further conditions in the WHERE clause, like this:

select *
from test
where (
    (value is null and @value is null)
    or value = @value
)
and /* other condition here */

The alternative, while enticing in its simplicity and elegance, just won’t perform as well.