Checking NULL Rates — Check for NULL rates and missing keys - SQL

SECTION 1

Historical Context & Motivation

The concept of NULL in relational databases traces back to E. F. Codd's foundational work on the relational model in the early 1970s. Codd introduced NULL as a marker for missing or inapplicable information, distinct from any actual data value such as zero or an empty string. This three-valued logic—TRUE, FALSE, and UNKNOWN—was elegant in theory but introduced subtle pitfalls that have plagued practitioners for decades. As organizations began to rely on SQL-based systems for critical decision-making, the consequences of unchecked NULLs grew from minor inconveniences into material business risks, corrupting aggregate calculations, breaking JOIN operations, and silently excluding rows from result sets.

1970 • Codd's Relational Model

E. F. Codd publishes 'A Relational Model of Data for Large Shared Data Banks,' introducing the concept of NULL to represent missing or inapplicable data in relational tuples.

1986 • SQL-86 Standard

The first ANSI SQL standard formalizes NULL semantics including three-valued logic, IS NULL predicates, and the behavior of NULLs in aggregate functions like SUM and COUNT.

1999 • Data Warehousing Era

As data warehouses scale to terabytes, Kimball and Inmon methodologies emphasize NULL auditing during ETL processes. Surrogate keys and default dimension rows become standard strategies for handling missing references.

2010s • Big Data & Data Lakes

Schema-on-read architectures amplify NULL problems: semi-structured data ingested from APIs, IoT sensors, and logs introduces unpredictable NULL patterns that traditional constraints cannot catch at write time.

2020s • Data Observability & Contracts

Tools like Great Expectations, dbt tests, and Monte Carlo formalize NULL-rate checks as automated data quality assertions, treating NULL monitoring as a first-class software engineering practice.

Despite half a century of tooling improvements, the fundamental question remains the same: how many NULLs are acceptable in a given column, and what happens when foreign keys silently reference rows that do not exist? This lesson equips you with the SQL patterns, mathematical reasoning, and debugging intuition to answer that question rigorously.

SECTION 2

Core Principles & Definitions

Before writing any diagnostic queries, it is essential to internalize the foundational concepts that govern NULL behavior and missing-key detection. These principles inform both the structure of your audit queries and the interpretation of their results. A NULL rate is simply the fraction of rows in a column that contain NULL rather than a concrete value. A missing key (also called an orphan foreign key) occurs when a child table references a primary key value that does not exist in the parent table, a condition that unenforced foreign-key constraints allow to persist silently.

Three-Valued Logic

Any comparison involving NULL yields UNKNOWN, not FALSE. This means WHERE x = NULL returns zero rows—you must use IS NULL instead.

NULL Rate as a Metric

The NULL rate of a column is the ratio of NULL-valued rows to total rows: COUNT(*) - COUNT(col) gives the NULL count because COUNT(col) ignores NULLs.

Orphan Foreign Keys

When a child table's foreign-key column contains a value that has no matching primary-key row in the parent table, that row is an orphan. A LEFT JOIN where the parent side is NULL exposes these.

Aggregate Distortion

Functions like AVG, SUM, and COUNT(col) silently skip NULLs. A column with a 40% NULL rate returns an average computed from only 60% of rows, potentially yielding a misleading statistic.

Schema vs. Runtime Enforcement

NOT NULL constraints and FOREIGN KEY constraints enforce integrity at write time, but many production systems disable them for performance. Runtime auditing via SQL queries becomes the safety net.

✦

KEY TAKEAWAY

Think of NULL-rate checking like a structural inspection on a building. The building may look fine from the outside, but a certain percentage of steel bolts inside are actually missing. Below a threshold the structure holds; above it, catastrophic failure becomes likely. Your SQL audit queries are the X-ray machine that reveals whether the ratio of missing bolts has crossed the danger line.

SECTION 3

Visual Explanation — NULL Rate Anatomy

The following diagram illustrates how a NULL-rate audit operates on a sample table. Each column is inspected independently: the total row count is compared against the non-NULL count to produce a NULL rate expressed as a percentage. Columns exceeding a predefined threshold are flagged for investigation.

Each bar represents the distribution of valid versus NULL values for a column. The shipping_addr column fails at 30% NULLs, while promo_code at 60% NULLs is acceptable because it is nullable by design—not every order uses a promotion.

