SQL Server treats NULL values in some un-intuitive ways.
Consider this in my 'Where' clause :
code NOT IN
(32,37,33,39,40,41,47,48,83,94,93,95,501)
I would think this automatically means, anything that is
not one of these numbers, including any NULL. However, that is not how it works. It ignored NULL, removing valuable data from the returned data, so I
had to add an explicit inclusion of that potential table value :
code NOT IN
(32,37,33,39,40,41,47,48,83,94,93,95,501) OR code IS NULL
Microsoft has said, all future versions of SQL Server will use ANSI NULLS ON with no option to turn it on or off. So what? You might ask. Well, this means that " When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name"
The best guidance I found in the MSDN article was " For
a script to work as intended, regardless of the ANSI_NULLS database option or
the setting of SET ANSI_NULLS, use IS NULL and IS NOT NULL in comparisons that
might contain null values."
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql
My takeaway is just to be sure to specify in every query what SQL Server should do when encountering a NULL.
