Your SQL Query Did Not Fail. NULL Changed the Question.

Your query returned rows. The database raised no error. The dashboard refreshed on schedule.

And the answer was still wrong.

That is the danger of NULL in SQL. It does not crash the query. It changes the logic underneath it.

Imagine a subscription table with three customers:

customer_id	status
101	active
102	cancelled
103	NULL

You want everyone who has not cancelled, so you write:

SELECT customer_id
FROM subscriptions
WHERE status <> 'cancelled';

Most people expect customers 101 and 103. SQL returns only 101.

Customer 103 is not equal to cancelled. But SQL cannot prove that it is unequal either. The comparison evaluates to UNKNOWN, and a WHERE clause keeps only rows where its condition is TRUE.

No error message appears because the database did exactly what SQL says.

This tutorial explains seven NULL behaviors that quietly change filters, exclusions, joins, counts, averages, and business metrics—and how to test them before they reach a dashboard.

NULL is not a value

NULL does not mean zero. It does not mean an empty string. It does not mean false. It does not even always mean “missing” in the business sense.

Technically, NULL marks the absence of a known value. In real datasets, that absence can mean several different things:

• the value has not arrived yet;
• the value does not apply;
• the value was not collected;
• the value was suppressed;
• the source system failed;
• nobody knows the value.

Those meanings are not interchangeable. SQL stores the same marker for all of them unless your data model adds more context.

NULL also introduces a third logical result. A predicate can be:

Predicate result	Meaning in a WHERE clause
TRUE	Keep the row
FALSE	Remove the row
UNKNOWN	Remove the row

That last line causes most of the surprises.

PostgreSQL's official documentation states that ordinary comparison operators return NULL—meaning unknown—when either input is NULL. That includes both equality and inequality. (Comparison functions)

Trap 1: = NULL and <> NULL never do what you intend

This filter returns no rows:

SELECT *
FROM customers
WHERE phone_number = NULL;

So does this one:

SELECT *
FROM customers
WHERE phone_number <> NULL;

Every comparison is unknown. Use the dedicated predicates:

WHERE phone_number IS NULL
WHERE phone_number IS NOT NULL

When you need to compare two nullable columns, ordinary equality has the same problem:

WHERE current_email = previous_email

If both columns are NULL, the result is unknown—not true. In PostgreSQL, use null-safe comparison:

WHERE current_email IS NOT DISTINCT FROM previous_email

IS NOT DISTINCT FROM treats two NULLs as equal. IS DISTINCT FROM is its null-safe “not equal” counterpart. Other databases expose similar ideas with different syntax, so check your engine before copying the expression.

Trap 2: one NULL can poison a NOT IN filter

Suppose you want customers who do not appear in a suppression list:

SELECT c.customer_id
FROM customers AS c
WHERE c.customer_id NOT IN (
  SELECT s.customer_id
  FROM suppression_list AS s
);

If the subquery returns (102, 205, NULL), a customer such as 101 is compared like this:

101 <> 102
AND 101 <> 205
AND 101 <> NULL

The first two comparisons are true. The last is unknown. TRUE AND TRUE AND UNKNOWN is unknown, so the row is filtered out.

The result can be an unexpectedly empty dataset.

PostgreSQL documents this explicitly: if no equal value is found and at least one right-hand row is NULL, NOT IN returns NULL rather than true. (Subquery expressions)

A safer anti-join pattern is NOT EXISTS:

SELECT c.customer_id
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM suppression_list AS s
  WHERE s.customer_id = c.customer_id
);

This asks a clearer question: “Does any matching suppression row exist?” A NULL in an unrelated suppression row cannot turn the entire decision into unknown.

You can filter NULL out of the subquery instead, but NOT EXISTS usually expresses the business intent more directly.

Trap 3: COUNT(*) and COUNT(column) count different things

These queries do not answer the same question:

SELECT COUNT(*) FROM claims;
SELECT COUNT(paid_amount) FROM claims;

COUNT(*) counts input rows. COUNT(paid_amount) counts rows where paid_amount is not NULL. PostgreSQL's aggregate documentation makes that distinction explicit. (Aggregate functions)

That difference is useful when it is intentional:

SELECT
  COUNT(*) AS claim_rows,
  COUNT(paid_amount) AS claims_with_payment,
  COUNT(*) - COUNT(paid_amount) AS claims_missing_payment
FROM claims;

It is dangerous when analysts call COUNT(paid_amount) “total claims.” The query is actually counting observed payment values.

The denominator can change quietly too:

SELECT
  COUNT(paid_amount) * 1.0 / COUNT(*) AS payment_completeness
