raulggonzalez (6/12/2013)
Thanks for the nice question with plenty of knowledge involved, but I've got it just because none of the above made sense for me, and the explanation is not 100% correct as even if we have 5 Null values in the table, the operator EXCEPT will act as explained in BOL:EXCEPT returns any distinct values from the left query that are not also found on the right query.
So it will never ever return 5 rows, if NULL values come to play, there will be just one row with NULL value in it.
Good point, and maybe I should have included that in my explanation.
What you forget, though, is that EXCEPT applies the distinct algorithm to the entire result of the SELECT. And I think it's safe to assume that the table has more columns than just the NumericColumn used in the BETWEEN. In your repro, if you add a second column and make sure it has different values in the five rows with a NULL in NumericValue, you'll get 5 rows returned.