What is NULL?
Wednesday, October 21 at 10:45–11:35
"NULL" is a single token in SQL with at least six distinct meanings, and the standard is conspicuously quiet about which one you're getting at any given site. A NULL produced by an outer join doesn't mean the same thing as a NULL in an unpopulated column, which doesn't mean the same thing as the NULL SUM returns over an empty set, which doesn't mean the same thing as the NULL a ROLLUP super-aggregate writes into a grouping column. We call it "three-valued logic" as if that were the design. It isn't. The design is that we built one escape hatch and then stuffed everything we couldn't model into it.
This talk is the inventory I wish someone had handed me thirty years ago. I'll walk through every place SQL produces or accepts a NULL, what it actually means there, what the standard says about it (and where it declines to say anything), and what Postgres actually does at each of those sites. Postgres gets most of this right, which is interesting in itself, because "right" here means picking one reading of an ambiguous spec and living with the consequences. I'll point at the places where the consequences bite. I'll also detour through a few notable sins committed elsewhere, Oracle's insistence that the empty string is NULL chief among them, because the contrast sharpens what Postgres chose not to do.
If you've ever written x NOT IN (SELECT y FROM t), watched it return zero rows, and spent the rest of the afternoon working out why, this is the talk for you.
