This issue came up when I got different records counts for what I thought were identical queries one using a
where constraint and the other a
left join. The table in the
not in constraint had one null value (bad data) which caused that query to return a count of 0 records. I sort of understand why but I could use some help fully grasping the concept.
To state it simply, why does query A return a result but B doesn't?
A: select 'true' where 3 in (1, 2, 3, null) B: select 'true' where 3 not in (1, 2, null)
This was on SQL Server 2005. I also found that calling
set ansi_nulls off causes B to return a result.