Data Modeling

SQL NULL changes filters because ordinary comparisons involving NULL do not return TRUE or FALSE. They return UNKNOWN. In a WHERE clause, SQL keeps only rows where the predicate is TRUE, so UNKNOWN rows disappear just like FALSE rows. Most NULL bugs come from forgetting that a comparison can be neither true nor false.

Why NULL changes predicate reasoning

In ordinary two-valued logic, a statement is either true or false. SQL uses three-valued logic for predicates because databases often contain missing or inapplicable values. When a predicate touches NULL, SQL may not have enough information to decide whether the predicate is true or false, so the result is UNKNOWN.

This matters because practitioners often read a SQL condition as plain English. The condition country <> 'US' sounds like every row that is not in the United States. In SQL, it means rows where the database can prove that country is a known value different from 'US'. If country is NULL, the database cannot prove that.

The durable rule is simple: ordinary comparisons with NULL produce UNKNOWN, and filters usually keep only TRUE.

Operator rule

If a nullable column appears in a predicate, decide what should happen to NULL rows before you trust the result count.

A small dataset for counterexamples

Use this hypothetical customer table. The exact business meaning is intentionally simple: country may be missing, consent_status may be missing, referred_by_customer_id may point to another customer, and deleted_at is NULL for active customers.

The examples below are not engine benchmarks or product-specific tests. They are worked relational examples to show how three-valued logic affects predicate outcomes.

customer_id name country consent_status referred_by_customer_id deleted_at
1 Ada US opted_in NULL NULL
2 Ben NULL opted_out 1 NULL
3 Cy US NULL 99 NULL
4 Diya GB opted_in NULL 2026-01-01
5 Eli NULL NULL 2 NULL

WHERE keeps TRUE, not not-false

The most important operational fact is that WHERE does not keep every row that is not false. It keeps rows where the condition evaluates to TRUE. Rows where the condition evaluates to FALSE are removed. Rows where the condition evaluates to UNKNOWN are also removed.

That distinction explains many surprising query results. A filter for country = 'US' removes rows with country NULL because the comparison is UNKNOWN. A filter for country <> 'US' also removes rows with country NULL for the same reason. These two filters are not complements over the whole table unless country is guaranteed to be NOT NULL.

If you need to include missing values, say so explicitly. For example, model the intention as country <> 'US' OR country IS NULL, or use a NULL-aware comparison where your SQL engine supports one. If missing country should not be allowed, enforce that with the model rather than compensating in every query.

Memory aid

WHERE keeps TRUE. It does not keep UNKNOWN, even when UNKNOWN feels like it should mean not false.

Predicate TRUE rows FALSE rows UNKNOWN rows Rows kept by WHERE
country = 'US' 1, 3 4 2, 5 1, 3
country <> 'US' 4 1, 3 2, 5 4
NOT (country = 'US') 4 1, 3 2, 5 4
country = 'US' OR country <> 'US' 1, 3, 4 none 2, 5 1, 3, 4
country IS NULL 2, 5 1, 3, 4 none 2, 5

The minimum truth table practitioners need

You do not need to memorize every formal detail of three-valued logic to write safer SQL. You do need to know how UNKNOWN behaves with AND, OR, and NOT.

AND is strict when one side is FALSE: FALSE AND UNKNOWN is FALSE. OR is permissive when one side is TRUE: TRUE OR UNKNOWN is TRUE. NOT does not turn UNKNOWN into TRUE; NOT UNKNOWN is still UNKNOWN.

This is why adding more conditions can silently remove rows with missing values, and why negating a comparison does not usually recover the NULL rows you expected.

Expression Result Practical meaning
TRUE AND UNKNOWN UNKNOWN The row is not kept by WHERE unless another expression makes the whole predicate TRUE.
FALSE AND UNKNOWN FALSE One proven false condition is enough to make AND false.
TRUE OR UNKNOWN TRUE One proven true condition is enough to make OR true.
FALSE OR UNKNOWN UNKNOWN If the only possible match is unknown, WHERE will not keep the row.
NOT UNKNOWN UNKNOWN Negating an unknown comparison does not make it true.

Common counterexamples that expose NULL mistakes

