How to compare two CSV files and find what changed

You have two exports of the same report, a week apart, and one question: what changed? Eyeballing works for a few dozen rows. A cell-by-cell diff shows which cells differ, but not which records were added or dropped. SQL answers both.

Decide what identifies a row

Every useful comparison starts with a key: the column, or columns, that identify the same record in both files. Usually that’s an order ID or a customer ID. Sometimes it’s a date plus a product code.

Without a key you can tell that something changed, but not what. Pick the key first. Most of the work is right there.

Find rows that exist in only one file

A FULL OUTER JOIN on the key lines up both versions side by side. Whichever side comes back null tells you whether the row was added or removed.

SELECT
  COALESCE(a.order_id, b.order_id) AS order_id,
  CASE
    WHEN a.order_id IS NULL THEN 'added'
    WHEN b.order_id IS NULL THEN 'removed'
    ELSE 'in both'
  END AS status
FROM week_1 a
FULL OUTER JOIN week_2 b
  ON a.order_id = b.order_id
WHERE a.order_id IS NULL OR b.order_id IS NULL;

Find rows that changed

For rows in both files, compare the value columns. Use IS DISTINCT FROM, not <>. Plain inequality returns null when either side is null, so real changes drop out of your results without a trace.

SELECT
  a.order_id,
  a.status   AS old_status,
  b.status   AS new_status,
  a.revenue  AS old_revenue,
  b.revenue  AS new_revenue
FROM week_1 a
JOIN week_2 b
  ON a.order_id = b.order_id
WHERE a.status  IS DISTINCT FROM b.status
   OR a.revenue IS DISTINCT FROM b.revenue;

Get the summary first

Count rows per change type before you read any detail. Three new rows is a normal week. Four thousand removed rows means the export broke.

SELECT
  CASE
    WHEN a.order_id IS NULL THEN 'added'
    WHEN b.order_id IS NULL THEN 'removed'
    WHEN a.revenue IS DISTINCT FROM b.revenue THEN 'changed'
    ELSE 'unchanged'
  END        AS change_type,
  COUNT(*)   AS rows
FROM week_1 a
FULL OUTER JOIN week_2 b
  ON a.order_id = b.order_id
GROUP BY change_type
ORDER BY rows DESC;

Watch for fake differences

Most surprising diffs turn out to be formatting. Trailing spaces, a new date format, or numbers exported as text in one file will all show up as changes.

Normalize inside the comparison. The noise goes away and both source files stay untouched.

SELECT a.order_id
FROM week_1 a
JOIN week_2 b
  ON a.order_id = b.order_id
WHERE TRIM(LOWER(a.customer_name)) IS DISTINCT FROM TRIM(LOWER(b.customer_name));

Skip the SQL entirely

NoEgress has a compare view. Pick two opened tables and a key column, and it shows you the added, removed, changed, and unchanged rows. It runs the same kind of query as above, locally, so neither export gets uploaded.