Apart from all the issues with NULL confusing developers, NULLs have another very serious drawback: Performance

NULL'able columns are a disaster from a performance perspective. Consider integers aritmetic as an example. In a sane world without NULL, it is "easy" to vectorise integer arithmetic in the database engine code using SIMD instructions to perform pretty much any calculation at speeds faster than 1 row per CPU cycle. However, the moment you introduce NULL, you need to handle all the special cases that NULL creates. Modern CPU instruction sets (read: x86/x64/ARM and GPU logic too) are simply not equipped to do this efficiently.

Consider Division as an example. At a very high level, this is the logic you need with a non null integer:

if (b == 0)
  do something when dividing by error
else
  return a / b

With NULL, this becomes little more tricky. together with b you will need an indicator if b is null and similarly for a. The check now becomes:

if (b_null_bit == NULL)
   return NULL
else if (b == 0) 
   do something when dividing by error
else if (a_null_bit == NULL)
   return NULL
else 
   return a / b

The NULL arithmetic is significantly slower to run on a modern CPU than the not null arithmetic (by a factor of around 2-3x).

It gets worse when you introduce SIMD. With SIMD, a modern Intel CPU can perform 4 x 32-bit integer divisions in a single instruction, like this:

x_vector = a_vector / b_vector
if (fetestexception(FE_DIVBYZERO))
   do something when dividing by zero
return x_vector;

Now, there are ways to handle NULL in SIMD land too, but this requires using more vectors and CPU registers and doing some clever bit masking. Even with good tricks, the performance penalty of the NULL integer arithmetic creeps into the 5-10x slower range for even relatively simple expressions.

Something like the above holds for aggregates and to some extent, for joins too.

In other words: The existence of NULL in SQL is an impedance mismatch between database theory and the actual design of modern computers. There is a pretty good reason NULL confuses developers" because an integer cannot be NULL in most sane programming languages - because that is just not how computers work.

Apart from all the issues with NULL confusing developers, NULLs have another very serious drawback: Performance

NULL'able columns are a disaster from a performance perspective. Consider integers arithmetic as an example. In a sane world without NULL, it is "easy" to vectorise integer arithmetic in the database engine code using SIMD instructions to perform pretty much any calculation at speeds faster than 1 row per CPU cycle. However, the moment you introduce NULL, you need to handle all the special cases that NULL creates. Modern CPU instruction sets (read: x86/x64/ARM and GPU logic too) are simply not equipped to do this efficiently.

Consider division as an example. At a very high level, this is the logic you need with a non null integer:

if (b == 0)
  do something when dividing by error
else
  return a / b

With NULL, this becomes a little more tricky. Together with b you will need an indicator if b is null and similarly for a. The check now becomes:

if (b_null_bit == NULL)
   return NULL
else if (b == 0) 
   do something when dividing by error
else if (a_null_bit == NULL)
   return NULL
else 
   return a / b

The NULL arithmetic is significantly slower to run on a modern CPU than the not null arithmetic (by a factor of around 2-3x).

It gets worse when you introduce SIMD. With SIMD, a modern Intel CPU can perform 4 x 32-bit integer divisions in a single instruction, like this:

x_vector = a_vector / b_vector
if (fetestexception(FE_DIVBYZERO))
   do something when dividing by zero
return x_vector;

Now, there are ways to handle NULL in SIMD land too, but this requires using more vectors and CPU registers and doing some clever bit masking. Even with good tricks, the performance penalty of the NULL integer arithmetic creeps into the 5-10x slower range for even relatively simple expressions.

Something like the above holds for aggregates and to some extent, for joins too.

In other words: The existence of NULL in SQL is an impedance mismatch between database theory and the actual design of modern computers. There is a pretty good reason NULL confuses developers - because an integer cannot be NULL in most sane programming languages - that is just not how computers work.