在where子句+ SQL Server中，NULL vs = NULL ?

How to check a value IS NULL [or] = @param (where @param is null)

如何检查一个值为NULL[或]= @param(其中@param为NULL)

Ex:

例:

Select column1 from Table1
where column2 IS NULL => works fine

If I want to replace comparing value (IS NULL) with @param. How can this be done

如果我想用@param替换比较值(为NULL)。怎么做呢

Select column1 from Table1
where column2 = @param => this works fine until @param got some value in it and if is null never finds a record.

How can this achieve?

该如何实现呢?

5 个解决方案

#1

select column1 from Table1
  where (@param is null and column2 is null)
     or (column2 = @param)

#2

There is no "one size fits all" query approach for this, there are subtle performance implications in how you do this. If you would like to go beyond just making the query return the proper answer, no matter how slow it is, look at this article on Dynamic Search Conditions in T-SQLby Erland Sommarskog

没有一种“一刀切”的查询方法，如何做到这一点有微妙的性能影响。如果您不想仅仅让查询返回正确的答案，不管它有多慢，请参阅这篇关于T-SQLby Erland Sommarskog中的动态搜索条件的文章

here is a link to the portion on x = @x OR @x IS NULL

这里是指向x = @x或@x为空部分的链接

#3

I realize this is an old question, but I had the same one, and came up with another (shorter) answer. Note: this may only work for MS SQL Server, which supports ISNULL(expr,replacement).

我意识到这是一个老问题，但我有一个相同的问题，并想出了另一个(更短的)答案。注意:这可能只适用于MS SQL Server，它支持ISNULL(expr,replace)。

SELECT column1 FROM table1
WHERE ISNULL(column2,'') = ISNULL(@param,'')

This also assumes you treat NULL and empty strings the same way.

这也假定您以相同的方式处理空字符串和空字符串。

#4

WHERE ((COLUMN1 = @PARAM) OR (COLUMN1 IS NULL AND @PARAM IS NULL))

#5

Select column1 from Table1
where (column2 IS NULL and @param IS NULL) 
or ( column2 IS NOT NULL AND @param IS NOT NULL AND ( column2 = @param )  )

#1

select column1 from Table1
  where (@param is null and column2 is null)
     or (column2 = @param)

#2