CODEWITHSUNDEEP
sqlsql-servert-sql
Philip
Philip

Reputation: 2628

Showing NULL values in WHERE clause

I've put together the below example to explain what I'm trying to do.

I'm trying to within the query when @FeatureID is NULL then return all the records in the#Temp table for that ClientID. If a @FeatureID contains a 1 for example then returning the 1 record and the NULL record.

Where have I gone wrong in my where clause?

CREATE TABLE #Temp
(
    ClientID int,
    FeatureID int
)

Insert into #Temp
(
    ClientID,
    FeatureID
)
SELECT
    1,
    1
UNION
SELECT
    1,
    2
UNION
SELECT
    1,
    3
UNION
SELECT
    1,
    NULL


Declare @ClientID int = 1
Declare @FeatureID int = NULL

--should return all 4 records
select * from #Temp 
where ClientID = 1 and 
FeatureID = IIF(@FeatureID IS NULL, FeatureID, @FeatureID)

Set @ClientID = 1
Set @FeatureID = 1

--should return the 1,1 record and the 1,NULL record
select * from #Temp 
where ClientID = 1 and 
FeatureID = IIF(@FeatureID IS NULL, FeatureID, @FeatureID)

drop table #Temp

Upvotes: 1

Views: 394

Answers (3)

haldo
haldo

Reputation: 16711

If you want to combine them then this should work:

select * from #Temp 
where ClientID = 1 and 
(@FeatureID is null
or
(@FeatureID is not null 
    and (FeatureID is null or FeatureID=@FeatureID)))

This will select all the records when @FeatureID = null and return 2 results {(1, null), (1, 1)} when @FeatureID = 1

Upvotes: 1

APH
APH

Reputation: 4154

An alternative formulation that might be a little simpler:

select * from #Temp 
where ClientID = 1 and 
(ISNULL(@FeatureID, FeatureID) = FeatureID or FeatureID is NULL)

If @FeatureID is null, then FeatureID = FeatureID, which is always true. Otherwise, it will check for @FeatureID = FeatureID.

It will always return the rows where FeatureID is null.

Upvotes: 2

kicken
kicken

Reputation: 2167

You're effectively trying to compare NULL = NULL in your where clause which doesn't work. NULL does not compare equal to another NULL.

For your first query what you need to do is only compare the feature id column when @FeatureID is not null. This can be accomplished by testing the variable and using an OR condition.

--should return all 4 records
select * from #Temp 
where ClientID = 1 and 
(@FeatureID IS NULL OR FeatureID = @FeatureID)

In the second query you need to compare the feature ID column to both @FeatureID and NULL to get both rows.

--should return the 1,1 record and the 1,NULL record
select * from #Temp 
where ClientID = 1 and 
(@FeatureID IS NOT NULL AND (FeatureID IS NULL OR FeatureID=@FeatureID))

To handle both cases in a single query, use two conditions joined by OR that branched based on whether the variable is null or not.

select * from #Temp 
where ClientID = 1 and 
    (
        @FeatureID IS NULL 
        OR (@FeatureID IS NOT NULL AND (FeatureID IS NULL OR FeatureID=@FeatureID))
    )

Upvotes: 1

Related Questions