Nulls are also called the billion-dollar mistake (and that was decades ago; it's much more than that now). Both nulls and exceptions are ways of trying to make the main line of processing clear, while handling other lines in structured ways.

There's no one-size-fits-all solution. In a lot of ways, the best response to "record not found" is that you get the same result as finding one, except with zero answers. That means your main line can process the same way -- unless that doesn't produce the result you need. In which case maybe you an if(null) somewhere, or a special fake record, or an exception.

> Nulls are also called the billion-dollar mistake (and that was decades ago; it's much more than that now). Both nulls and exceptions are ways of trying to make the main line of processing clear, while handling other lines in structured ways.

At the same time before NULL, devs used to use "guard values", so NULL is really just a convenience.

for instance, just to illustrate what I'm saying:

    let NULL = {} /* should every lib define its own null value? */

    function GetOneRecord(){
        let dbResult = queryRecordsFromDB();
        if (dbResult.length === 0){
            return NULL
        }
        return dbResult.records[0];
    }

Optional types, are better, but one needs functional programming features for it to be really useful.

What's people opinion on zero values by the way?

Zero values may act as nice defaults. Programmers who care about their code, often want initial state or default state to reflect something useful. What zero value should reflect depends on context, but you get to state that once and for all, for that type. It's just one less thing off a mind when building code, although you'd want to catch any invalid usage. Ie. referencing zero value, could result in error if due to control flow, and panic() in case of programmer error. I don't think the language or library should choose for you, unless being bound by that is a very useful trade-off.

Yeah the problem really is 'in band signaling' See SQL injection attacks for an another example why the practice is dodgy.

What "functional programming features" does one need for useful optional types?

Putting aside the "functional" bit, for a language to allow user-defined (i.e. not hardcoded into the language itself (which may not necessarily be a bad thing, but that's beside the point here)) optional types, you'd want the ability to say "this variable is either _this_ XOR _this_" (a.k.a. tagged unions/sum types/what Rust calls "enums"), and then in a statically-typed language you'd additionally want generics.

Null _pointers_ are called the billion-dollar mistake, not null values.