Notice that a high NULL rate is not inherently problematic—promo_code is expected to be NULL for orders placed without a coupon. The key insight is that you must define expected NULL behavior per column and alert only on deviations from that expectation. A column marked NOT NULL in the logical schema should have a 0% NULL rate; a column that is nullable by business rule might tolerate up to 80%. Your audit queries should encode these thresholds explicitly.

SECTION 4

How It Works — SQL Patterns for NULL Auditing

The mathematical foundation of NULL-rate checking is straightforward, but the SQL idioms that implement it exploit subtle behaviors of aggregate functions and set operations. Understanding these mechanics precisely is what separates a cursory check from a production-grade data quality assertion.

NULL RATE FORMULA

NULL_rate(col) = (COUNT(*) − COUNT(col)) / COUNT(*)

COUNT(*) counts all rows including NULLs. COUNT(col) counts only non-NULL values in that column. The difference gives the NULL count. Dividing by total rows yields a rate between 0 and 1.

ALTERNATIVE USING SUM + CASE

NULL_rate(col) = SUM(CASE WHEN col IS NULL THEN 1 ELSE 0 END) / COUNT(*)

This form is more explicit and generalizable—you can replace the IS NULL predicate with any condition to compute rates for arbitrary data quality dimensions (e.g., empty strings, out-of-range values).

Detecting Orphan Foreign Keys

ORPHAN KEY DETECTION VIA LEFT JOIN

SELECT c.fk_col FROM child c LEFT JOIN parent p ON c.fk_col = p.pk_col WHERE p.pk_col IS NULL AND c.fk_col IS NOT NULL

The LEFT JOIN preserves all child rows. Where the parent side is NULL after the join, the child's foreign key has no matching parent—an orphan. The second condition c.fk_col IS NOT NULL excludes rows where the foreign key itself is NULL, which may be intentionally nullable.

ORPHAN KEY DETECTION VIA NOT EXISTS

SELECT c.fk_col FROM child c WHERE c.fk_col IS NOT NULL AND NOT EXISTS (SELECT 1 FROM parent p WHERE p.pk_col = c.fk_col)

The NOT EXISTS approach is semantically equivalent and sometimes more efficient, as the optimizer can short-circuit the subquery on the first matching row. Both patterns should be in your toolkit.

💡

Performance Note

On large tables (100M+ rows), computing NULL rates column-by-column with individual queries is expensive. Instead, audit all columns in a single pass using conditional aggregation:

SELECT COUNT(*) AS total, SUM(CASE WHEN col1 IS NULL THEN 1 ELSE 0 END) AS col1_nulls, SUM(CASE WHEN col2 IS NULL THEN 1 ELSE 0 END) AS col2_nulls, ... FROM table_name

. This performs a single table scan rather than N scans.

SECTION 5

Classifying NULL Scenarios & Missing-Key Topology

Not all NULLs are created equal. Before you can set meaningful thresholds, you need to classify each NULL-bearing column into one of several categories based on its semantic intent and expected missingness pattern. The diagram below presents a decision tree for classifying NULL scenarios and choosing the appropriate audit strategy.

The decision tree classifies NULLs into three actionable categories: intentional NULLs that require lenient thresholds, ETL bug NULLs that require pipeline fixes, and orphan foreign keys that require reconciliation with the parent table.

NULL Category	Example Columns	Typical Threshold	Audit Strategy
Intentional	`promo_code, middle_name`	≤ 80–95%	Monitor for unexpected spikes (e.g., sudden 100%)
Required Field	`email, amount, created_at`	0% (strict)	Alert immediately on any NULL; add NOT NULL constraint
Soft Required	`shipping_addr, phone`	≤ 5–10%	Warn on threshold breach; investigate upstream source
Foreign Key	`customer_id, product_id`	0% orphans	LEFT JOIN audit; reconcile or cascade delete orphans

NULL classification matrix with recommended thresholds and audit strategies

SECTION 6

Worked Example — Full NULL Audit on an E-Commerce Schema

Consider an e-commerce database with three tables: orders (columns: order_id, customer_id, amount, shipping_addr, promo_code), customers (columns: customer_id, email, name), and products (columns: product_id, title, price). We will perform a complete NULL-rate audit and orphan-key check.

NULL Rate Audit & Orphan Key Detection

1
Step 1 — Compute NULL rates for all columns in orders

