How do SQL interprets nulls?

Null values in SQL, how are they different from traditional nulls?

null, nil, or none is a special value that most programming languages reserve, and it usually means nothing or no value.

Developers usually mistake SQL nulls for having the same meaning, and they end up writing queries that produce unexpected results or a behaviour that is hard to debug.

SQL interprets null as I don’t know or unknown value rather than nothing or no value.

What does that mean?

To understand SQL nulls, we first need to know about the three valued logic. Assume that you are a database engine (cool right?), and you're given two black boxes (A) and (B) and asked to compare their content:

• Is black box A the same as black box B? You don't know.
• Okay, is A greater than B? You still can't tell!
• How about A being less than B? No idea!
• Hmm, maybe A = B is doable? Nope.

And that's exactly how a database engine treats null values; it just doesn't know.

How do we compare values against null then?
It depends, Postgres for example, uses the is operator.
So A is B will evaluate true, and there are other functions that can handle null comparisons, like distinct.
And that's why you hear people advising against using nulls especially in columns with indexes, because they can affect your index efficiency (more on that on another article).
Next time when you use null, be mindful of this.

Below a picture of the null truth table to make it easier to understand.