FROM claims;

That metric is not a payment rate. It is field completeness. Name it for the question it answers.

Trap 4: a WHERE filter can erase your LEFT JOIN

A LEFT JOIN promises to retain every row from the left table, adding NULLs when there is no match.

Then this happens:

SELECT p.patient_id, c.claim_id
FROM patients AS p
LEFT JOIN claims AS c
  ON c.patient_id = p.patient_id
WHERE c.claim_status = 'paid';

Patients with no claims receive NULL for c.claim_status. The WHERE comparison becomes unknown, so those patients disappear. The result behaves like an inner join for this condition.

If your question is “keep every patient and attach paid claims where they exist,” put the filter in the join condition:

SELECT p.patient_id, c.claim_id
FROM patients AS p
LEFT JOIN claims AS c
  ON c.patient_id = p.patient_id
 AND c.claim_status = 'paid';

Predicate placement changes the population before aggregation. PostgreSQL's table-expression documentation distinguishes the ON condition, which determines join matches, from the later WHERE filter applied to the joined table. (Table expressions)

Always compare these counts when editing an outer join:

-- Expected population
SELECT COUNT(*) FROM patients;
 
-- Population after your join and filters
SELECT COUNT(DISTINCT p.patient_id)
FROM patients AS p
LEFT JOIN claims AS c
  ON c.patient_id = p.patient_id
WHERE ...;

If the second count falls, prove that the reduction is intentional.

Trap 5: aggregates ignore NULL—and empty groups return NULL

Most numeric aggregates skip NULL inputs. Consider three payment values:

100, 200, NULL

AVG(paid_amount) is 150, not 100. The database averages two known payments, not three claim rows.

Neither answer is universally correct. They answer different questions:

• 150 is the average recorded payment;
• 100 would be the average per claim only if a missing payment truly means zero.

That “if” is a business decision, not a SQL shortcut.

There is another edge case: except for COUNT, PostgreSQL aggregates such as SUM return NULL when no rows are selected. An empty result does not automatically become zero. (Aggregate functions)

This can propagate into arithmetic:

SELECT revenue - refunds AS net_revenue;

If refunds is NULL, net_revenue is NULL too.

Use COALESCE only after deciding what an empty set means:

SELECT
  COALESCE(SUM(revenue), 0)
  - COALESCE(SUM(refund_amount), 0) AS net_revenue
FROM transactions;

That may be correct for financial activity in an empty period. It may be wrong for a sensor whose missing readings should trigger an alert.

Trap 6: COALESCE can destroy information

COALESCE(a, b) returns the first non-NULL argument. It is excellent for presentation:

SELECT COALESCE(display_name, 'Unknown user')
FROM users;

It becomes risky when used to invent analytical facts:

SELECT AVG(COALESCE(satisfaction_score, 0))
FROM surveys;

A respondent who did not answer is now treated as maximally dissatisfied. The query did not fill a technical gap; it changed the population and the meaning of the metric.

PostgreSQL documents COALESCE as returning the first non-null argument, with later arguments evaluated only if needed. It does not claim that the replacement is semantically correct. (Conditional expressions)

A better pattern is to report the metric and its coverage together:

SELECT
  AVG(satisfaction_score) AS avg_score,
  COUNT(satisfaction_score) AS responses,
  COUNT(*) AS eligible_records,
  COUNT(satisfaction_score) * 1.0 / COUNT(*) AS response_rate
FROM surveys;

Now readers can see both the observed result and how much data supports it.

Trap 7: CASE logic can hide the unknown bucket

This expression looks binary:

CASE
  WHEN risk_score >= 80 THEN 'high'
  ELSE 'low'
END

A NULL risk_score does not satisfy the WHEN condition, so it falls into ELSE and becomes “low.” You have turned “not measured” into “low risk.”

Make the missing case explicit:

CASE
  WHEN risk_score IS NULL THEN 'unknown'
  WHEN risk_score >= 80 THEN 'high'
  ELSE 'low'
END

The same issue appears with nullable Boolean fields:

WHERE is_verified

This retains only true. Both false and NULL disappear. That may be correct for authorization. It is not the same as asking for records that are not explicitly false. PostgreSQL provides predicates such as IS TRUE, IS FALSE, and IS UNKNOWN when you need to name each state. (Comparison functions)

A five-minute NULL audit

Before trusting a query with nullable columns, run a short audit.

1. Profile presence, not just values

