Compare two tables in SQL for differences: three queries that work

#The two tables

Everything below runs against these two tables: one day of sales per store, from the tills and from the ERP. Run this in a scratch database first, so you can check each query's result against the one shown. Drop the dbo. and the same script runs in PostgreSQL.

CREATE TABLE dbo.till_sales (
  store         varchar(10)   NOT NULL,
  business_date date          NOT NULL,
  net_sales     decimal(12,2) NOT NULL,
  units         int           NOT NULL,
  tax_code      varchar(10)   NULL,
  CONSTRAINT pk_till_sales PRIMARY KEY (store, business_date)
);

CREATE TABLE dbo.erp_sales (
  store         varchar(10)   NOT NULL,
  business_date date          NOT NULL,
  net_sales     decimal(12,2) NOT NULL,
  units         int           NOT NULL,
  tax_code      varchar(10)   NULL,
  CONSTRAINT pk_erp_sales PRIMARY KEY (store, business_date)
);

INSERT INTO dbo.till_sales VALUES
  ('ST-004', '2026-11-02',  9850.20, 412, 'STD'),
  ('ST-011', '2026-11-02',  7301.40, 288, 'STD'),
  ('ST-014', '2026-11-02', 12400.00, 530, 'STD'),
  ('ST-022', '2026-11-02',  6410.75, 251, 'STD'),
  ('ST-031', '2026-11-02',  8120.00, 344, 'STD'),
  ('ST-037', '2026-11-02',  5214.60, 199, 'STD');

INSERT INTO dbo.erp_sales VALUES
  ('ST-004', '2026-11-02',  9850.20, 412, 'STD'),
  ('ST-011', '2026-11-02',  7301.40, 288, NULL),
  ('ST-014', '2026-11-02', 11920.00, 512, 'STD'),
  ('ST-031', '2026-11-02',  8119.96, 344, 'STD'),
  ('ST-037', '2026-11-02',  5214.60, 199, 'STD'),
  ('ST-047', '2026-11-02',  2392.30,  96, 'STD');

Hidden in there: ST-014 is 480.00 short in the ERP, ST-022 never arrived, ST-047 is in the ERP only, ST-031 is 0.04 out by rounding, and ST-011 lost its tax code. A good comparison finds all five and tells them apart.

#1. EXCEPT in both directions

EXCEPT returns the rows of the first query that the second query does not return. Run it both ways, because each direction only sees one side.

SELECT 'only in tills' AS side, *
FROM (
  SELECT store, business_date, net_sales, units, tax_code FROM dbo.till_sales
  EXCEPT
  SELECT store, business_date, net_sales, units, tax_code FROM dbo.erp_sales
) AS a
UNION ALL
SELECT 'only in ERP' AS side, *
FROM (
  SELECT store, business_date, net_sales, units, tax_code FROM dbo.erp_sales
  EXCEPT
  SELECT store, business_date, net_sales, units, tax_code FROM dbo.till_sales
) AS b
ORDER BY store, side;

It returns eight rows: ST-011, ST-014 and ST-031 twice each, once per side, plus ST-022 only in tills and ST-047 only in the ERP.

Three things to know, all from Microsoft's page on EXCEPT:

• NULLs count as equal. When comparing rows, two NULLs are treated as the same value. That is why ST-011 shows up: 'STD' and NULL differ, and EXCEPT says so.
• Duplicates collapse. EXCEPT returns distinct rows. A row sent twice to the ERP looks the same as a row sent once. PostgreSQL has EXCEPT ALL to keep them; SQL Server does not.
• Columns line up by position, not by name, and must be of compatible types. xml, text, ntext and image columns cannot be compared at all.

Where it stops. EXCEPT tells you a row is not identical. It does not tell you whether the row is missing or changed, which column changed, or whether 0.04 matters. A changed row appears twice, and with thousands of rows you are left pairing them up by eye. That is the join's job.

#2. FULL OUTER JOIN on the key

Join the two tables on the key that names a row, here store and date. A full outer join keeps the rows found on one side only, and a CASE names what happened to each.

SELECT COALESCE(t.store, e.store)                 AS store,
       COALESCE(t.business_date, e.business_date) AS business_date,
       t.net_sales                                AS till_net,
       e.net_sales                                AS erp_net,
       t.net_sales - e.net_sales                  AS difference,
       CASE
         WHEN e.store IS NULL THEN 'Missing in ERP'
         WHEN t.store IS NULL THEN 'Missing at tills'
         WHEN ABS(t.net_sales - e.net_sales) > 0.05 THEN 'Net differs'
         WHEN EXISTS (SELECT t.units, t.tax_code
                      EXCEPT
                      SELECT e.units, e.tax_code) THEN 'Other column differs'
         WHEN t.net_sales <> e.net_sales THEN 'Rounding, within 0.05'
         ELSE 'Matches'
       END AS result
