TL;DR: SQL NULL represents missing or unknown information, and ordinary comparisons involving it generally produce unknown rather than true or false. A reliable schema must distinguish the business reasons a value is absent, enforce rules at the right boundary, and preserve those meanings in queries and reports. Replacing every NULL with zero or an empty string often makes the data easier to display while making it less accurate.
The design hearing below concerns a fictional equipment-rental company called Juniper. Its proposed reservation table includes a return date, an inspection result, a quoted price, a discount, and an optional external reference. Five teams agree on the column names and disagree about what an empty field means. The disagreement becomes visible only when the first dashboard and billing query are written.
The hearing uses PostgreSQL 16 as its explicit database reference. The relevant behavior is documented in its comparison operators, constraints, aggregate functions, and row and array comparisons. The rental policies and examples are invented to examine design choices; they are not claims about any real company's data.
Opening statement: What exactly is missing?
The operations team says a missing return date means the equipment is still on loan. The migration team says it sometimes means an old record has no reliable date. The customer service team says it can also mean a reservation was canceled before pickup. Those are three different states, and a single nullable date does not distinguish them.
The proposed dashboard counts every record with a missing return date as outstanding. It therefore includes canceled reservations and incomplete historical records. The query may be technically correct relative to its predicate while being wrong about the business. No SQL trick can recover distinctions that the schema never stored.
Juniper introduces an explicit reservation lifecycle: reserved, checked out, returned, and canceled. A return timestamp is meaningful for the returned state. Historical uncertainty receives its own documented migration status rather than being forced into an active rental category. The schema can now express why an absent date is acceptable in one state and suspicious in another.
The hearing begins with this domain distinction because NULL is a representation, not an explanation. A column can be nullable for several legitimate reasons, but downstream consumers need enough context to interpret the absence. That context may be a status field, a separate relation, or a clearly limited invariant.
Claim one: An unknown price is not a free rental
The pricing team proposes storing zero until a quote is ready. It argues that this avoids nullable arithmetic and makes the checkout screen simpler. The finance reviewer objects that zero is a valid price for a complimentary rental. The placeholder would make an unfinished quote indistinguishable from an intentional commercial decision.
Juniper retains a missing quoted amount while the quote is pending and stores a quote status. A confirmed complimentary quote has a numeric amount of zero and an explicit confirmation state. The distinction allows the product to say “price pending” instead of displaying a misleading free rental.
The billing boundary requires a confirmed quote before issuing the relevant invoice. That rule is stronger than a display fallback. A user interface that shows a dash for NULL does not prevent a background process from accidentally treating it as zero. The business invariant must be enforced where the consequential transition occurs.
The reviewer also asks about currency. An amount without currency is incomplete even when it is non-null. Juniper treats amount and currency as a coordinated value and specifies which states permit their absence. A schema with many individually reasonable nullable columns can still admit combinations that have no coherent meaning.
Claim two: A comparison can leave the answer unknown
A developer writes a filter intended to select rentals whose discount is not ten percent. A row with a missing discount does not behave like a row with a known discount of zero. In SQL's three-valued logic, an ordinary comparison with NULL generally yields unknown, and a WHERE clause retains rows whose condition is true.
That behavior surprises someone who reads “not equal” as “everything except the matching rows.” The right question is whether an unknown discount should qualify for the business task. If the report means “confirmed discounts other than ten percent,” excluding unknowns may be correct. If it means “records requiring review because they are not confirmed as ten percent,” the predicate needs to express that broader intention.
PostgreSQL provides NULL-aware comparison predicates such as IS DISTINCT FROM, alongside IS NULL tests. These tools are useful because they make a chosen interpretation explicit. They do not decide which interpretation Juniper should use. A reviewer should be able to translate the predicate into a sentence that matches the report's purpose.
The hearing rejects a style rule that bans nullable columns merely because three-valued logic requires care. It also rejects a style rule that automatically rewrites every comparison with a fallback. Both would replace business reasoning with a mechanical preference. The design must say what is known and what decision follows from that knowledge.
Evidence card: The three-row discount report
Consider three fictional rentals. Alder has a confirmed discount of ten percent. Birch has a confirmed discount of zero. Cedar has no recorded discount decision. A report for confirmed non-ten-percent discounts should contain Birch. A report for all rentals whose discount needs clarification or differs from ten percent should contain Birch and Cedar.
The data is identical in both cases. The intended result set differs because the question differs. This small example is useful in review because it forces the team to name the missing-value policy before it sees a complicated production query. A hundred-row fixture can obscure the same distinction under unrelated fields.
The product designer adds the labels shown to users. Birch displays “No discount.” Cedar displays “Discount not decided.” Both may ultimately produce the same charge after review, but they should not be presented as the same current fact. The interface should preserve uncertainty where it affects the next action.
The test suite records these expected memberships and labels. It does not merely assert that a query returns two rows, because the wrong pair could satisfy that count. Named records make the business distinction legible to reviewers who do not spend their day reading SQL.
Claim three: A CHECK constraint must address absence separately
The schema proposal includes a condition requiring quoted price to be nonnegative. The reviewer asks whether that condition also requires a value to exist. In PostgreSQL, a CHECK constraint is satisfied when its expression is true or unknown; a separate non-null requirement is needed when absence itself is forbidden.
Juniper therefore distinguishes “if present, the amount must be nonnegative” from “a finalized quote must have an amount.” The first is a value-domain rule. The second is a state-dependent completeness rule. Combining them carelessly can leave a path through which an incomplete quote becomes finalized.
For a simple table, a carefully designed row-level constraint can express the allowed relationship among status, amount, and currency. For more complex workflows, the application may also coordinate transitions across tables. The important point is to identify which invariant is guaranteed by the database and which depends on a transactional service operation.
The hearing asks for failure examples. A finalized quote with no currency must be rejected. A pending quote with no amount may be allowed. A confirmed zero amount must remain valid when the business permits complimentary rentals. These cases demonstrate that completeness and positivity are distinct requirements, and neither should be inferred from a convenient default.
Claim four: Uniqueness has a missing-value policy too
The external reference is optional because some reservations originate in Juniper's own interface and others arrive from partners. The team wants partner references to be unique within each partner. It also wants unlimited direct reservations without partner references. A naive global uniqueness rule could express the wrong scope even before NULL behavior enters the discussion.
PostgreSQL's uniqueness semantics allow a chosen treatment of nulls, including the documented NULLS NOT DISTINCT option. The schema author must decide whether two absent values should count as the same key for this constraint. That is a domain decision about identity, not an opportunity to choose the shortest declaration.
Juniper models partner identity and partner reference together. A direct reservation has no partner-origin record. A partner-origin record requires the partner's identifier and its reference. This separate relation makes the uniqueness rule apply exactly where an external identity exists, while keeping the reservation's own primary key stable.
Another valid design might use nullable columns with appropriate constraints. The hearing does not require one universal normalization pattern. It requires that reviewers can explain the allowed rows and the identity guarantee. An empty string inserted to avoid NULL would still need a policy and could create accidental collisions among unrelated direct reservations.
Claim five: An average can improve when data disappears
Juniper measures inspection duration. Three completed inspections take 10, 20, and 30 minutes. Two additional records have missing durations because their logging failed. An average over the known durations is 20 minutes. Treating the missing values as zero produces 12 minutes across five records.
Neither number should be labeled simply “average inspection time” without explaining its denominator. The first describes observed durations among records with measurements. The second makes an unsupported assumption that the missing inspections took no time. The latter can make a system outage look like an operational improvement.
PostgreSQL's aggregate documentation distinguishes counting rows from counting non-null expressions and describes how common aggregates treat null inputs. Juniper's dashboard therefore shows the observed mean together with the number measured and the number eligible. The display can say “20 minutes across three measured inspections; two missing.” That is enough information to prevent an apparent precision the data does not support.
The team also checks the all-missing case. A report with no observed durations should not silently display a zero-minute average. The appropriate output is an unavailable statistic with its reason. That distinction matters when charts connect points over time; a fabricated zero can create a dramatic trend that never occurred.
Claim six: A left join can lose the rows it was meant to preserve
The operations report lists every active rental and any associated inspection. A developer uses a left join, intending to retain rentals with no inspection. A later filter on an inspection field appears in the WHERE clause and excludes rows whose joined inspection values are missing. The result no longer matches the stated purpose.
The hearing does not prescribe moving every condition into the join. It asks which rows should remain. If the report means “all active rentals, with qualifying inspections when present,” the join condition and presentation should preserve unmatched rentals. If it means “active rentals that have a qualifying inspection,” excluding unmatched rows is intentional.
The difference becomes clearer with four fixtures: no inspection, a completed passing inspection, a completed failing inspection, and an unfinished inspection. A single nullable result column cannot always distinguish these states. Juniper retains an inspection identity and status so the report can separate absence of a record from a record whose result is not yet available.
The UI presents those states distinctly. “No inspection scheduled” and “Inspection in progress” trigger different actions. The database query should preserve enough evidence to support that distinction instead of collapsing both into a blank cell that the frontend guesses how to label.
Claim seven: NOT IN is not always the exclusion you meant
A bulk operation should find rentals whose identifiers are absent from an exclusion list. The list unexpectedly contains a NULL. SQL's NOT IN semantics can then produce unknown for candidates that do not match a known list member, causing results that surprise a developer expecting ordinary set subtraction.
PostgreSQL documents this behavior in its row and array comparison section. The hearing's practical response is to define the exclusion list's domain and choose a query form consistent with it. If the list represents actual rental identifiers, missing identifiers should be rejected or handled before the list becomes an authoritative exclusion set.
A correlated NOT EXISTS pattern can express some anti-join intentions more directly, but it must still use the correct matching condition and scope. Replacing syntax mechanically is not enough if the list mixes tenants, identifier types, or unresolved records. The business rule should be stated as a relationship between valid identities.
Juniper adds a test containing a malformed exclusion entry and verifies that the system reports it rather than silently producing an empty work list. A batch with no selected rows can look like success while leaving every intended task undone. The operational outcome matters more than whether the query itself returned without an error.
Claim eight: Defaults can erase the moment uncertainty began
The migration team wants to populate missing inspection results with “pending.” That may be appropriate for inspections genuinely awaiting work, but historical records with unknown outcomes are different. A default can convert missing historical evidence into a claim about a present workflow state.
Juniper records migration provenance and an explicit unknown historical outcome where needed. New inspections follow the normal lifecycle. Old records can be reviewed or corrected without pretending that a worker has an active task dating back several years. The distinction also keeps current backlog metrics meaningful.
The hearing asks when a default is applied: at insertion, during a backfill, in a query, or only in presentation. Each location has different consequences. A display fallback can be changed later without rewriting stored facts. A backfill permanently alters the dataset unless provenance is retained. An insert default can conceal a missing required input from an upstream integration.
The migration plan therefore includes a table of source conditions and chosen representations. It counts how many records enter each category and samples them for review. The goal is not to eliminate every NULL. It is to make each remaining absence and each filled value interpretable by future consumers.
Claim nine: A nullable field can encode too many states
The inspection result currently uses NULL, pass, and fail. Customer service asks for “unable to inspect,” while operations wants “inspection waived.” Adding more overloaded meanings to NULL would make queries increasingly fragile. A missing value should not become a secret code understood only by the original developer.
Juniper separates inspection status from inspection outcome. A completed inspection can have a pass or fail outcome. A waived inspection has a reason and authorizing context. An inspection that could not be completed records that event without inventing an outcome. The model makes invalid combinations visible rather than relying on comments to explain them.
This increases the number of fields, but it can reduce uncertainty in the application. The frontend no longer needs to infer workflow state from a mixture of timestamps and blank values. Reports can group records by explicit categories, and service transitions can validate the combinations they create.
The hearing still resists unnecessary complexity. If a small feature has only one well-defined reason for absence and no downstream distinction, a nullable field may be the clearest representation. The test is whether the current model can answer real operational questions accurately, not whether it achieves a particular number of tables or avoids NULL on principle.
Claim ten: Reporting needs a denominator contract
The finance dashboard shows the percentage of rentals with a completed inspection. Does the denominator include canceled reservations, equipment still on loan, waived inspections, or historical records with unknown status? The SQL expression cannot decide which population the organization intends to measure.
Juniper defines an eligible population and publishes the exclusions next to the metric definition. For a fictional month with 120 returned rentals, 100 completed inspections, 10 documented waivers, and 10 unresolved records, the team can report several useful quantities. Completed inspections are 100 of 120 returns, or about 83.3 percent. Completed or waived cases are 110 of 120, or about 91.7 percent. Neither should silently replace the other.
The unresolved ten remain visible. Excluding them from the denominator would produce a more flattering number while changing the question. If the organization wants a metric among resolved cases, it can publish that separately and label it accordingly. Missingness should not disappear through an undocumented denominator change.
The dashboard's query and prose definition are reviewed together. When a business rule changes, the team decides whether historical values should be recomputed and how the change is marked. Otherwise an apparent trend may be a change in missing-value treatment rather than a change in operations.
Cross-examination: What happens at the API boundary?
A JSON response can omit a field, include it with null, or provide a concrete value. Clients may interpret those states differently. Juniper defines the contract for partial updates separately from the contract for returned records: omission can mean “leave unchanged,” while an explicit null can mean “clear this optional value” where clearing is permitted.
That distinction must survive validation and persistence. A generic merge helper that converts omitted fields to null can erase data during a partial update. A client that drops all null fields can make an intentional clearing request impossible. The schema hearing therefore includes the transport layer rather than treating database nullability as an isolated concern.
The team also checks search indexing and analytics exports. A blank string substituted during export can later be imported as a known empty value, losing the original uncertainty. A spreadsheet consumer may need a separate status column to distinguish unknown from not applicable. The appropriate format depends on the downstream task, but the meaning should be documented at the boundary.
The acceptance test follows one record through creation, partial update, retrieval, reporting, and export. It verifies that a pending quote remains pending until explicitly confirmed and that a zero-price quote remains a real zero. This end-to-end example catches semantic loss that isolated database tests would miss.
Decision of the hearing
Juniper approves the schema after each nullable field has a defined meaning, each consequential state transition has a completeness rule, and each report has a named population. The external identity relation owns partner-reference uniqueness. Inspection status and outcome are separate. Historical uncertainty remains distinguishable from active work.
The implementation review also examines error messages. If a finalized quote lacks a currency, the service should identify the missing decision rather than expose a raw constraint name to a customer. Internally, the constraint remains useful as a last line of enforcement. Externally, the workflow should explain which field must be completed and preserve the user's other entries so the correction is straightforward.
Operational monitoring distinguishes rejected invalid states from unresolved valid states. A pending quote is expected during normal work; a finalized quote rejected for missing required data may indicate a broken integration. Counting both as generic missing-value errors would make the signal noisy and encourage teams to suppress useful checks. The metric should reflect whether the system is waiting for an authorized decision or preventing an impossible record from being stored.
Finally, schema comments name the owner of each meaning. If the pricing team later permits an estimated amount before confirmation, it must decide how that estimate differs from an agreed quote. The existing NULL policy cannot silently absorb the change. A small explicit contract gives future teams a place to revise semantics without reverse-engineering every report that once relied on a blank field.
The team keeps a small set of evidence cards beside the schema: zero versus unknown price, three discount states, missing duration measurements, unmatched inspections, malformed exclusion entries, and partial-update clearing. These examples are short enough to review whenever a query or API contract changes. They express business meaning more durably than a blanket rule about avoiding nulls.
The result is not a database without missing values. It is a database whose missing values do not silently change the meaning of a decision. Queries can now state whether they require known facts, include unresolved cases, or measure only observed values. Users see the same distinctions in the interface.
Unknown information is often an honest state of the world. A well-designed system can store it, constrain it, and explain it without inventing a number or hiding a record. That is the practical standard for SQL missing-value design: preserve what is known, make the relevant uncertainty visible, and prevent consequential workflows from treating uncertainty as a completed decision.