IS NULL vs = NULL in where clause + SQL Server

Question

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?

score 42 · Accepted Answer · answered Mar 22 '10 at 05:15

42

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

answered Mar 22 '10 at 05:15

Laurence Gonsalves

137,896
35
246
299

score 6 · Answer 2 · answered Oct 16 '13 at 21:00

6

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.

answered Oct 16 '13 at 21:00

Dan

1,313
1
14
21

1

If you changed your SQL to use COALESCE instead of ISNULL then it would work for more than just SQL Server since the former is ISO/ANSI defined unlike the latter. – Scott Gartner Jun 27 '14 at 22:38

score 2 · Answer 3 · answered Mar 22 '10 at 18:02

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

Diego Mendes · Answer 4 · 2011-07-25T18:19:39.317

1

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

edited Jul 25 '11 at 18:19

answered Jun 23 '11 at 04:09

Diego Mendes

10,631
2
32
36

1

`COLUMN = @PARAM` will never be true if `@PARAM` is null, so the second condition (`@PARAM IS NOT NULL`) is redundant. – Damien_The_Unbeliever Jun 23 '11 at 04:43
@Damien_The_Unbeliever Jun You're Right, I removed the redundant code. Thanks – Diego Mendes Jul 25 '11 at 18:22

score 0 · Answer 5 · edited Nov 16 '11 at 17:52

0

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

edited Nov 16 '11 at 17:52

Michael Fredrickson

36,839
5
92
109

answered Nov 16 '11 at 17:06

Mike A

51
2

1

The `column2 IS NOT NULL` check is unnecessary, since this would be covered by `column2 = @param`... which makes this answer a less concise version of the accepted answer from last year. – Michael Fredrickson Nov 16 '11 at 18:00
the answer from last year had this: (@param is null and column2 is null) or (column2 = @param) If the @param is not null, the first condition would be false, and the 2nd would be evaluated only: (column2 = @param) I have found that if you are checking things like (column2 = @param) and it could be null, the extra check is safer to have – Mike A Jan 25 '12 at 16:13

IS NULL vs = NULL in where clause + SQL Server

5 Answers5

Linked

Related