We use conditional aggregation to check all columns in a single pass: SELECT COUNT(*) AS total_rows, ROUND(1.0 * SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) / COUNT(*), 4) AS customer_id_null_rate, ROUND(1.0 * SUM(CASE WHEN amount IS NULL THEN 1 ELSE 0 END) / COUNT(*), 4) AS amount_null_rate, ROUND(1.0 * SUM(CASE WHEN shipping_addr IS NULL THEN 1 ELSE 0 END) / COUNT(*), 4) AS shipping_null_rate, ROUND(1.0 * SUM(CASE WHEN promo_code IS NULL THEN 1 ELSE 0 END) / COUNT(*), 4) AS promo_null_rate FROM orders;

Result: total_rows = 10000, customer_id_null_rate = 0.0320, amount_null_rate = 0.0000, shipping_null_rate = 0.2850, promo_null_rate = 0.6100
2
Step 2 — Interpret against thresholds

Based on our classification: customer_id is a foreign key and should have 0% NULLs, but shows 3.2%—this is a problem. amount at 0% is healthy. shipping_addr at 28.5% exceeds the 10% soft-required threshold. promo_code at 61% is within the intentional-NULL tolerance.

Two columns flagged: customer_id (3.2% NULLs) and shipping_addr (28.5% NULLs)
3
Step 3 — Detect orphan foreign keys in customer_id

For non-NULL customer_id values, check whether they exist in the customers table: SELECT COUNT(*) AS orphan_count FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id WHERE c.customer_id IS NULL AND o.customer_id IS NOT NULL;

Result: orphan_count = 147 — meaning 147 orders reference customer IDs that do not exist in the customers table.
4
Step 4 — Compute orphan rate and investigate

The orphan rate among non-NULL foreign keys is 147 / (10000 − 320) = 147 / 9680 ≈ 1.52%. To investigate, list the distinct orphan customer_id values: SELECT DISTINCT o.customer_id FROM orders o WHERE o.customer_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id) ORDER BY o.customer_id;

This reveals the specific customer IDs that were deleted or never loaded, enabling targeted data reconciliation.
5
Step 5 — Build a reusable audit summary

Combine all metrics into a single diagnostic view using UNION ALL to produce a column-by-column report: SELECT 'customer_id' AS col_name, 'FK / required' AS category, 0.032 AS null_rate, 0.0 AS threshold, 'FAIL' AS status UNION ALL SELECT 'amount', 'required', 0.0, 0.0, 'PASS' UNION ALL SELECT 'shipping_addr', 'soft_required', 0.285, 0.10, 'FAIL' UNION ALL SELECT 'promo_code', 'intentional', 0.61, 0.80, 'PASS';

Final audit: 2 of 4 audited columns fail their thresholds. 147 orphan foreign keys detected. Immediate remediation required for customer_id NULLs and shipping_addr data gaps.

SECTION 7

Strengths, Limitations & Method Comparison

Every NULL-detection strategy involves tradeoffs between completeness, performance, and maintainability. Understanding these tradeoffs allows you to choose the right approach for your database's scale and governance maturity.

Method	Strengths	Limitations
*COUNT() − COUNT(col)**	Concise, leverages built-in aggregate optimization, works on all SQL engines	Only checks for NULL, misses empty strings or sentinel values like -1 or 'N/A'
SUM(CASE WHEN ... THEN 1 ELSE 0 END)	Highly flexible—can detect NULLs, empty strings, and out-of-range values in the same scan	More verbose; requires explicit enumeration of each column and condition
LEFT JOIN (orphan detection)	Intuitive, returns the orphan rows themselves for manual inspection	Can be slow on large tables without indexes on the join columns; may produce large result sets
NOT EXISTS (orphan detection)	Often faster than LEFT JOIN due to short-circuit evaluation; cleaner semantic intent	Correlated subquery can confuse beginners; some older optimizers may not flatten it efficiently
INFORMATION_SCHEMA profiling	Can dynamically generate audit queries for all columns in a table without hardcoding column names	Requires dynamic SQL or scripting layer; metadata views vary across DBMS vendors

Comparison of NULL-rate checking and orphan-key detection methods

✦

KEY TAKEAWAY

Choosing between LEFT JOIN and NOT EXISTS for orphan detection is like choosing between a full table comparison and an index lookup. In production systems with proper indexing on foreign-key columns, NOT EXISTS often wins because it can terminate its search as soon as one match is found. However, when you need to return the actual orphan rows for remediation, the LEFT JOIN pattern is more natural because the unmatched rows are directly available in the SELECT clause.

SECTION 8

Connection to Advanced Data Quality Engineering

