Reconciliation queries

Reconciliation compares two lists that should match, like orders and payments, and flags every difference: missing on one side, or different amounts.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Do two systems agree?

SELECT COALESCE(o.order_id, p.order_id) AS order_id, o.revenue, p.amount,
       CASE WHEN o.order_id IS NULL THEN 'missing order' WHEN p.order_id IS NULL THEN 'missing payment' WHEN o.revenue <> p.amount THEN 'mismatch' ELSE 'ok' END AS status
FROM orders o FULL OUTER JOIN payments p ON o.order_id = p.order_id

Result:

order_id	revenue	amount	status
O1	120	120	ok
O2	80	75	mismatch
O3	50		missing payment
O4	200	200	ok
O5		60	missing order

Step by step

FULL OUTER JOIN keeps orders with no payment, and payments with no order.

SELECT o.order_id, o.revenue, p.order_id, p.amount
FROM orders o FULL OUTER JOIN payments p ON o.order_id = p.order_id;

COALESCE takes whichever side has the id.

SELECT COALESCE(o.order_id, p.order_id) AS order_id, o.revenue, p.amount
FROM orders o FULL OUTER JOIN payments p ON o.order_id = p.order_id;

CASE says what's wrong: missing order, missing payment, or a mismatch.

SELECT COALESCE(o.order_id, p.order_id) AS order_id, o.revenue, p.amount,
       CASE WHEN o.order_id IS NULL THEN 'missing order' WHEN p.order_id IS NULL THEN 'missing payment' WHEN o.revenue <> p.amount THEN 'mismatch' ELSE 'ok' END AS status
FROM orders o FULL OUTER JOIN payments p ON o.order_id = p.order_id;

Practice

Try each one in your head first, then open the answer.

1. Show the order_id of orders with no matching payment.

SELECT o.order_id FROM orders o LEFT JOIN payments p ON o.order_id = p.order_id WHERE p.order_id IS NULL

2. Show order_id, revenue and amount where the payment doesn't match the order's revenue.

SELECT o.order_id, o.revenue, p.amount FROM orders o JOIN payments p ON o.order_id = p.order_id WHERE o.revenue <> p.amount

3. Show the order_id of payments with no matching order.

SELECT p.order_id FROM payments p LEFT JOIN orders o ON p.order_id = o.order_id WHERE o.order_id IS NULL

4. Using a FULL OUTER JOIN, show order_id (from either side, with COALESCE), revenue and amount for every order and payment.

SELECT COALESCE(o.order_id, p.order_id) AS order_id, o.revenue, p.amount FROM orders o FULL OUTER JOIN payments p ON o.order_id = p.order_id

5. Full reconciliation: show order_id, revenue, amount and a status: 'missing order', 'missing payment', 'mismatch' or 'ok'.

SELECT COALESCE(o.order_id, p.order_id) AS order_id, o.revenue, p.amount, CASE WHEN o.order_id IS NULL THEN 'missing order' WHEN p.order_id IS NULL THEN 'missing payment' WHEN o.revenue <> p.amount THEN 'mismatch' ELSE 'ok' END AS status FROM orders o FULL OUTER JOIN payments p ON o.order_id = p.order_id

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed