You have to learn how your database handles NULLs, different databases do it slightly differently. Once you do that, you're fine.

One big thing is that NULL should never "mean" anything in a business sense. It is the absence of a value, hence it cannot mean anything.

By the same token you need to understand how your database handles concurrency. Different databases do it differently.

> One big thing is that NULL should never "mean" anything in a business sense. It is the absence of a value, hence it cannot mean anything

I always had a problem with that notion. I mean, it has a memory representation, it has a set of operators you can apply to it, defined behavior in UNIQUE and FOREIGN KEY constraints etc. All this is well documented (though can behave slightly differently between databases, as you mentioned).

So, it has a set of valid values (just one: NULL) and a set of valid operations, so it's a type!

And now you have a type that looks somewhat similar to a null in "normal" programing languages, and SQL generally lacks the mechanisms for inventing your own types, so why wouldn't you use it in your business logic where it makes sense?

The design of NULL seems like a historical accident anyway. The best I can discern, there was a need for something to behave as "excluded" in the context of FOREIGN KEYs and outer joins, and so that semantic was just passed along to other areas where it made less sense.

I think a better type system and a better separation between comparison logic and the core type would have obviated most of the NULL's weirdness and made it far less foot-gunny in the process...

The problem is that it isn't just me. It's everybody. Everybody that uses an SQL system has to learn a version of logic that looks like something they've learned before but actually has no relation to it. People can learn it, but it is a chore and takes a lot of real world experience (i.e. costly mistakes) to get it drilled into their head just like it did with me. As someone who does a lot of mentoring of data engineers, it's infuriating that they all have to go through this at some point.

Unfortunately the problem is made worse by the proliferation of languages making the equally heinous mistake of treating null as "false-y". Bad form, Peter, bad form!

Many businesses are absent of value :)

Agreed. The power of SQL is awesome. But really wish people started making better languages that compile to SQL; like it happened with JavaScript

Same. Edgedb is doing something good but I wish I didn’t have to deploy a new service on top of my own db. Postgres only as well.