But that would mean you need to be aware that the column can have this property, no?
Continuing with my middle name example. Say I and everyone I knew had middle names, so I write a database including a required middle name column. Later I discover not everyone has middle names, and so I need to relax the restriction.
In your case, I would change the column to accept NULLs, and I'd have to remember to go over my query to add the ~ operator.
In my case, I'd change the column to accept NAPs (or whatever) and since a NAP value would behave differently to a NULL for <> (and other operators), I wouldn't need to change my query.
Re: I'd have to remember to go over my query to add the ~ operator.
I'd almost always use it no matter what. In fact if starting SQL over via a DeLorean, I'd reverse it to require "~" to make it null-sensitive.
It's analogous to case-sensitive comparing. The vast majority of the time you don't want case-sensitive comparisons such that case-insensitive should be the DEFAULT, and you only add extra specifiers/functions if and when you need to do case-based comparing.
If not, then you either end up with SQL full of "toUpperCase(x)", or problems keep popping up if somebody typed stuff wrong or the UI coders messed up.
Similarly, if your SQL is full of NVL() or DENULL() or whatnot, it means the language was poorly factored for your domain, or even most domains. It flunked D.R.Y. in design, such as comparisons having the wrong default behavior.
Continuing with my middle name example. Say I and everyone I knew had middle names, so I write a database including a required middle name column. Later I discover not everyone has middle names, and so I need to relax the restriction.
In your case, I would change the column to accept NULLs, and I'd have to remember to go over my query to add the ~ operator.
In my case, I'd change the column to accept NAPs (or whatever) and since a NAP value would behave differently to a NULL for <> (and other operators), I wouldn't need to change my query.