NULL-rate checking is the entry point to a broader discipline known as data observability, which applies software reliability engineering principles—monitoring, alerting, incident response—to data pipelines. Modern frameworks extend simple NULL checks into temporal anomaly detection, schema drift monitoring, and automated data contracts.

Concept (This Lesson)	Advanced Extension	Tool / Framework
Static NULL rate (point-in-time)	Time-series NULL rate monitoring with anomaly detection (z-score thresholds)	Monte Carlo, Anomalo
Manual threshold setting	Learned thresholds from historical NULL distributions	Great Expectations, Soda Core
Orphan FK detection via LEFT JOIN	Automated referential integrity tests in CI/CD for dbt models	dbt tests (relationships)
Per-column audit query	Schema-level data contracts enforcing NULL constraints declaratively	Data contracts, protobuf schemas

Bridging basic NULL auditing to advanced data quality engineering

As you progress into roles involving data engineering or analytics engineering, the SQL patterns you learn here become the foundation for automated, production-grade data quality pipelines. A dbt relationships test, for instance, is syntactic sugar over the exact LEFT JOIN orphan-detection pattern covered in this lesson. Understanding the SQL underneath these abstractions gives you the ability to debug failures, customize thresholds, and extend frameworks to cover edge cases that out-of-the-box tools miss.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

Explain why WHERE email = NULL returns zero rows even when the email column contains NULL values. What is the correct predicate to use, and why does SQL behave this way?

Show answer

SQL implements three-valued logic, where any comparison involving NULL evaluates to UNKNOWN rather than TRUE or FALSE. Since WHERE clauses only pass rows that evaluate to TRUE, the expression email = NULL yields UNKNOWN for every row—including rows where email is actually NULL—and therefore no rows pass the filter. The correct predicate is WHERE email IS NULL, which is a special operator designed to test for the NULL marker. This behavior stems from Codd's original relational model, which treats NULL as 'value unknown' rather than as a concrete value that could participate in equality comparisons.

Question 2: BASIC CALCULATION

Write a SQL query that computes the NULL rate for the phone_number column in the customers table. Express the result as a percentage rounded to two decimal places.

Show answer

The query uses the formula NULL_rate = (COUNT() − COUNT(col)) / COUNT(): SELECT ROUND(100.0 * (COUNT(*) - COUNT(phone_number)) / COUNT(*), 2) AS phone_null_pct FROM customers; Here, COUNT(*) counts all rows regardless of NULLs, while COUNT(phone_number) counts only non-NULL values. Multiplying by 100.0 (a decimal literal) forces floating-point division rather than integer division, which would truncate the result to 0 on many SQL engines. ROUND with a precision of 2 gives a human-readable percentage. An equivalent approach uses SUM(CASE WHEN phone_number IS NULL THEN 1 ELSE 0 END) as the numerator.

Question 3: INTERMEDIATE

You have an order_items table with columns (item_id, order_id, product_id, quantity, unit_price). Write a single query that returns one row per column, with columns: col_name, total_rows, null_count, null_rate. Use UNION ALL to stack the results.

Show answer

The approach uses conditional aggregation within each SELECT to compute the NULL count for one column, then combines the results:

SELECT 'item_id' AS col_name, COUNT(*) AS total_rows, SUM(CASE WHEN item_id IS NULL THEN 1 ELSE 0 END) AS null_count, ROUND(1.0 * SUM(CASE WHEN item_id IS NULL THEN 1 ELSE 0 END) / COUNT(*), 4) AS null_rate FROM order_items UNION ALL SELECT 'order_id', COUNT(*), SUM(CASE WHEN order_id IS NULL THEN 1 ELSE 0 END), ROUND(1.0 * SUM(CASE WHEN order_id IS NULL THEN 1 ELSE 0 END) / COUNT(*), 4) FROM order_items UNION ALL SELECT 'product_id', COUNT(*), SUM(CASE WHEN product_id IS NULL THEN 1 ELSE 0 END), ROUND(1.0 * SUM(CASE WHEN product_id IS NULL THEN 1 ELSE 0 END) / COUNT(*), 4) FROM order_items UNION ALL SELECT 'quantity', COUNT(*), SUM(CASE WHEN quantity IS NULL THEN 1 ELSE 0 END), ROUND(1.0 * SUM(CASE WHEN quantity IS NULL THEN 1 ELSE 0 END) / COUNT(*), 4) FROM order_items UNION ALL SELECT 'unit_price', COUNT(*), SUM(CASE WHEN unit_price IS NULL THEN 1 ELSE 0 END), ROUND(1.0 * SUM(CASE WHEN unit_price IS NULL THEN 1 ELSE 0 END) / COUNT(*), 4) FROM order_items;