FROM dbo.till_sales AS t
FULL OUTER JOIN dbo.erp_sales AS e
  ON  e.store = t.store
  AND e.business_date = t.business_date
WHERE t.business_date = '2026-11-02'
   OR e.business_date = '2026-11-02'
ORDER BY COALESCE(t.store, e.store);

Two details make this query honest.

The date filter goes in WHERE with an OR. Filter only t.business_date and you throw away every row that exists only in the ERP, because its t side is NULL. That quietly turns the full outer join back into a left join.

The NULL-safe comparison. The obvious WHEN t.tax_code <> e.tax_code misses ST-011. 'STD' <> NULL is neither true nor false, it is unknown, so the WHEN never fires. The EXISTS (SELECT … EXCEPT SELECT …) line borrows EXCEPT's rule that two NULLs are equal, and it works on any supported version. On SQL Server 2022 and Azure SQL you can write t.tax_code IS DISTINCT FROM e.tax_code instead, which Microsoft documents as always returning true or false, even when one side is NULL.

The tolerance is yours to pick. Five cents here, for one store's takings for a day. Put each measure's tolerance in its own WHEN line, and keep the rounding line after the other checks so a real difference is never filed as rounding.

#3. One hash per row

When a table has forty columns, naming every one in a CASE gets old. Turn each row into one hash and compare the hashes instead.

WITH t AS (
  SELECT store, business_date,
         HASHBYTES('SHA2_256', CONCAT(
           COALESCE(CONVERT(varchar(30), net_sales), '~'), '|',
           COALESCE(CONVERT(varchar(30), units), '~'), '|',
           COALESCE(tax_code, '~'))) AS row_hash
  FROM dbo.till_sales
),
e AS (
  SELECT store, business_date,
         HASHBYTES('SHA2_256', CONCAT(
           COALESCE(CONVERT(varchar(30), net_sales), '~'), '|',
           COALESCE(CONVERT(varchar(30), units), '~'), '|',
           COALESCE(tax_code, '~'))) AS row_hash
  FROM dbo.erp_sales
)
SELECT COALESCE(t.store, e.store) AS store,
       CASE
         WHEN e.row_hash IS NULL THEN 'Missing in ERP'
         WHEN t.row_hash IS NULL THEN 'Missing at tills'
         ELSE 'Differs'
       END AS result
FROM t
FULL OUTER JOIN e
  ON  e.store = t.store
  AND e.business_date = t.business_date
WHERE t.row_hash IS NULL
   OR e.row_hash IS NULL
   OR t.row_hash <> e.row_hash
ORDER BY COALESCE(t.store, e.store);

It returns ST-011, ST-014, ST-022, ST-031 and ST-047.

Why not CONCAT_WS? It looks made for this. Microsoft's page says CONCAT_WS ignores NULL values and adds no separator for them. So CONCAT_WS('|', 'A', NULL, 'B') and CONCAT_WS('|', 'A', 'B', NULL) both give A|B: two different rows, one hash. Wrapping every column in COALESCE(…, '~'), as above, keeps each value in its place. Pick a marker that never appears in your data.

Also know:

• Use SHA2_256 or SHA2_512. Microsoft deprecated MD5 and SHA1 in HASHBYTES from SQL Server 2016. On SQL Server 2014 and earlier, the input is limited to 8,000 bytes.
• A hash has no tolerance. ST-031's 0.04 differs, because 8120.00 and 8119.96 are different text. Use the hash to find which rows changed, then run the join from section 2 on just those.
• Same text, same hash. varchar and nvarchar versions of the same value hash differently, and so do 12.4 and 12.40. Convert both sides the same way.

#The same in PostgreSQL

EXCEPT is written the same way, and PostgreSQL adds EXCEPT ALL to keep duplicates. The join can compare several columns at once with IS DISTINCT FROM:

SELECT COALESCE(t.store, e.store) AS store,
       t.net_sales AS till_net,
       e.net_sales AS erp_net,
       CASE
         WHEN e.store IS NULL THEN 'Missing in ERP'
         WHEN t.store IS NULL THEN 'Missing at tills'
         WHEN abs(t.net_sales - e.net_sales) > 0.05 THEN 'Net differs'
         WHEN (t.units, t.tax_code) IS DISTINCT FROM (e.units, e.tax_code)
           THEN 'Other column differs'
         WHEN t.net_sales <> e.net_sales THEN 'Rounding, within 0.05'
         ELSE 'Matches'
       END AS result
