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...

问题:

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

回答1:

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.


回答2:

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))
    )

回答3:

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

  • 发表于 2019-02-28 08:23
  • 阅读 ( 179 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除