Validating Data with SQL — Ranges, Required Fields and the NULL Trap

Published Jun 30, 2026 · Updated Jul 2, 2026
by

A range check that runs green is no proof of clean data. Anyone who writes WHERE age < 0 OR age > 120 to find implausible ages silently misses every row where age has no value at all — because in SQL, a comparison with NULL is neither true nor false, but unknown. That very missing required value later breaks the load into the strictly modeled target. To validate data with SQL therefore means: not just checking value ranges, but cleanly separating validity from completeness — and knowing three-valued logic before it swallows rows.

The key points up front:

  • A WHERE clause that describes the bad rows is the single most powerful check — and covers validity (ranges, formats, plausibility) and completeness (required fields).
  • The NULL trap: NULL values fall out of both a condition and its negation — a missing value is neither “good” nor “bad”, it vanishes from both sets.
  • Validity (is the value allowed?) and completeness (is there a value at all?) are two distinct dimensions — and belong in two separate checks, not one.
  • Hardcoded, none of these checks is practical: the rules live in a configuration table, from which the runner of the framework article assembles the SQL generically.

Prerequisite: Postgres as the example engine and the shared checking scaffold from the framework article — a central error table dq.error into which every violation writes one row, and a staging layer to check against. This article deepens the first of the three routines from there: the WHERE clause.

Contents

The WHERE Clause as a Validity Check

To validate data with SQL, you almost always start here. The principle is simple: you formulate a condition that describes the bad rows, attach it to the table — and everything the condition matches is a finding. For the rule “age must be between 0 and 120” it looks like this:

  1: INSERT INTO dq.error (schema_name, table_name, id1_column, id1_value,
  2:                       error_column, error_value, severity, message)
  3: SELECT
  4:     'staging'
  5:    ,'customer'
  6:    ,'customer_id'
  7:    ,T01.customer_id::text
  8:    ,'age'
  9:    ,T01.age::text
 10:    ,'E'
 11:    ,'Age outside 0..120'
 12: FROM
 13:    staging.customer T01
 14: WHERE
 15:    T01.age < 0 OR T01.age > 120;

The predicate on line 15 defines what “invalid” means. This single form covers a whole family of validity rules: value ranges (age < 0 OR age > 120), plausibility (order_date > current_date), fixed value lists (country_code NOT IN ('DE', 'AT', 'CH')). That is the validity dimension — do the values match the allowed rules?

And this is exactly where the mistake almost everyone makes once is waiting.

The NULL Trap: Why Three-Valued Logic Swallows Rows

SQL knows not two truth values but three: true, false, and unknown. Every comparison with NULL yields unknown — and WHERE only lets through rows whose condition is true. A row with age IS NULL therefore does not match age < 0 OR age > 120: the record with the missing age is not flagged as a finding.

It gets worse when you think the negation puts you on the safe side. “Good” and “bad” together should surely yield every row:

  1: -- "good": age within the allowed range
  2: SELECT customer_id FROM staging.customer
  3: WHERE
  4:    age BETWEEN 0 AND 120;          -- 1, 5
  5:
  6: -- "bad": simply negated
  7: SELECT customer_id FROM staging.customer
  8: WHERE
  9:    NOT (age BETWEEN 0 AND 120);    -- 2, 3

Both queries together return customers 1, 2, 3, 5 — customer 4 with age IS NULL appears in neither. NOT (unknown) is again unknown, not true. The missing value drops out of the “good” set and the “bad” set and disappears silently. A validation that relies on “everything that isn’t valid” lets missing required values slip through unchecked.

The lesson: to validate data with SQL, you must handle NULL explicitly — IS NULL and IS NOT NULL are not optional but mandatory. Never assume a negation will also catch the missing value.

Validity Is Not Completeness

The clean solution is to ask the two questions separately. “Is the value missing?” is completeness, “is the existing value allowed?” is validity — often with different severity:

  1: -- Completeness: required field missing (its own finding)
  2: INSERT INTO dq.error (...)
  3: SELECT ..., 'age', NULL, 'E', 'Age missing (required field)'
  4: FROM   staging.customer T01
  5: WHERE
  6:    T01.age IS NULL;
  7:
  8: -- Validity: range, explicitly only for existing values
  9: INSERT INTO dq.error (...)
 10: SELECT ..., 'age', T01.age::text, 'E', 'Age outside 0..120'
 11: FROM   staging.customer T01
 12: WHERE
 13:        T01.age IS NOT NULL
 14:    AND (T01.age < 0 OR T01.age > 120);

Now customer 4 gets its own entry “Age missing”, and the value range is only checked where a value exists. The IS NOT NULL on line 13 is technically redundant — the comparison would exclude NULL anyway — but it makes the intent visible: this check is responsible for validity, not completeness. Whether the missing value is an error (E) or just a warning (W) depends on whether the column is NOT NULL at the target.

Formats, Lengths, Patterns

The same WHERE routine covers format and pattern checks. In Postgres the regex operator ~ (matches) or !~ (does not match) does the job; each check again deliberately excludes NULL:

  1: -- ZIP must be 5 numeric digits
  2: WHERE
  3:        T01.zip IS NOT NULL
  4:    AND T01.zip !~ '^[0-9]{5}$';
  5:
  6: -- Birth date must not be in the future
  7: WHERE
  8:    T01.birth_date > current_date;

