Before I start, please understand that I am not criticizing nor asking for any kind of change. Rather, my purpose is more-or-less idle academic curiosity. My departure point has to do with this kind of syntax:
WHERE x NOT IN (SELECT y FROM anyTable)
Specifically, the circumstances in which y is a nullable column.
Many of you already know that this kind of WHERE clause should be avoided because of 3-state logic. 3-state logic cause this kind of WHERE clause to exclude all rows whenever there are ANY null values in the y column. The logic is to interpret
a NULL as a state in which the value of y is "unknown". The logic continues in that since Y is unknown it becomes therefore impossible to decide whether or not X is truely not in the Y column.
Fair enoug. Now, by academic logic and not based on how SQL works if we apply the "NULL represents an UKNOWN value" hypothesis and for the sake of consistency, should the MAX and MIN functions given in this circumstance also be NULL?
declare @test table (x tinyint)
insert into @test
select null union all select 3 union all select 250
--select * from @test
/* -------- Output: --------
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
I am not saying that th
View Complete Post