185: NULL and Three-Valued Logic: IS NULL, COALESCE, CASE, and Correct Predicates

Learning outcomes

By the end of this lesson, you can:

explain and apply three-valued logic in a realistic implementation;
explain and apply IS NULL and IS DISTINCT FROM in a realistic implementation;
explain and apply COALESCE in a realistic implementation;
explain and apply CASE in a realistic implementation;
explain and apply NULL in aggregates in a realistic implementation.

Prerequisites and retrieval

This lesson assumes the earlier 01–06 foundation and the preceding lessons in this module. Before you read on, retrieve one concrete example from a previous project where “missing,” “unknown,” or “not applicable” data affected a query or a piece of application logic. It might have been an optional timestamp, a nullable foreign key, or a report whose denominator was not what you first expected.

The goal is not to memorize a collection of NULL-related keywords. The goal is to make a defensible decision inside a PostgreSQL-backed transactional application, where schema design, correctness, query plans, and concurrency all matter. Keep the original business question in view: are you looking for an absent value, comparing two values including their absence, displaying a fallback, classifying a row, or measuring available data? Those questions often require different SQL.

Terminology

Three-valued logic: A comparison involving NULL usually produces UNKNOWN, not TRUE or FALSE. A WHERE clause keeps only rows for which its condition is TRUE.
IS NULL and IS DISTINCT FROM: Use IS NULL and IS NOT NULL to test for absence. PostgreSQL's IS DISTINCT FROM and IS NOT DISTINCT FROM provide comparison semantics that treat NULL as a comparable state rather than allowing the result to become UNKNOWN.
COALESCE: COALESCE evaluates expressions from left to right and returns the first non-null expression. It is useful when the query's output contract explicitly calls for a fallback.
CASE: CASE produces a value conditionally inside a query. It is useful for classifications, conditional aggregates, and computed output, as long as the conditions remain understandable and complete.
NULL in aggregates: Most aggregates ignore NULL inputs, while COUNT(*) counts rows and COUNT(column) counts non-null values. That difference is central to completeness reports and rate calculations.
Outer joins and NULL: An outer join supplies NULL placeholders for columns on the unmatched side. Those placeholders are not stored values in that related table; they represent the fact that no matching row was found.

Mental model

Treat NULL and Three-Valued Logic: IS NULL, COALESCE, CASE, and Correct Predicates as a design problem with observable inputs, outputs, invariants, and failure modes. SQL predicates can evaluate to TRUE, FALSE, or UNKNOWN. The third result is where many production bugs begin: application code often assumes a boolean, while SQL is also representing “the answer cannot be determined from this value.”

For example, if shipped_at is NULL, shipped_at = shipped_at is not TRUE. The comparison is UNKNOWN because the database cannot establish equality for an unknown value. Likewise, NOT (shipped_at = some_timestamp) is still UNKNOWN, not TRUE. The practical consequence is simple: absence needs an explicit predicate, and fallback output should not be confused with changing the stored fact.

A useful interview and production sequence is:

requirement -> constraints -> model -> implementation -> failure analysis -> verification

Do not jump from a requirement directly to a library call or a familiar SQL pattern. First state what must remain true. Then choose the mechanism that enforces it. For a nullable value, write down whether NULL means “not supplied,” “not yet known,” “not applicable,” or some other domain state. If those meanings have different behavior, they may need different columns, an explicit status, or a schema constraint rather than one overloaded NULL.

Deep dive

1. Three-valued logic

The immediate problem is that a query such as WHERE shipped_at = NULL returns no matching rows, even when some rows have no shipping timestamp. Comparisons with NULL usually produce UNKNOWN, and WHERE keeps only TRUE; both FALSE and UNKNOWN are filtered out. That is why column = NULL is not a valid presence check. Negating an expression involving NULL does not repair it: NOT (column = NULL) is also UNKNOWN.

