Why should I avoid NULL values in a SQL database?

Question

I read a 45-tips-database-performance-tips-for-developers document from a famous commercial vendor for SQL tools today and there was one tip that confuse me:

If possible, avoid NULL values in your database. If not, use the appropriate IS NULL and IS NOT NULL code.

I like having NULL values because to me it is a difference if a value was never set or it 0 or string empty. So databases have this for a porpuse.

So is this tip nonsense or should I take action to prevent having NULL values at all in my database tables? Does it effect performance a lot have a NULL value instead of a filled number or string value?

Answer 1

Besides the reasons mentioned in other answers, we can look at NULLs from a different angle.

Regarding duplicate rows, Codd said

If something is true, saying it twice doesn’t make it any more true.

Similarly, you can say

If something is not known, saying it is unknown doesn't make it known.

Databases are used to record facts. The facts (truths) serve as axioms from which we can deduce other facts.
From this perspective, unknown things should not be recorded - they are not useful facts.
Anyway, anything that is not recorded is unknown. So why bother recording them?
Let alone their existence makes the deduction complicated.