Using ISNULL in where Clause doesn't return records with NULL field

Question

Table:

ID     AppType     AppSubType   Factor
1   SC  CD      1.0000000000
2   SC  CD      2.0000000000
3   SC  NULL    3.0000000000
4   SC  NULL    4.0000000000

Query:

declare @ast varchar(10)

set @ast = null

select *
from tbl
where AppType = 'SC' and AppSubType = ISNULL(@ast, AppSubType)

Result:

ID  AppType AppSubType  Factor
1   SC  CD  1.0000000000
2   SC  CD  2.0000000000

Question:

Shouldn't this query return all 4 records and not just the first 2?

Answer 1

Abviously @ast is null and Isnull would exchange null with other value, so you shouldn't expect @ast to be not null. If your AppSubType is null , so the result become null but AppSubType=null doesn't mean because AppSubType is null is true. Because null is not a value so it cant work with equal. for your expected result this code will work.

declare @ast varchar(10)

set @ast = null

select *
from tbl
where AppType = 'SC' and (AppSubType = ISNULL(@ast, AppSubType) Or AppSubType is null)