Compare Two Tables in SQL

Example query

This example assumes: billing_invoices(invoice_id text, amount numeric), ledger_invoices(invoice_id text, amount numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT COALESCE(b.invoice_id, l.invoice_id) AS invoice_id,
       b.amount AS billing_amount,
       l.amount AS ledger_amount,
       CASE
         WHEN b.invoice_id IS NULL THEN 'missing in billing'
         WHEN l.invoice_id IS NULL THEN 'missing in ledger'
         ELSE 'amount differs'
       END AS issue
FROM billing_invoices b
FULL OUTER JOIN ledger_invoices l ON l.invoice_id = b.invoice_id
WHERE b.invoice_id IS NULL
   OR l.invoice_id IS NULL
   OR b.amount IS DISTINCT FROM l.amount
ORDER BY issue, invoice_id;

How it works

• FULL OUTER JOIN keeps rows from both tables, matching on invoice_id and leaving nulls where there is no partner.
• The WHERE clause keeps only the mismatches: missing on either side, or present on both with different amounts.
• IS DISTINCT FROM treats two nulls as equal and a null against a value as different, which is what you want for comparison.

MySQL, BigQuery, SQL Server and SQLite differences

• IS DISTINCT FROM is PostgreSQL, BigQuery and SQL Server 2022+. MySQL uses the null-safe equal operator (NOT (b.amount <=> l.amount)); SQLite uses IS NOT. MySQL has no FULL OUTER JOIN: combine a LEFT JOIN and a RIGHT JOIN with UNION.

Common mistakes

• Duplicate ids in either table multiply the output; check that the key is unique on both sides.
• Amounts that differ by rounding (0.001) are flagged; compare ROUND(amount, 2) if that is acceptable.
• Matching on a text key needs the same case, spacing and formatting in both systems.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

• Which invoices are in billing but missing from the ledger?
• Compare the two invoice tables and show the amounts that do not match.