There is a bug in your answer. You say:
This means that the first query returns all rows from MyTable, the second only the rows with NumericColumn equal to NULL. The EXCEPT removes the second set from the first, leaving the rows with NumericColumn equal to NULL in the result set. Apparently, there are five rows with NumericColumn = NULL.
That should read:
This means that the first query returns all rows from MyTable, the second only the rows with NumericColumn not equal to NULL. The EXCEPT removes the second set from the first, leaving the rows with NumericColumn equal to NULL in the result set. Apparently, there are five rows with NumericColumn = NULL.
In order for the final query to return the rows with NULL in them, the second query has to return the ones that are not null, for them to be excepted from the first query, which includes all rows. More to the point, WHERE NOT (1 between -1 and -10) will pass the row, because NOT (FALSE) evaluates to true, while WHERE NOT (NULL between -1 and -10) will cull the row, because NOT (UNKNOWN) evaluates to unknown which is treated as not true. So query 2 will pass not-null rows, which will then be excepted, which will leave the null rows to be returned.
Here's a complete test harness (using a table variable instead of an actual table, which eliminates the red herrring of the isolation level):
declare @MyTable table (NumericColumn int, TextColumn char(5));
insert @MyTable
values
(NULL,'NULL1'),
(NULL,'NULL2'),
(NULL,'NULL3'),
(NULL,'NULL4'),
(NULL,'NULL5'),
(1,'VAL1'),
(2,'VAL1'),
(3,'VAL1'),
(4,'VAL1'),
(5,'VAL1');
--1: all rows
select *
from @MyTable
--2: rows to be excepted
select *
from @MyTable where not (NumericColumn between -1 and -10)
--3: full query
select *
from @MyTable
except
select *
from @MyTable where not (NumericColumn between -1 and -10)
and the results:
Query 1:
NumericColumnTextColumn
NULLNULL1
NULLNULL2
NULLNULL3
NULLNULL4
NULLNULL5
1VAL1
2VAL1
3VAL1
4VAL1
5VAL1
Query 2:
NumericColumnTextColumn
1VAL1
2VAL1
3VAL1
4VAL1
5VAL1
Query 3:
NumericColumnTextColumn
NULLNULL1
NULLNULL2
NULLNULL3
NULLNULL4
NULLNULL5