Is There a Way to Treat Null Values to be Equal in a WHERE Clause? 1

[DaveInIowa]

Is there a better way to code the following comparison in a WHERE clause?

WHERE ((@ParameterValue IS NULL AND ColumnValue IS NULL) OR (@ParameterValue = ColumnValue))

Normally when I accept a null parameter value I use it to mean I don't want to filter on that particular value but in this case I want it to mean I only want records where the ColumnValue is also null.

I thought about doing this...

WHERE ISNULL(@ParameterValue, [red]'text'[/red]) = ISNULL(ColumnValue, [red]'text'[/red])

where [red]'text'[/red] is a value that will never be in the table but I don't like using magic values and you never know what values will never be in the table. I considered converting a NEWID() to a VARCHAR(50) and using that as the [red]'text'[/red]. There has to be a more elegant solution.

 

[fredericofonseca]

if you do the second option any index on that column may not be usable on your query.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[gk53]

why no simple
where ISNULL(@ParameterValue, '') = ISNULL(ColumnValue, '')

 

[DaveInIowa]

The problem with ISNULL(@ParameterValue, '') = ISNULL(ColumnValue, '') is that if I pass a NULL @ParameterValue, in addition to returning NULL ColumnValue rows, I'll also be getting rows where the ColumnValue is an empty string.

Conversely, passing an empty string for the @ParameterValue would also return rows where the ColumnValue is either an empty string (which is okay) or NULL (which would not be okay).

 

[bborissov]

IF @ParameterValue IS NULL
   SELECT ....
      FROM ...
    WHERE ColumnValue IS NULL
ELSE 
   SELECT ....
      FROM ...
    WHERE ColumnValue = @ParameterValue

Borislav Borissov
VFP9 SP2, SQL Server

 

[gmmastros]

In my opinion, there is no elegant way to do this.

There is a setting that would elegantly solve your problem. Unfortunately, it has been on the SQL Server deprecation list for the last 10 years.

Example:

Create Table #Temp(Data VarChar(100))

Insert Into #Temp Values('Red')
Insert Into #Temp Values('Blue')
Insert Into #Temp Values('')
Insert Into #Temp Values(null)

Create Index idx On #Temp(Data)

[!]Set ANSI_NULLS OFF[/!]

Declare @Param varchar(100)

Set @Param = null

Select	*
From	#Temp
Where	Data = @Param

ANSI_NULLS is usually on. You can see the deprecation statement here:

https://msdn.microsoft.com/en-us/library/ms188048.aspx

Frederico makes a good point about indexes. Unfortunately, with either version you won't get index seeks. I would prefer the first version because the deprecation statement scares me and I also don't like to use "magic values".

For example. Run the following code with "Include Actual Execution Plan" turned on. (Click Query -> Include Actual Execution Plan).

Create Table #Temp(Data VarChar(100))

Insert Into #Temp Values('Red')
Insert Into #Temp Values('Blue')
Insert Into #Temp Values('')
Insert Into #Temp Values(null)

Set ANSI_NULLS OFF

Create Index idx On #Temp(Data)

Declare @Param varchar(100)

Set @Param = null

Select	*
From	#Temp
Where	Data = @Param

Select	*
From	#Temp
Where	(Data Is NULL And @Param Is NULL)
		Or
		Data = @Param

Select	*
From	#Temp
WHERE	ISNULL(@Param, 'text') = ISNULL(Data, 'text')

After running the code above, you should see a new tab in the results for "Execution Plan". You'll see that only the first query has an index seek. The others will be an index scan. (BTW, Seeks are a lot better than scans).

All this, just to say, there's no really elegant way to do this unless you are willing to use a feature that may (some day) be removed from the database engine.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DaveInIowa]

Thanks to all for your time and suggestions.

George - Thank you for the mini tutorial and examples on ANSI_NULLS and execution plans. It helped to make sense of what Frederico and Borislav were trying to convey.

Borislav - I'll keep your suggestion in the back of my mind if performance is an issue but for now I'd rather maintain 1 query instead of 2 nearly identical queries. It would be nice if SQL Server could optimize this type of construct or offer a hint we could apply.