Compare tables in SQL and check data differences across matching, missing, and changed rows.

SQL Compare: Find Matching, Missing, and Changed Rows

Start with a stable, unique key on both sides. To compare data in SQL, join the two tables on that key, then classify each result as matching, missing, or changed. The example below uses PostgreSQL and treats two null values in the same field as equal for comparison.

SQL Data Compare Using a Full Join

Suppose two snapshots contain product IDs, names, and prices. The primary key makes each ID unique and non-null in its table:

CREATE TEMP TABLE old_products (
  id integer PRIMARY KEY, name text, price numeric(8, 2)
);
CREATE TEMP TABLE new_products (
  id integer PRIMARY KEY, name text, price numeric(8, 2)
);

INSERT INTO old_products VALUES
  (1, 'Pen', 2.00), (2, 'Pad', 5.00),
  (3, 'Ruler', NULL), (4, 'Clip', 1.00);
INSERT INTO new_products VALUES
  (1, 'Pen', 2.00), (2, 'Pad', 6.00),
  (3, 'Ruler', NULL), (5, 'Tape', 3.00);

An SQL data compare query can preserve rows from both snapshots with FULL OUTER JOIN. Classify missing IDs before comparing the other fields:

SELECT COALESCE(o.id, n.id) AS id,
       CASE
         WHEN o.id IS NULL THEN 'only in new'
         WHEN n.id IS NULL THEN 'only in old'
         WHEN o.name IS DISTINCT FROM n.name
           OR o.price IS DISTINCT FROM n.price THEN 'changed'
         ELSE 'matching'
       END AS status
FROM old_products AS o
FULL OUTER JOIN new_products AS n ON o.id = n.id
ORDER BY id;

The expected result is:

id status
1 matching
2 changed
3 matching
4 only in old
5 only in new

The status describes only the columns named in the query. ID 2 changed because its price rose from 5 to 6. ID 3 matches under the stated null policy: both prices are null. IDs 4 and 5 have no partner on the other side. An SQL compare query can expose the old and new values as additional selected columns when a reviewer needs to see exactly what changed.

Choose a Key and Define Equality

Use an identifier that represents the same entity in both snapshots and is unique and non-null on each side. If IDs are reused or regenerated between exports, matching numbers may refer to different products. If the natural key spans several columns, join on all of them and enforce or check their combined uniqueness. Duplicate keys otherwise create multiple joined pairs and make counts misleading.

For a comparison of SQL values, ordinary = and <> return unknown when either operand is NULL. PostgreSQL’s IS DISTINCT FROM returns false for two nulls and true when just one side is null. That is a deliberate policy here: two unknown prices are treated as equivalent for the report, not as proof that the real prices are equal. Choose another rule if your application requires missing values to be reviewed separately.

Text collation, numeric precision, timestamps, and normalization also affect whether values compare as expected. Decide whether whitespace, case, or rounding differences matter before changing the query. Add every business-relevant column to the changed test; an omitted column cannot affect the status. If the tables contain audit timestamps that change on every export, decide whether those fields belong in the comparison. Excluding them may make the report more useful, but document that exclusion for its readers.

Check the Scope Before Acting

A full join reports keys present on either side, but it does not infer renames or identify which field changed unless you select or label those values. Set operations such as EXCEPT can reveal different projected rows, but their default distinct behavior removes duplicates and does not pair changed rows by entity.

A comparison in SQL is most reliable when both inputs represent the intended point in time. Compare stable snapshots or use an appropriate transaction strategy so concurrent updates do not distort the result. Before using a SQL comparison to generate updates, review the differences and test on a copy. If rows multiply unexpectedly, look for duplicate join keys. If a changed value is reported as matching, check whether that column was included and whether the null and normalization rules match the intended comparison.