When predicates are combined, the same rule matters. A row with an unknown comparison can disappear from WHERE status = 'open' AND shipped_at <> ..., and an apparently broad OR condition may still fail to include it. Test the truth table you actually need instead of relying on ordinary two-valued programming intuition.

Decision rule: Use three-valued logic deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design. In practice, that often means IS NULL, IS NOT NULL, or a separate domain condition at the point where absence matters.

2. IS NULL and IS DISTINCT FROM

Use IS NULL and IS NOT NULL for absence:

SELECT id
FROM orders
WHERE shipped_at IS NULL;

This asks the database directly whether the value is absent. It does not compare the column to a special literal.

PostgreSQL's IS DISTINCT FROM provides null-aware inequality semantics when NULL should be treated as a comparable state. a IS DISTINCT FROM b is false when both values are NULL, false when the values are equal, and true when exactly one is NULL or the non-null values differ. The inverse, IS NOT DISTINCT FROM, treats two NULLs as equal. This is useful for change detection, synchronization, and comparison of optional fields, but it should express a deliberate business rule rather than conceal one.

Decision rule: Use IS NULL and IS DISTINCT FROM deliberately when they make the contract or invariant easier to prove. If the query relies on a hidden assumption that a column is never NULL, enforce that assumption with NOT NULL or another constraint, or use the explicit null-aware predicate that matches the requirement.

3. COALESCE

COALESCE returns the first non-null expression:

SELECT id, COALESCE(discount, 0) AS discount_for_display
FROM products;

This is appropriate when the output contract says that a missing discount should be displayed or calculated as zero. It does not change the stored value, and it does not prove that “missing” and “zero” mean the same thing. If those states have different business meaning, use IS NULL to preserve the distinction and decide where the default belongs.

The expressions should also be type-compatible with the result you intend. Be clear about whether the fallback is a presentation choice, a calculation rule, or a data-cleaning operation. A default applied in a report may be correct while the same default applied before an eligibility decision is wrong.

Decision rule: Use COALESCE deliberately when it makes the contract or invariant easier to prove. If it only hides missing data whose distinction matters to business logic, keep the NULL visible and handle the decision explicitly.

4. CASE

CASE creates conditional values inside queries. A searched CASE is often the clearest way to classify rows or make a conditional aggregate:

SELECT id,
       CASE
         WHEN shipped_at IS NULL THEN 'pending'
         ELSE 'shipped'
       END AS shipping_state
FROM orders;

The condition must use the right NULL predicate. WHEN shipped_at = NULL will not classify the pending rows. Put more specific conditions first when they overlap, and include an ELSE when an unexpected or NULL input should have a known output rather than silently becoming NULL. Keep complex business workflows out of giant opaque CASE expressions; move durable rules to a constrained data model or an appropriate domain/service layer.

CASE is also useful in aggregates, for example COUNT(*) FILTER (WHERE ...) or SUM(CASE WHEN ... THEN 1 ELSE 0 END). Whichever form you choose, define what happens to NULL and define the denominator before calling the result a rate.

Decision rule: Use CASE deliberately when it makes the contract or invariant easier to prove. If the expression becomes a second application full of hidden workflow rules, separate the concerns and make the owning layer explicit.

5. NULL in aggregates

Most aggregates ignore NULL inputs. COUNT(*) counts rows, including rows whose target column is NULL, while COUNT(column) counts only rows where that column is non-null. For a table with ten rows and three non-null discount values, COUNT(*) is 10 and COUNT(discount) is 3. SUM(discount) totals the known discounts; it does not treat missing inputs as zero merely because a numeric result is desired. If no non-null values are available, many aggregates return NULL, so COALESCE may be appropriate for the report's output contract.

This difference matters for rates and completeness reports. A query reporting “percentage of orders that have shipped” needs a row count as its denominator and a count of rows satisfying shipped_at IS NOT NULL as its numerator. A query reporting an average price may need to distinguish “there are no known prices” from an average of zero.

Decision rule: Use NULL-aware aggregates deliberately when they make the contract or invariant easier to prove. Name the numerator, denominator, and treatment of missing inputs before implementing the query; otherwise a syntactically valid report can communicate the wrong metric.