FROM till_sales AS t
FULL OUTER JOIN erp_sales AS e
  ON  e.store = t.store
  AND e.business_date = t.business_date
ORDER BY 1;

For a hash, PostgreSQL can turn a whole row into text: md5(t::text) over FROM till_sales AS t hashes every column, with an empty slot where a value is NULL, so positions survive. concat_ws has the same NULL-skipping trap as in SQL Server, per PostgreSQL's documentation.

#The worked example

Run the join from section 2 against the two tables and you get this:

Invented figures. Seven rows, five results, and each one sends you somewhere different: ST-014 to the ERP's rejected lines, ST-022 to the interface that should have carried it, ST-047 to the store's till upload, ST-011 to the tax mapping. ST-031 can be left alone. Totals alone would have shown a gap of 4,498.49 and hidden all of that.

#When the tables are on two servers

The queries above assume both tables are in one database. They rarely are: the tills write to one server, the ERP to another.

A linked server. Pull the one day you need into a temporary table, then compare locally. OPENQUERY sends its query to the other server as written, so only that day travels:

SELECT *
INTO #erp
FROM OPENQUERY(ERPSRV,
  'SELECT store, business_date, net_sales, units, tax_code
   FROM erp_db.dbo.erp_sales
   WHERE business_date = ''2026-11-02''');

Then use #erp in place of dbo.erp_sales. Don't run EXCEPT straight across the link: Microsoft says EXCEPT and INTERSECT in distributed queries run only on the local server and are not pushed to the linked server.

tablediff. Microsoft's tablediff utility compares a table on one SQL Server with a table on another, column by column with -c, and -f writes a T-SQL script to bring the destination into line. It ships with the replication tools, the source table needs a primary key, identity or ROWGUID column, and it does not work with servers other than SQL Server. Read the fix script before running it: it changes the destination.

& "C:\Program Files\Microsoft SQL Server\160\COM\tablediff.exe" `
  -sourceserver TILLSRV -sourcedatabase tills -sourcetable till_sales `
  -destinationserver ERPSRV -destinationdatabase erp_db -destinationtable erp_sales `
  -c -o .\tablediff_result.txt

Two engines. When the tills are on SQL Server and the ERP on Oracle or PostgreSQL, the hash method breaks: each engine writes numbers, dates and text differently, so equal rows hash differently. Compare values, not hashes, after converting both sides to the same types. Moving both into one place is what comparing two CSV files covers.

#Where doing it by hand runs out

The queries find the five rows. They don't run themselves tomorrow at six, don't remember that ST-031 is always 0.04 out, and don't tell you why ST-014 is 480 short. Someone still opens the ERP's error log, finds the lines refused for a missing tax code, and phones the store.

DataMosaic does the comparison as a check: it reads SQL Server, Oracle and PostgreSQL, lines the two sides up on shared keys, and shows matched, differing and missing rows, with rules for what counts as a failure. On a differing row, an investigation walks through the reasons a system can disagree, such as a rejected record or data that hasn't arrived yet. A check only reads. Mosaic changes a system only through a workflow you build, such as sending the batch again and running the check once more.

For the techniques behind all three methods, from row counts to grain, see data reconciliation: techniques and a worked retail example.

#Questions people ask

#How do I compare two tables in SQL Server for differences?

Run EXCEPT both ways to find rows that are not identical, then a FULL OUTER JOIN on the key to label each one as missing on one side or different. Compare nullable columns with a NULL-safe test, not <>.

#How do I find rows in one table that are not in another?

SELECT key FROM a EXCEPT SELECT key FROM b lists keys in a missing from b. NOT EXISTS with a correlated subquery does the same and lets you return the other columns too.

#Why does my comparison miss rows with NULL values?

Because NULL = NULL and 'A' <> NULL are both unknown, not true, so the row is filtered out. Use EXCEPT, IS DISTINCT FROM (SQL Server 2022 and PostgreSQL), or the EXISTS (SELECT … EXCEPT SELECT …) pattern.

#Can I compare two tables on different SQL Servers?

Yes. Pull the rows you need through a linked server with OPENQUERY into a temporary table and compare locally, or use Microsoft's tablediff utility, which works between SQL Servers only.

#Is HASHBYTES good for comparing tables?

For wide tables, yes, with SHA2_256 and every column converted the same way on both sides. Hashes are exact, so a rounding difference shows as a changed row.