How do I find the rows that differ between two databases?

Applies to BroadSQL 5.5.0 · reviewed October 18, 2026

What you'll accomplish

Two databases should hold the same rows: production and QA after a refresh, a source and a target after a migration, two systems kept in sync. A row count that matches proves little, since a missing row and an extra row cancel out. You need the rows themselves: which keys exist on one side only, and which rows have different values.

You will copy the table from each side into one local H2 database with DUMP ... AS H2, then compare the two copies with three ordinary SQL queries. The two sides may be two Environments of one application or two products from different vendors.

Short answer

DUMP both sides AS H2 into the same local database, connect to it, then join the two copies on their key.

Before you start

The example compares the practice connections WORLD (production) and WORLD-QA, which do not exist by default: see Set up QA and production copies of WORLD. With WORLD alone, see the variation "With WORLD alone" below.

To have something to find, make two changes in the QA copy only, as a faulty refresh could: change the population of Gibraltar and remove Monaco. These statements change WORLD-QA, your practice copy:

CONNECT WORLD-QA;
UPDATE COUNTRY SET POPULATION = 30000 WHERE CODE = 'GIB';
DELETE FROM COUNTRY WHERE CODE = 'MCO';

Example

Copy the countries of each side into the local database CHECKS. TRIM removes the trailing spaces of the fixed-length NAME column, so that a name compares on its letters only:

CONNECT WORLD;
DUMP (SELECT CODE, TRIM(NAME) AS NAME, POPULATION FROM COUNTRY) TO CHECKS.PROD_COUNTRY AS H2;
CONNECT WORLD-QA;
DUMP (SELECT CODE, TRIM(NAME) AS NAME, POPULATION FROM COUNTRY) TO CHECKS.QA_COUNTRY AS H2;
CONNECT CHECKS;

CHECKS is created by the first DUMP, as a local H2 database in the export folder, and registered as a connection. Run the three comparison queries there. The first lists the keys found in production but not in QA:

SELECT P.CODE, P.NAME FROM PROD_COUNTRY P
LEFT JOIN QA_COUNTRY Q ON Q.CODE = P.CODE WHERE Q.CODE IS NULL;

It finds Monaco:

CHECKS> SELECT P.CODE, P.NAME FROM PROD_COUNTRY P LEFT JOIN QA_COUNTRY Q ON Q.CODE = P.CODE WHERE Q.CODE IS NULL;
|----|----------------------------------------------------|
|CODE|NAME                                                |
|----|----------------------------------------------------|
|MCO |Monaco                                              |
|----|----------------------------------------------------|

1 rows fetched in 3 ms.

The second query looks the other way: keys found in QA but not in production. Here it returns no row:

SELECT Q.CODE, Q.NAME FROM QA_COUNTRY Q
LEFT JOIN PROD_COUNTRY P ON P.CODE = Q.CODE WHERE P.CODE IS NULL;

The third query lists the keys present on both sides whose values differ:

SELECT P.CODE, P.POPULATION AS PROD_POPULATION, Q.POPULATION AS QA_POPULATION
FROM PROD_COUNTRY P JOIN QA_COUNTRY Q ON Q.CODE = P.CODE
WHERE P.NAME IS DISTINCT FROM Q.NAME OR P.POPULATION IS DISTINCT FROM Q.POPULATION;

It finds Gibraltar, with both values:

CHECKS> SELECT P.CODE, P.POPULATION AS PROD_POPULATION, Q.POPULATION AS QA_POPULATION FROM PROD_COUNTRY P JOIN QA_COUNTRY Q ON Q.CODE = P.CODE WHERE P.NAME IS DISTINCT FROM Q.NAME OR P.POPULATION IS DISTINCT FROM Q.POPULATION;
|----|---------------|-------------|
|CODE|PROD_POPULATION|QA_POPULATION|
|----|---------------|-------------|
|GIB |25000          |30000        |
|----|---------------|-------------|

1 rows fetched in 2 ms.

IS DISTINCT FROM treats two NULL values as equal and a NULL against a value as a difference, which <> does not.

Variations

• With WORLD alone: copy COUNTRY twice from WORLD (TO CHECKS.PROD_COUNTRY and TO CHECKS.QA_COUNTRY), then make the two changes above in CHECKS, on QA_COUNTRY, instead of in a second database. The three queries are the same.
• Two Environments of one application: ENV QA; instead of the second CONNECT.
• Hand the result over: after one of the three queries, DUMP / TO differences AS XLSX;.
• Run it again next month: save the commands as a Script and run it with @; the reconciliation template of the Scripts Gallery is a starting point.

Clean up

Put the QA copy back with UPDATE COUNTRY SET POPULATION = 25000 WHERE CODE = 'GIB'; on WORLD-QA, or replace samples/WorldDB_QA.mv.db with a new copy of the sample while BroadSQL is closed (Monaco then comes back too). CHECKS is a file of the export folder (CHECKS.mv.db) and a connection: DEL CONNECTION CHECKS; makes the connection inactive, then delete the file while BroadSQL is closed.

Watch out

• Each DUMP ... AS H2 replaces the table by default (MODE OVERWRITE), so a new run compares fresh copies.
• The queries need a key column. For a very large table, compare a filtered subset first.
• Every AS H2 export records its source query and row count in BROADSQL_PULL_AUDIT inside CHECKS, so you can show later what was compared.

Related

• Local H2 datasets
• Check that a deployment changed a table the same way in QA and production
• Verify that a data fix changed only the intended rows
• Article: Compare and reconcile data between two databases

Commands used

• DUMP
• CONNECT
• DEL CONNECTION

Found something wrong or unclear on this page? Tell us, with the page address.