Compare SQL Snapshots: Added, Changed, and Missing Rows

To compare two table snapshots, first prove that each has one non-null row per business key. Then use a full outer join to find keys present on only one side and null-safe comparisons to identify changed values. Keep the differences as a report until you have established why they occurred.

This tutorial uses PostgreSQL 16+ and fictional product snapshots. Every query reads literal sample rows; it creates no tables and changes no data. The output distinguishes after_only, before_only, changed, and unchanged. A missing row is an observation about these extracts, not proof that a business record was deleted.

Choose what the comparison means

Write a short contract before running a diff: which two extracts, source filters, capture times, key, compared attributes, and normalization rules define agreement? Two identical totals can hide offsetting record changes. Two different totals can result from comparing different populations.

Suppose a nightly product extract is compared with yesterday's accepted extract. The stable key is id; amount and note are the attributes of interest. Both extracts must represent the same source scope. If one includes inactive products and the other does not, its missing keys require an explanation before anyone treats them as deletions.

Use one consistent source snapshot or an owner-approved extraction boundary when a source changes during export. A read of page one at 09:00 and page ten at 09:10 can create a combination that never existed. PostgreSQL documents the guarantees and limits of transaction isolation; a single statement and a multi-statement extraction do not necessarily observe the same boundary. Independently operated systems also need a business watermark or batch identifier.

Check the key before joining

Run this complete sample first. It should return zero rows. Any output identifies a null or repeated key; stop the record-level comparison and resolve the intended grain. Replacing this check with DISTINCT can silently discard competing versions.

WITH before_rows(id, amount, note) AS (
  VALUES (101, 10::numeric, 'ok'::text),
         (102, 20, 'ok'), (103, 30, NULL),
         (104, 40, 'ok'), (105, 50, 'ok')
), after_rows(id, amount, note) AS (
  VALUES (101, 10::numeric, 'ok'::text),
         (102, 25, 'ok'), (103, 30, ''),
         (105, 50, 'ok'), (106, 60, 'new')
)
SELECT 'before' AS side, id, count(*) AS occurrences
FROM before_rows GROUP BY id
HAVING id IS NULL OR count(*) > 1
UNION ALL
SELECT 'after', id, count(*)
FROM after_rows GROUP BY id
HAVING id IS NULL OR count(*) > 1;

For real data, replace the two literal CTEs with narrowly scoped reads of your approved before and after snapshots. Keep their batch filters inside each CTE. If an extract contains one row per product and location, use both columns as the key and validate that pair. A product-only join would compare different locations with one another.

Zero rows in the key check proves only the key condition. Also validate extract completeness, source filters, and expected volume. An empty current extract can pass uniqueness while still representing a failed job.

Build the full outer join diff

The following query includes the same runnable inputs. It returns six keys because the union of the two five-row snapshots contains six distinct IDs.

WITH before_rows(id, amount, note) AS (
  VALUES (101, 10::numeric, 'ok'::text),
         (102, 20, 'ok'), (103, 30, NULL),
         (104, 40, 'ok'), (105, 50, 'ok')
), after_rows(id, amount, note) AS (
  VALUES (101, 10::numeric, 'ok'::text),
         (102, 25, 'ok'), (103, 30, ''),
         (105, 50, 'ok'), (106, 60, 'new')
), diff AS (
  SELECT coalesce(b.id, a.id) AS id,
         CASE
           WHEN b.id IS NULL THEN 'after_only'
           WHEN a.id IS NULL THEN 'before_only'
           WHEN b.amount IS DISTINCT FROM a.amount
             OR b.note IS DISTINCT FROM a.note THEN 'changed'
           ELSE 'unchanged'
         END AS change_type,
         b.amount AS before_amount, a.amount AS after_amount,
         b.note AS before_note, a.note AS after_note
  FROM before_rows b
  FULL OUTER JOIN after_rows a ON a.id = b.id
)
SELECT * FROM diff ORDER BY id;

A full outer join retains keys from both sides. The missing-side checks use the validated non-null key, not a nullable business attribute. Null amounts therefore cannot make an existing product look absent. PostgreSQL describes these join semantics in joined tables.

Compare NULL without hiding real changes

An ordinary inequality such as b.note <> a.note does not become true when one side is NULL. Its result is unknown. IS DISTINCT FROM gives the diff a definite true or false result: two nulls agree, and a null differs from a non-null value. See PostgreSQL's comparison predicates.

Do not replace every null with an empty string before comparing. In this example, NULL means a note was not supplied, while an empty string is an explicitly empty note. A business owner could decide they are equivalent, but that is a separate normalization rule. Keep the raw attributes and show which rule changed the comparison.

Compare deliberately selected typed columns. Comparing timestamps formatted in two different zones, rounded display strings, or JSON rendered with inconsistent formatting can create misleading differences. For exact money-like demonstration values, this example uses numeric. Approximate measurements need an explicit tolerance, units, and null policy rather than an arbitrary rounding fix.

Reconcile counts and values

The sample has two unchanged keys, two changed keys, one before-only key, and one after-only key. Verify both row identities:

• Before rows: 2 unchanged + 2 changed + 1 before-only = 5.
• After rows: 2 unchanged + 2 changed + 1 after-only = 5.
• Union of keys: 2 + 2 + 1 + 1 = 6.

The before total is 150 and the after total is 175. Its bridge is 150 minus 40 for key 104, plus 60 for key 106, plus 5 for the amount change on key 102. Key 103 changes its note but contributes zero to the amount difference. The resulting total is 175; a sum-only check would miss the note change entirely.

For your own report, group the classified result by change_type, count rows, and retain the underlying key list. Reconcile the original snapshot totals separately. Report null amount counts alongside totals because SUM ignores null inputs; a plausible total does not establish complete values.

Turn the result into an investigation

Start with a small approved partition, such as one completed batch or business day. Select only the key and compared attributes. Evaluate the execution plan before scaling to large extracts; indexing, data distribution, and join strategy depend on your actual system. No timing claim for the tiny fixture predicts production performance.

Keep reconciliation separate from synchronization

A diff is evidence for a decision. It does not establish that the current extract is authoritative, nor that every absent key should be deleted from a downstream table. A production synchronization process additionally needs completeness checks, ownership, transaction boundaries, concurrency rules, recovery, and an explicit deletion policy.

Archive the extract identifiers, contract, key-check result, classified counts, amount bridge, and exceptional keys. Record whether the comparison was complete or sampled. The SQL expected-results guide helps turn this small fixture into repeatable checks; the recurring report workflow covers delivery controls. If the disagreement starts at the metric definition rather than the rows, use the metric reconciliation playbook.

FAQ

Why not use EXCEPT for everything?

Set differences are useful for identifying unequal rows, but a keyed diff explains whether a record appeared, disappeared, or changed. Plain set semantics can also hide duplicate multiplicity. Validate the grain first and choose the comparison output that answers your investigation.

Can I compare two databases directly with this query?

The example assumes both snapshots are available to the same query engine. Cross-system access depends on approved connectors or extracts. Preserve original types and a common batch boundary when staging copies; do not assume separate databases can be joined using ordinary table names.

What happens when both snapshots are empty?

The diff has no keys. That can mean agreement or a shared extraction failure. Check the expected population and completion receipts before declaring success. Likewise, an empty current snapshot makes all earlier keys before-only; it must not automatically trigger deletion.