6. Outer joins and NULL

Outer joins introduce NULL placeholders for unmatched sides. With a LEFT JOIN, every row from the left table remains, while columns from the right table are NULL when no match exists. A predicate placed in WHERE can accidentally remove those rows and effectively turn the outer join into an inner join. A predicate placed in the ON clause often preserves the left-side rows while limiting which right-side rows may match.

For example, filtering o.created_at in WHERE removes customers with no orders. If the requirement is “all active customers, with only recent orders when available,” put the order filter in the join condition and verify the result with a customer who has no matching order. Also distinguish an unmatched right row from a matched row whose nullable column happens to be NULL; counting a non-null right-side identifier is often the safer way to count actual matches.

Decision rule: Use outer joins and NULL deliberately when it makes the contract or invariant easier to prove. State which side must be preserved, which columns identify a real match, and where right-side filters belong before tuning the query.

Worked example

Consider a PostgreSQL-backed transactional application where schema design, correctness, query plans, and concurrency all matter. Suppose customers may be active or inactive, orders may or may not have been shipped, discounts may be missing, and an order may optionally be associated with a customer. Start by writing the requirement in one sentence, list the input and output contracts, and identify which of the concepts above owns each failure mode.

The important move is separation: parsing or validation belongs at the boundary; domain rules belong in the domain/service layer; persistence rules belong in the database or repository; presentation rules belong in the client. Mixing these concerns makes a happy-path demo look shorter, but it makes edge cases much harder to reason about. In particular, COALESCE in a display query should not silently redefine what an absent discount means to a pricing rule.

For the reporting requirement “show every active customer and the number of their orders,” this query preserves active customers even when they have no orders:

SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.status = 'active'
GROUP BY c.id, c.name
ORDER BY order_count DESC;

The LEFT JOIN preserves the active customer. COUNT(o.id) is zero for an unmatched customer because the right-side identifier is NULL, while COUNT(*) would count the preserved customer row and produce one. That is a small syntax choice with a direct reporting consequence.

Now add a requirement to show recent orders only. If customers with no recent orders must remain visible, keep that right-side filter in the join:

SELECT c.id, c.name, COUNT(o.id) AS recent_order_count
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
 AND o.created_at >= CURRENT_DATE - INTERVAL '30 days'
WHERE c.status = 'active'
GROUP BY c.id, c.name
ORDER BY recent_order_count DESC;

Walk the example with at least four cases: the normal path, an empty or missing value, a duplicate/retry/concurrent path where relevant, and a dependency failure. For this query, include an active customer with several orders, an active customer with none, an order with shipped_at NULL, and an order whose optional customer association is NULL. For each case, state which layer detects the problem and what the caller observes. This is the level of explanation expected in a senior code review or technical interview.

Inspect the execution plan after correctness is established. Confirm that indexes and row estimates support the actual workload, but do not replace a semantic check with a performance check. A fast query that counts preserved join rows incorrectly is still wrong.

Production perspective

Production correctness is broader than “the code works on my machine.” Ask how the design behaves during deploys, retries, partial failure, stale clients, concurrent requests, malformed data, schema changes, and high cardinality. A nullable column can be valid today and become a source of ambiguity after a new workflow is added. Prefer explicit contracts, bounded resource usage, structured errors, and measurable behavior. Optimize only after you can identify the bottleneck or risk with evidence.

When the topic involves an external dependency, define a timeout and cancellation strategy. When it involves persistence, define transaction and consistency expectations. When it involves user-visible state, define loading, empty, error, stale, and success states. When it involves security, assume the client can be modified and network input is untrusted. A client-side fallback is not a database constraint, and a report that hides NULL is not evidence that the underlying data is complete.

Guided lab