The fastest way to teach SQL null and three-valued logic is to show counterexamples. These are the patterns that most often damage filters, metrics, and data quality checks.

  • Not equal is not the same as everything else. country <> 'US' excludes NULL country rows because the comparison is UNKNOWN.
  • The excluded middle does not cover NULL. country = 'US' OR country <> 'US' does not keep every row. Rows with country NULL still evaluate to UNKNOWN.
  • Comparing to NULL with equals does not work. deleted_at = NULL is UNKNOWN for every row. Use deleted_at IS NULL.
  • NOT IN can be unsafe when NULL appears in the list or subquery. If the set being checked contains NULL, SQL may be unable to prove that a value is not in the set. Prefer NOT EXISTS for anti-joins, or explicitly filter NULL out of the subquery when that matches the intended logic.
  • CASE WHEN only takes the TRUE branch. If a WHEN condition is UNKNOWN, it does not match that branch. It behaves like a non-match and falls through to the next WHEN or ELSE.

How UNKNOWN affects joins and outer joins

Join predicates are predicates too. In an inner join, a row pair is returned only when the join condition evaluates to TRUE. If customer.referred_by_customer_id is NULL, then comparing it to another customer_id does not produce TRUE, so there is no inner join match.

Outer joins add another layer. A LEFT JOIN preserves rows from the left side even when the ON condition finds no TRUE match. But a later WHERE clause can still remove those preserved rows if it tests a right-side column and the result is UNKNOWN.

For example, a LEFT JOIN from customers to referrers followed by WHERE referrer.country = 'US' will remove customers with no referrer, because referrer.country is NULL in the preserved unmatched row and the comparison is UNKNOWN. If the intention is to keep customers with no referrer while labeling which referrers are in the US, place the condition carefully or express the no-referrer case explicitly.

Constraints should express whether missing values are allowed

Three-valued logic is not only a query-writing issue. It is also a modeling issue. If a column is optional, downstream predicates must handle UNKNOWN. If a column is required for business logic, the model should say so with a NOT NULL constraint or an equivalent validated rule in the analytical layer.

CHECK constraints are also worth understanding. In SQL systems such as PostgreSQL, a check constraint rejects rows when the check is false, but NULL results do not necessarily fail the check. That means a rule such as amount > 0 may not reject a NULL amount unless amount is also constrained as NOT NULL.

The modeling lesson is practical: do not rely on a comparison predicate to do the work of requiredness. Requiredness and allowed values are separate ideas. Model both when both matter.

Modeling checkpoint

If NULL should be impossible, enforce requiredness. If NULL is legitimate, make every important predicate state how NULL should be handled.

Diagnostic questions for NULL-sensitive predicates

Before trusting a predicate that touches nullable columns, ask these questions.

  • Can this column be NULL? Check the model, not just recent data.
  • What should missing mean here? Missing may mean unknown, not applicable, not collected yet, or intentionally blank. Those are different business states.
  • Should NULL rows be included, excluded, or separated? Write the predicate to say that explicitly.
  • Is a negated predicate being used as a complement? If the column is nullable, the complement probably does not include NULL rows.
  • Does an outer join have right-side filters in WHERE? That often removes the rows the outer join was meant to preserve.
  • Could a NOT IN subquery return NULL? If yes, consider NOT EXISTS or filter NULLs deliberately.
  • Is a data quality test using a comparison but not a requiredness test? Add an explicit non-null test when NULL should fail.

A simple way to explain UNKNOWN to a team

When a stakeholder asks why the filter did not return the expected rows, avoid starting with formal logic. Use a sentence like this: SQL will only keep rows it can prove satisfy the condition. If a value is missing, SQL often cannot prove the condition either way, so the row does not pass the filter.

Then show one concrete row. If Ben has country NULL, the question country = 'US' cannot be answered from the stored data. The question country <> 'US' also cannot be answered from the stored data. Ben is not known to be in the US, but Ben is also not known to be outside the US. Both comparisons are UNKNOWN.

That framing keeps the discussion practical. The issue is not that SQL is being strange for its own sake. SQL is preserving the distinction between known false and unknown.

Key takeaways

  • SQL comparisons involving NULL usually evaluate to UNKNOWN, not TRUE or FALSE.
  • WHERE, HAVING, and join predicates keep rows or row pairs only when the relevant condition is TRUE.
  • A negated comparison is not a safe complement when nullable columns are involved.
  • Use IS NULL, IS NOT NULL, explicit OR branches, NOT EXISTS, or NULL-aware operators where appropriate.
  • Model requiredness separately from value rules; a comparison constraint is not the same as a NOT NULL rule.

Next step

Take one important dashboard query and mark every predicate that touches a nullable column. For each one, write the intended behavior for NULL rows: include, exclude, or separate. Then adjust the SQL or the model so the predicate says that explicitly.

Recommended next reads