IS NULL vs = NULL in where clause + SQL Server

问题:

How to check a value IS NULL [or] = @param (where @param is 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

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?

回答1:

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

回答2:

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

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

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

回答3:

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

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

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