This handles lengths (length(zip) <> 5), simple patterns (email contains an @), value lists, and date plausibility. The WHERE clause is for simple syntactic checks, not for real specifications — a full RFC-compliant email validation does not belong in it. For numbers-from-text, field-level type conversion is the more robust route; it is the fine-grained validity check covered in detail by the cluster around TRY_CONVERT and the type-conversion basics.

Rules Across Multiple Fields

A WHERE clause can also check relationships between columns of a single row — “discount only if status is active”, “end date not before start date”:

  1: WHERE
  2:        T01.discount  > 0
  3:    AND T01.status   <> 'active';

Here too the NULL rule applies: if status is NULL, status <> 'active' is unknown and the row slips through. Cross-field business rules can sometimes be expressed this way, sometimes not — as soon as they need aggregates, time series, or external references, the limit of the single-table check is reached. Orphaned foreign keys, for instance, are a separate topic — referential integrity.

Performance: the Full Scan and Its Limits

Let’s be honest: each of these checks is at heart a full table scan. A freely formulated predicate like age < 0 OR age > 120 cannot use an ordinary index — the database has to look at every row. How much that weighs, though, depends less on the check itself than on the amount of data it runs against.

The type of load decides. In an initial load the entire dataset moves through staging — millions of rows, every rule a full scan: this is the case where performance really matters. If the ETL process runs as a delta or incremental load, only the changed records arrive per run. Then you check a handful to a few thousand rows instead of the whole table — the full scan over this small set is negligible, and in ongoing operation the performance question practically takes care of itself.

The lever, then, is to limit the check per batch to the freshly loaded set instead of scanning the entire table every time. A staging table may well hold the data of several batches — a load marker (such as a load_id or batch_id column) confines each check to the current batch, so already-processed rows aren’t scanned again. That leaves only the initial load as a real bottleneck; there a functional or partial index helps additionally when the same validity rule runs often and selectively (CREATE INDEX … ON staging.customer (age) WHERE age < 0 OR age > 120). For an arbitrary predicate, however, there is no index guarantee — that belongs in the effort estimate.

Generic via the Config Routine

Hardcoded, none of these checks is usable. They become generic by putting the predicate not in code but in a configuration row — one row per rule, which the runner from the framework article translates at runtime into an INSERT INTO dq.error … SELECT … WHERE:

  1: INSERT INTO dq.check_rule
  2:    (check_type, schema_name, table_name, id1_column, check_column, where_clause, severity, message)
  3: VALUES
  4:     ('constraint', 'staging', 'customer', 'customer_id', 'age', 'age IS NULL'                               , 'E', 'Age missing (required field)')
  5:    ,('constraint', 'staging', 'customer', 'customer_id', 'age', 'age IS NOT NULL AND (age < 0 OR age > 120)', 'E', 'Age outside 0..120'          );

The runner inserts schema and column names via format() with %I (injection-safe), but the predicate from where_clause via %s — as unchanged SQL. It has to be that way, because where_clause is a SQL expression, not a value. That makes the dq.check_rule table the trust boundary: whoever may write there can have arbitrary SQL executed — uncritical as long as the configuration is maintained administratively and never filled from user input. The full runner mechanics are in the framework article.

Bridge: the Same in SQL Server

The pattern is not Postgres-specific. In SQL Server, sp_executesql takes the role of EXECUTE format(), and QUOTENAME() protects identifiers instead of %I. One pitfall is different: for “is this a number?” people like to reach for ISNUMERIC() — but that also reports currency and exponential notation as numeric:

  1: SELECT ISNUMERIC('$1,000');          -- 1  (wrongly "numeric")
  2: SELECT ISNUMERIC('1e4');             -- 1  (wrongly "numeric")
  3: SELECT TRY_CONVERT(int, '$1,000');   -- NULL  (cleanly rejected)
  4: SELECT TRY_CONVERT(int, '1e4');      -- NULL

TRY_CONVERT is the reliable route: it returns NULL when a value can’t be converted, instead of wrongly waving it through. Structure, error table, severity gate, and the NULL trap stay identical in SQL Server — only the dialect tools differ.

FAQ

Why doesn’t my WHERE check find the NULL values?

Because every comparison with NULL in SQL yields not false but unknown — and WHERE only lets through rows whose condition is true. A row with a missing value matches neither the condition nor its negation. Missing values must be caught with IS NULL as a separate check.

Validity vs. completeness — what’s the difference?

Completeness asks: is there a value at all? (age IS NULL). Validity asks: is the existing value allowed? (age < 0 OR age > 120). These are two data-quality dimensions and two separate checks — often with different severity: a missing required value as an error, a format deviation perhaps only as a warning.

Can I index range checks?

Only to a limited extent. A free predicate uses no ordinary index — the check is a full scan. What matters most, though, is the volume: in a delta or incremental load you only check the few freshly loaded rows, and the performance question barely arises. It becomes noticeable mainly in the initial load over the entire dataset — there a partial index helps (CREATE INDEX … WHERE <predicate>), and in general a load marker confines the check to the current batch instead of scanning the whole table every time.

How does this relate to TRY_CONVERT?

Safe type conversion is the field-level validity check: “can this text be read as a date/number?” TRY_CONVERT (SQL Server) or a cast with error handling (Postgres) returns NULL instead of a runtime error when the value doesn’t fit — more robust than a regex format check for numbers and dates.

Does this work in SQL Server too?

Yes. sp_executesql replaces EXECUTE format(), QUOTENAME() replaces %I, and instead of the ISNUMERIC trap you use TRY_CONVERT. Three-valued logic, and therefore the NULL trap, applies in SQL Server just the same.