Create a dataset with nullable discount, shipped_at, and an optional customer association. Write queries that intentionally fail under naive NULL comparisons, then correct them with IS NULL, COALESCE, and null-aware predicates. Include at least one row for each meaningful state: a known discount, a missing discount, a shipped order, a pending order, a matched customer, and an order with no customer. Record the expected rows before running each query so that you are testing semantics rather than merely checking whether SQL executes.

Compare COUNT(*) with COUNT(shipped_at) and with a conditional count. Add a LEFT JOIN, then place a right-side filter first in WHERE and then in ON; explain why the result changes. Use IS DISTINCT FROM to compare two optional values and include the case where both are NULL.

Complete the lab with this discipline:

Write the requirement and two non-requirements.
List input, output, and error contracts before implementation.
Implement the smallest correct vertical slice.
Add at least one invalid-input test and one edge-case test.
Instrument or inspect the behavior instead of guessing.
Refactor one hidden assumption into an explicit type, constraint, function, or configuration.
Explain one alternative design and why you did not choose it.
Record a short “what would break at 10× scale?” note.

Edge cases and failure modes

Three-valued logic: Test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Include both column = NULL and NOT (column = NULL) so the UNKNOWN result is observed rather than assumed.
IS NULL and IS DISTINCT FROM: Test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Explicitly test both values NULL, exactly one value NULL, equal values, and different values.
COALESCE: Test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Verify that the fallback changes only the intended output and does not erase a business distinction.
CASE: Test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Exercise every branch, NULL input, overlapping conditions, and the ELSE behavior.
NULL in aggregates: Test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Compare COUNT(*), COUNT(column), SUM, and the chosen denominator for a rate.

Common mistakes and debugging

Solving the example instead of the requirement: a copied pattern can be syntactically correct but architecturally wrong. Confirm whether the requirement is testing absence, providing a fallback, comparing optional values, or measuring known data.
Hiding uncertainty with assertions, broad exception handlers, permissive schemas, or “temporary” any values. A default can make a query look complete while concealing a data-quality problem.
Testing only the happy path and therefore discovering contracts only after integration. Include unmatched joins, NULL inputs, empty aggregate sets, and both sides NULL in comparisons.
Optimizing before measuring, or selecting a scalable mechanism without a scale requirement. First verify the returned rows and counts, then inspect the execution plan and workload.
Letting client-side behavior stand in for server-side authorization, validation, or persistence guarantees. A UI that displays zero for NULL has not made the database value zero.

For debugging, reproduce the smallest failing case, inspect the actual value or execution plan, trace the boundary where the invariant first becomes false, and fix the owning layer rather than adding a downstream patch. Start at the database when the row set or aggregate is wrong; inspect the repository or transaction when persistence is wrong; inspect the service when a NULL is being assigned the wrong domain meaning; and inspect the client only when the server result is correct but displayed incorrectly.

Interview questions

What problem does three-valued logic solve, and what trade-off or failure mode would make you choose a different approach?
What problem do IS NULL and IS DISTINCT FROM solve, and what trade-off or failure mode would make you choose a different approach?
What problem does COALESCE solve, and what trade-off or failure mode would make you choose a different approach?
What problem does CASE solve, and what trade-off or failure mode would make you choose a different approach?
What problem does NULL-aware aggregation solve, and what trade-off or failure mode would make you choose a different approach?

Checkpoint

Without notes, explain NULL and Three-Valued Logic: IS NULL, COALESCE, CASE, and Correct Predicates to another developer in five minutes. Your explanation must include one invariant, one edge case, one production failure mode, and one alternative design. Then implement a small example without copying the lesson code. A strong answer should distinguish COUNT(*) from COUNT(column), explain why column = NULL fails, and describe how a LEFT JOIN can lose unmatched rows when filtered in WHERE.

Mastery checklist

I can define the core terms precisely.
I can choose a design from requirements instead of from habit.
I can implement and test the normal path and edge cases.
I can explain the runtime, storage, or complexity cost.
I can identify which layer owns validation, errors, and recovery.
I can compare at least two reasonable alternatives.
I can explain how the design changes at larger scale or stricter reliability.