An equality test against an absent value returns no rows, and negating it returns every row — why?

## Why comparison is the wrong instrument An equality test asks *are these two values the same*. Absence is not a value; it is the recorded fact that no value exists. Asking whether a cell's value equals the absence of a value is not a question the comparison machinery can answer honestly, and every design in this family declines it — but they decline it in different ways, and the difference is what the question is really probing. There are two mechanisms, and keeping them apart is the whole of the answer. ## Mechanism one: a reserved pattern inside the value Some designs write absence into the number itself, borrowing the bit pattern the floating-point format sets aside for a result with no numeric answer. That pattern is not a number, and the format specifies how it compares: it is **unordered** with respect to everything. The consequences follow mechanically: - `equals` is **false** for every row, including rows that do hold that very pattern — the pattern is not even equal to a copy of itself; - `less than`, `greater than` and their non-strict forms are **false** for every row, for the same reason; - the **not-equal** form is **true** for every row, because "not equal" is satisfied by "unordered" just as it is by "different". So the equality test selects nothing and its negation selects everything. Neither result is the set of holes, and the second one is the more dangerous of the two because it looks like a full table rather than an empty one. ## Mechanism two: a typed marker under a third outcome Other designs define their own absence marker and give comparison a **third outcome** alongside true and false. The comparison does not lie and does not guess; it declines to commit, because the value on one side is unknown and no claim about it can be justified. That third outcome then has to become a decision somewhere before rows are selected, and designs differ again on what they do: - coerce it to false, so the rows drop out of both the test and its negation; - refuse the selection outright when the condition contains it, which is the only loud behaviour; - carry it through into a result that is itself partly indeterminate. Negation does not rescue it. The negation of an outcome that is neither true nor false is still neither. ## The two side by side | | Reserved pattern inside the value | Typed marker with a third outcome | |---|---|---| | Equality against absence | False on every row | Neither true nor false | | Not-equal against absence | True on every row | Neither true nor false | | Absence compared with absence | Reported as unequal | Neither true nor false | | Rows the equality test returns | None | None, once coerced | | Rows the negated test returns | All of them | None, once coerced | The row that catches people out is the last one. A candidate who has only used one of these designs will state its behaviour as the rule, and be confidently wrong in front of someone who has used the other. ## What to write instead Every design here ships a **dedicated cell-by-cell absence test**: a predicate that asks, for each position, whether the cell holds a value at all. It does not compare contents; it inspects presence, which is the only question with an answer. 1. Use that predicate whenever the question is *is this cell empty* — never an equality or an inequality operator. 2. Use its complement when you want the present values, rather than negating a comparison and hoping. 3. When the condition column feeding a selection may contain absence, state explicitly what should happen to those rows instead of inheriting whichever coercion the surface applies. ## Why the designs do not simply make equality work It is tempting to ask why a tool does not define absence to compare equal to absence and be done with it. Two reasons, one per mechanism: - Under the reserved-pattern design, the unordered-comparison rule comes from the number format, not from the tool. Overriding it for one operator would make the column's arithmetic and its comparisons disagree about the same bit pattern. - Under the typed-marker design, saying that absence equals absence asserts that two unknown quantities are the same quantity. That is exactly the claim the design exists to refuse, and asserting it would silently merge two customers whose identifiers were both never recorded. Which sets up the genuinely surprising part, and the one seniors are expected to know: the surfaces that *group*, *de-duplicate*, *order* and *match on a key* often do treat all absences as one and the same key, in the same tool, in the same session. The scalar rule described here does not predict them.