Asked how many orders have not been cancelled, a model generates status <> 'cancelled'. The database successfully returns two orders, yet the answer may omit another whose status is unknown. The problem need not be table retrieval or duplicated joins: NULL changes comparison, filtering and aggregation. This discussion follows PostgreSQL 17 documentation and cross-checks the basic logic with SQL Server documentation. All records and numbers are constructed deductions; no database experiment was performed.
Distinguish an unknown value, zero and an absent record
Reliable enterprise Text-to-SQL translates business intent into verifiable calculation constraints. Null policy deserves its own constraint: a nullable field does not permit every query to ignore it, and permitted missing data does not automatically mean zero in a business total. This article focuses on selection and aggregation, extending the existing treatment of query governance and join fanout.
SQL NULL represents missing or unknown information rather than numeric zero or an ordinary empty string. Comparisons can produce TRUE, FALSE or UNKNOWN. Microsoft documents this three-valued logic and the IS NULL and IS NOT NULL tests.[1] The discussion concerns ordinary scalar comparisons; special database types or compatibility settings require separate validation.
Construct an orders table with unique, non-null order_id. Its columns are order_id, customer_id, amount and status. Four rows are: O1, C1, 100, paid; O2, C2, NULL, NULL; O3, NULL, 0, open; and O4, C3, 50, cancelled. Amounts are decimal monetary values in one currency. NULL here is not the text string 'NULL'.
Distinguish three origins: an unknown source field, no matching row in an outer join, and no usable aggregate input. Each may appear blank but needs a different explanation. Retain provenance status or supporting counts instead of giving one blank cell every meaning. Business owners must decide whether unknown status belongs in a pending-review set; the data team then implements the rule.
WHERE retains TRUE; negation does not resolve uncertainty
For the four orders, status <> 'cancelled' is TRUE for O1 and O3, FALSE for O4, and UNKNOWN for O2. The result is O1 and O3. PostgreSQL specifies that WHERE discards conditions evaluating to FALSE or NULL.[4] The result therefore means known status unequal to cancelled, which is not automatically every order that the business intends to treat as not cancelled.
If the approved policy includes unknown status in a review set, status <> 'cancelled' OR status IS NULL returns O1, O2 and O3, with O2 flagged for confirmation. If unknown states must be excluded from the official count, returning two is defensible while separately reporting one unresolved record. Either policy can serve a purpose; SQL defaults should not silently choose between them.
NOT (status = 'cancelled') does not fix the omission: negating UNKNOWN leaves uncertainty. Neither should missing values be tested with status = NULL. Partition the business predicate into satisfied, not satisfied and unknown, and display the counts when material. A predicate and its negation do not necessarily partition the entire row set.
The same issue affects monetary thresholds, approval flags and end dates. amount > 0 excludes both O2's missing amount and O3's genuine zero, for different reasons. An exception explanation should distinguish missing amount from known zero instead of labeling every excluded record as failing the business requirement.
Before replacing NOT IN, define what the list means
Construct excluded_customers with only C2 and NULL. Filtering orders by customer_id NOT IN (SELECT customer_id FROM excluded_customers) retains none: C2 matches an exclusion, while other values cannot be established as different from the unknown list value. PostgreSQL explicitly documents this behavior.[2] An empty result here does not establish an absence of orders; the predicate produced TRUE for none.
If the intended rule excludes known matching customer identifiers, use NOT EXISTS (SELECT 1 FROM excluded_customers e WHERE e.customer_id = o.customer_id), with o as the outer orders alias. This equality-based definition returns O1, O3 and O4. NOT EXISTS checks for matching rows; an unrelated null list entry does not reject every known customer.
However, O3's customer identifier is also unknown and finds no equality match, so that formulation retains it. If only orders with known customer identities are eligible, add o.customer_id IS NOT NULL; the result is O1 and O4. NOT EXISTS expresses a particular matching policy, not an unconditionally correct replacement. Filtering NULL out of an incomplete list does not establish that the list itself is valid.
Choose whether a missing list identifier is an isolatable data error, a limitation restricting analysis to known matches, or a risk that requires stopping the decision. For a critical list, an unidentified entry may prevent safe conclusions and require source repair. Also test an empty list: with no right-hand rows, NOT IN returns TRUE even for a null left value. It is incorrect to memorize a rule that null on the left always fails.[2]
Attach input completeness to COUNT and SUM
Across all four orders, COUNT(*) is 4, COUNT(amount) is 3, SUM(amount) is 150, and AVG(amount) is 50. The latter aggregates use non-null amounts, so the mean divides by three. PostgreSQL defines these distinctions.[3] The 150 is a sum of known amounts; without resolving O2, it is not an evidenced complete total for all orders.
Replacing each missing amount with zero changes AVG(COALESCE(amount,0)) to 37.5. Nothing fails technically, but the estimand changes from the mean of three known amounts to a four-order mean that assumes an unknown amount is zero. That assumption needs business evidence. Producing a populated report cell is not data repair.
Consider two boundary cases. Selecting only O2 gives row count 1, non-null amount count 0 and SUM(amount) NULL. Selecting no orders in a scalar aggregate query without GROUP BY gives row count 0 and SUM(amount) NULL as well.[3] SUM alone therefore cannot distinguish an existing but entirely missing input from no orders. A grouped query over empty input normally produces no groups, unlike the single result row of a scalar aggregate.
Return row_count, known_amount_count, missing_amount_count and known_amount_sum together: 4, 3, 1 and 150 in this example. On the same row set, COUNT(*) - COUNT(amount) yields the missing count. The metric contract determines whether a complete total may be presented, or whether the result must remain provisional. A few supporting fields give the model evidence for explaining the limitation.
LEFT JOIN preserves rows at the join stage, not through every later filter
Construct customers with C1, C2 and C3, each a unique non-null key. The requirement is to list every customer and their paid-order count. LEFT JOIN orders by customer identifier and then apply WHERE o.status = 'paid': only C1 remains. C2 has an order with unknown status and C3 has a cancelled order, both removed by the later filter. This distinction follows outer-join and WHERE semantics.[4]
Instead, put the paid condition in the matching rule: customers c LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'paid'. All three customers remain. Grouping by customer and using COUNT(o.order_id) yields C1: 1, C2: 0 and C3: 0. The order identifier must be non-null on actual order rows for this to be a reliable match count.
COUNT(*) on that same join gives C2 and C3 a count of 1 each because the outer join supplies a null-extended result row for each unmatched customer. That one counts a join result, not an order. Counting a nullable right-side field can instead miss actual matched records; select a dependable non-null fact key.
Adding OR o.order_id IS NULL to the original WHERE is not a general repair. C2 and C3 already matched orders with unknown and cancelled statuses, so no new null-extended row is generated to rescue that condition. Moreover, a zero count of known paid orders does not establish that there is certainly no payable obligation. O2's unresolved status still belongs in an independent completeness measure.
Put null semantics into generation constraints and counterexample acceptance
First, register nullability, possible null origins, exclusion policy, denominator meaning and missing-data output status for each certified metric. The model needs these rules before query generation. Without an approved policy, seek a specific clarification or label an exploratory answer with its limits. A generic instruction to remember NULL is not a business definition.
Second, inspect risky forms: direct comparisons with NULL, NOT IN with potentially nullable subqueries, null-rejecting WHERE conditions on the right of an outer join, and unexplained COUNT(nullable_column) or COALESCE choices. These are review signals rather than universal prohibitions: some queries intentionally need those semantics. Evaluate them against field constraints and metric definitions.
Third, freeze small counterexample fixtures with expected sets. Here, known non-cancelled orders are O1 and O3; including unresolved status gives O1, O2 and O3; excluding known listed customers while requiring known customer identities gives O1 and O4; one of four amounts is unknown; and all-customer paid-order counts are 1, 0 and 0. Compare order_id or customer_id sets as well as totals, since a missing row and an extra row can cancel numerically.
Fourth, add variants for empty tables, entirely null columns, zeros, empty strings, nulls in the right-hand list, unknown left-hand identities, unmatched outer joins and matched rows with unknown status. Run them on the actual database and deployed version, checking that the query builder, driver and display continue to preserve NULL. The deductions in this article do not substitute for that integration acceptance.
The final answer should identify known results, unresolved records and the data cutoff. When a discrepancy appears, locate the source field, predicate or aggregation policy before applying a repair. Neither filling zeros nor choosing different SQL can conceal an undefined metric. Trustworthy AI analytics needs correct numbers and an explanation of the facts those numbers cover, including information still unconfirmed.