I’m not sure what explanation you want, NULL is in essence “not a value”, which works more or less the same way “not a number” does. So yes if you perform operations between a NULL and a value you get a NULL, therefore when you sum or compare NULLs with or to other things you get NULL, which is then treated as false in a boolean context.
If the price of one item is UNKNOWN (which is what NULL represents in SQL) then it stands to reason that the sum is unknown, and it is unknown whether the sum is or is not smaller than 10000.
> But the end result has no conceptual integrity.
> Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?
> Extra challenge: explain why using COUNT instead of SUM in the query does correctly return orders with fewer than 4 items:
You don’t need to determine the values of the sequence to see that there are 4 of them, therefore count doesn’t care that some of the items are null.
If the price of one item is UNKNOWN (which is what NULL represents in SQL) then it stands to reason that the sum is unknown, and it is unknown whether the sum is or is not smaller than 10000.
> But the end result has no conceptual integrity.
> Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?
> Extra challenge: explain why using COUNT instead of SUM in the query does correctly return orders with fewer than 4 items:
You don’t need to determine the values of the sequence to see that there are 4 of them, therefore count doesn’t care that some of the items are null.