Row-Level Diffing in SQL: Finding Exactly Which Columns Changed

Column-by-column diffing with CASE

SELECT
  n.customer_id,
  CASE WHEN o.email IS DISTINCT FROM n.email
       THEN CONCAT('email: ', o.email, ' -> ', n.email) END AS email_change,
  CASE WHEN o.status IS DISTINCT FROM n.status
       THEN CONCAT('status: ', o.status, ' -> ', n.status) END AS status_change,
  CASE WHEN o.phone IS DISTINCT FROM n.phone
       THEN CONCAT('phone: ', o.phone, ' -> ', n.phone) END AS phone_change
FROM customers_old_snapshot o
JOIN customers n ON o.customer_id = n.customer_id
WHERE o.email IS DISTINCT FROM n.email
   OR o.status IS DISTINCT FROM n.status
   OR o.phone IS DISTINCT FROM n.phone;

Each CASE produces a NULL for unchanged columns and a readable before/after string for changed ones — a compact per-row change summary without needing separate queries per column.

Row hashing for wide tables

-- Step 1: cheap hash comparison to find which rows changed at all
SELECT n.customer_id
FROM customers_old_snapshot o
JOIN customers n ON o.customer_id = n.customer_id
WHERE MD5(CONCAT_WS('|', o.email, o.status, o.phone, o.address))
   != MD5(CONCAT_WS('|', n.email, n.status, n.phone, n.address));

-- Step 2: run the full column-by-column diff only on the small set of ids from step 1

This two-step pattern is much cheaper on tables with dozens of columns — most rows haven't changed at all, and the hash comparison rules them out in a single pass before the more expensive column-level breakdown runs on only the handful that actually differ.

Handling NULLs correctly

A plain != comparison never evaluates to true when either side is NULL, which means a change from a real value to NULL (or vice versa) would be silently missed by naive diffing logic. IS DISTINCT FROM treats two NULLs as equal and a NULL-vs-value pair as different — exactly the behavior needed for correct diffing.

Real-world example: a silently-changed customer address

A support ticket claims a customer's shipping address was "changed without their knowledge." Running the column-by-column diff between yesterday's snapshot and today's live table for that customer_id shows the address field changed, but also that updated_by changed from the customer's own user ID to an internal support-tool service account ID at the same timestamp. That combination points to an internal address-correction tool making the change, not unauthorized account access — the row-level diff resolved the question definitively instead of leaving it as a guess.

Building a lightweight audit trail

Storing the output of a row-level diff — which column changed, the old value, the new value, and a timestamp — into a dedicated change-log table turns a one-off diff into a queryable history. This is a lighter-weight alternative to a full CDC setup (see Change Data Capture (CDC) Explained) when only a handful of sensitive columns on a few tables genuinely need change tracking.

Common mistakes

• Using != instead of a NULL-safe comparison, missing changes that involve a NULL on either side.
• Comparing timestamp columns with mismatched precision (milliseconds vs seconds) and flagging false positives.
• Running a full column-by-column diff on every row of a huge table instead of hash-filtering first.
• Not normalizing whitespace or casing before comparing text columns, flagging cosmetic differences as real changes.