Filtering with OR vs IN

A common filter in SQL procs goes something like:

WHERE (@a = 0 OR @a = a)

Obviously the idea being to filter on a if a positive parameter was provided to the proc, but to otherwise show all results. There are, of course, different variations of this that account for the possibility of nulls. But taking the specific example given above, would it be identical to write it as follows?

WHERE @a in (0, a)

Answer

Yes, those expressions will produce the same results.

This assumes context:

declare @a int
set @a = 1

select *
from [sometable]
where @a in (0, a)

…where a is an int column in the sometable table. In this case, you’ll get all records from sometable where a=1.

Advertisement

Answer