SELECT
  COUNT(*) AS rows,
  COUNT(target_column) AS known,
  COUNT(*) - COUNT(target_column) AS nulls,
  ROUND(
    100.0 * (COUNT(*) - COUNT(target_column)) / NULLIF(COUNT(*), 0),
    2
  ) AS null_pct
FROM source_table;

2. Split every important predicate into three buckets

SELECT
  COUNT(*) FILTER (WHERE amount > 0) AS true_rows,
  COUNT(*) FILTER (WHERE NOT (amount > 0)) AS false_rows,
  COUNT(*) FILTER (WHERE (amount > 0) IS UNKNOWN) AS unknown_rows
FROM transactions;

The FILTER syntax is PostgreSQL-specific, but the diagnostic idea works in any engine with conditional aggregation.

3. Test the adversarial row

For each important query, imagine or insert a tiny fixture containing:

• one matching value;
• one non-matching value;
• one NULL;
• one duplicate;
• no matching child row.

If you do not know what the query should return for each row, the business rule is unfinished.

4. Compare populations before and after

Record row counts and distinct entity counts at each stage:

SELECT COUNT(*), COUNT(DISTINCT customer_id)
FROM stage_name;

A query can preserve row count while changing which rows survive, so compare missingness and key coverage too.

5. Write the NULL meaning in plain language

For every important nullable field, document one sentence:

NULL in paid_amount means the claim has not received a finalized payment record as of the data cutoff.

That sentence tells analysts whether NULL should be excluded, imputed, labeled, escalated, or interpreted as zero. Without it, the SQL cannot encode the correct decision.

A practical review checklist

Before approving a query, ask:

1. Which columns in predicates, joins, and arithmetic can be NULL?
2. What does NULL mean in each source system?
3. Could a comparison evaluate to UNKNOWN?
4. Does a NOT IN subquery guarantee a non-null key?
5. Am I counting rows or non-null values?
6. Did a right-table WHERE predicate remove unmatched left rows?
7. What population does AVG actually average?
8. Does COALESCE replace missingness with a defensible business value?
9. Does CASE ... ELSE silently absorb NULL?
10. Have I tested a row with NULL explicitly?

Dialect differences matter

The core NULL model is part of SQL, but convenience syntax varies.

PostgreSQL supports IS DISTINCT FROM and IS NOT DISTINCT FROM. MySQL has the null-safe equality operator <=>. Some engines implement or optimize anti-joins differently. Boolean types, conditional aggregation, empty-string handling, and ordering of NULLs also vary.

So treat the examples here as a reasoning model first. Confirm the exact syntax and edge cases in your database's official documentation.

What would make this advice wrong?

NULL is not automatically a data-quality failure. A nullable discharge_date for a patient who is still admitted may be perfectly valid. A NULL middle_name may mean “not provided,” not “system broken.” Removing all NULLs can be as damaging as ignoring them.

NOT IN is not always unsafe. It behaves predictably when both sides are guaranteed non-null by enforced constraints and the left-hand expression is non-null. The problem is relying on an undocumented assumption.

Moving a predicate from WHERE to ON is not a universal fix either. It changes the question. Use it only when retaining all left-side entities is the intended population.

And replacing NULL with zero can be correct when the domain defines absence as zero—for example, no refund transactions in a closed reporting period. The point is to make that semantic decision explicitly and test it.

The deeper lesson

SQL errors are easy to notice. Semantic errors are not.

NULL turns apparently binary questions into three-way decisions. If you do not model the unknown case, the database will still make a decision for you: comparisons become unknown, filters discard the row, aggregates skip the input, and fallback branches absorb it.

The query will run.

The real safeguard is to define what missingness means, expose it in your metrics, and test the row that contains nothing.

NULL did not break the SQL. It revealed that the question was incomplete.

Key takeaways

1. SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN.
2. Ordinary comparisons with NULL evaluate to UNKNOWN; use IS NULL or a dialect's null-safe comparison.
3. A single NULL in a NOT IN subquery can prevent expected rows from passing; NOT EXISTS usually expresses an anti-join more safely.
4. COUNT(*) counts rows, while COUNT(column) counts non-null values.
5. A right-table filter in WHERE can remove unmatched rows from a LEFT JOIN.
6. Aggregates and COALESCE encode assumptions about missingness and denominators.
7. The most reliable defense is an explicit NULL fixture and a documented business meaning.

Primary references

• PostgreSQL Global Development Group, Comparison Functions and Operators.
• PostgreSQL Global Development Group, Subquery Expressions.
• PostgreSQL Global Development Group, Aggregate Functions.
• PostgreSQL Global Development Group, Table Expressions.
• PostgreSQL Global Development Group, Conditional Expressions.