Note that each SELECT scans the table, so this approach performs five scans. For a single-scan alternative, select all conditional aggregates in one SELECT and unpivot the results, but the UNION ALL approach is more readable and sufficient for tables under ~100M rows.

Question 4: APPLIED

An analytics team reports that the average order value (AOV) dropped 15% last month. You suspect NULL values in the amount column might be distorting the AVG function. Write a query to (a) compute the NULL rate for amount over the past 60 days, partitioned by week, and (b) compute AVG(amount) both including and excluding NULLs (treating NULLs as 0) to quantify the distortion.

Show answer

Part (a) uses DATE_TRUNC to bucket by week and computes the NULL rate per bucket:

SELECT DATE_TRUNC('week', created_at) AS week_start, COUNT(*) AS total_orders, SUM(CASE WHEN amount IS NULL THEN 1 ELSE 0 END) AS null_count, ROUND(1.0 * SUM(CASE WHEN amount IS NULL THEN 1 ELSE 0 END) / COUNT(*), 4) AS null_rate FROM orders WHERE created_at >= CURRENT_DATE - INTERVAL '60 days' GROUP BY DATE_TRUNC('week', created_at) ORDER BY week_start;

Part (b) compares the default AVG (which skips NULLs) against an AVG that treats NULLs as 0:

SELECT AVG(amount) AS avg_excl_nulls, AVG(COALESCE(amount, 0)) AS avg_incl_nulls_as_zero, AVG(amount) - AVG(COALESCE(amount, 0)) AS distortion FROM orders WHERE created_at >= CURRENT_DATE - INTERVAL '60 days';

If the distortion is significant, the NULL rate in the amount column has risen, and AVG is computed over a shrinking denominator—inflating the metric if NULLs disproportionately affected low-value orders, or deflating it if the NULL rows would have been high-value orders. The weekly partition in part (a) reveals when the spike occurred, guiding your ETL debugging.

Question 5: CRITICAL THINKING

Design a reusable SQL procedure or CTE-based approach that, given a schema name and table name, dynamically generates and executes NULL-rate checks for every column in that table. Discuss the security implications of dynamic SQL, how you would handle different data types, and how you would extend this to include orphan-key detection for columns with foreign-key metadata available in INFORMATION_SCHEMA.

Show answer

The approach involves querying INFORMATION_SCHEMA.COLUMNS to retrieve all column names for a given table, then constructing a dynamic SQL string with a conditional aggregate for each column. In PostgreSQL, this might look like:

DO $$ DECLARE sql_text TEXT; col RECORD; BEGIN sql_text := 'SELECT COUNT(*) AS total_rows'; FOR col IN SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'orders' LOOP sql_text := sql_text || ', ROUND(1.0 * SUM(CASE WHEN ' || quote_ident(col.column_name) || ' IS NULL THEN 1 ELSE 0 END) / COUNT(*), 4) AS ' || quote_ident(col.column_name || '_null_rate'); END LOOP; sql_text := sql_text || ' FROM public.orders'; EXECUTE sql_text; END $$;

Security: Dynamic SQL introduces SQL injection risk if table or column names are user-supplied. Using quote_ident() sanitizes identifiers. For orphan FK detection, join INFORMATION_SCHEMA.KEY_COLUMN_USAGE with INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS to find FK relationships, then generate LEFT JOIN queries for each FK column. Data type handling is minimal since IS NULL works uniformly across types, but you might add type-specific checks (e.g., empty strings for VARCHAR, zero for numeric). This pattern scales to hundreds of tables in a data warehouse when wrapped in a stored procedure that iterates over all tables and logs results to an audit table.

SUMMARY

Summary — Checking NULL Rates and Missing Keys

Summary

Checking NULL rates is a foundational data quality practice that quantifies missing data at the column level using the formula (COUNT() − COUNT(col)) / COUNT(). Each column must be classified by its semantic intent—intentional, required, soft-required, or foreign key—and assigned an appropriate threshold that distinguishes acceptable missingness from data corruption.

Beyond NULL rates, orphan foreign keys represent a distinct class of data quality failure detectable via LEFT JOIN or NOT EXISTS patterns that expose child rows referencing nonexistent parent records. Together, these techniques form the first line of defense in any data quality strategy, serving as building blocks for advanced data observability frameworks, automated dbt tests, and production data contracts.