Reconcile distributed financial ledgers, audit department headcounts, and eliminate two-sided relational blindspots with FULL OUTER JOIN and COALESCE key unification.
Open in Present mode · 400 practice questions · Live class schedule · Try the playground
Jo class mein bola, yahan nukte mein likha hai.
What I said in class, written here as key points.
A four-phase operational blueprint for ledger reconciliation, symmetric difference, and bidirectional integrity audits.
FULL OUTER JOIN is the cornerstone of reconciliation architecture.Transitioning from one-way absence detection to comprehensive two-sided reconciliation
o.order_id) without COALESCE — doing so turns valid right-only rows into nameless NULLs.SELECT COALESCE(o.order_id, p.order_id) AS id, ROUND(COALESCE(o.net_total, 0), 2) AS bill, ROUND(COALESCE(p.amount, 0), 2) AS paid, ROUND(COALESCE(p.amount, 0) - COALESCE(o.net_total, 0), 2) AS diff, CASE WHEN o.order_id IS NULL THEN 'Orphan' WHEN p.payment_id IS NULL THEN 'Unpaid' ELSE 'Discrepancy' END AS status FROM sales.orders o FULL OUTER JOIN sales.payments p ON o.order_id = p.order_id WHERE COALESCE(o.net_total, 0) != COALESCE(p.amount, 0) ORDER BY 4 DESC LIMIT 10; -- Full 2-sided audit with COALESCE
FULL OUTER JOIN with the non-negotiable COALESCE key pattern to surface unbilled receipts and unpaid invoices in a single query!How relational algebra constructs a complete bidirectional envelope across disjoint systems.
(A ∩ B) ∪ (A − B) ∪ (B − A). Every record from both tables survives.IS NULL on both primary keys, SQL classifies each row into Reconciled, Unpaid, or Orphan Gateway Payment.Click join perspectives below to visualize the bidirectional envelope and record preservation.
Constructing complete relational envelopes across financial ledgers in PostgreSQL 18.
FROM table_a FULL OUTER JOIN table_b ON a.id = b.a_id guarantees neither table suffers row loss.IS NOT NULL checks to classify each output row into business reconciliation categories.SELECT
COALESCE(o.order_id, p.order_id) AS reconciled_order_id,
o.net_total AS order_amount,
p.amount AS paid_amount,
CASE
WHEN o.order_id IS NOT NULL AND p.payment_id IS NOT NULL THEN 'Fully Reconciled'
WHEN o.order_id IS NOT NULL AND p.payment_id IS NULL THEN 'Pending Payment'
ELSE 'Orphan Gateway Payment'
END AS reconciliation_status
FROM sales.orders o
FULL OUTER JOIN sales.payments p
ON o.order_id = p.order_id
ORDER BY reconciled_order_id DESC
LIMIT 15;
Click below to observe why selecting a single join key produces dangerous NULL identifiers.
SELECT o.order_id yields NULL for all right-only orphan records!COALESCE(o.order_id, p.order_id) returns whichever ID is non-NULL.Why selecting raw table keys in FULL OUTER JOIN causes data corruption and how COALESCE fixes it.
Table A.id evaluates to NULL. Downstream grouping and dashboards fail immediately.COALESCE(left.pk, right.foreign_key) AS unified_id. It evaluates arguments in order, returning the first non-null value.SELECT o.order_id AS raw_order_id, s.order_id AS raw_shipment_order_id, COALESCE(o.order_id, s.order_id) AS unified_order_id, o.order_status, s.courier_name, s.shipped_date FROM sales.orders o FULL OUTER JOIN sales.shipments s ON o.order_id = s.order_id WHERE o.order_id >= 149990 ORDER BY unified_order_id DESC LIMIT 15;
Click transaction classifications below to observe ledger balance calculations and variance detection.
received_amount != billed_amount.Calculating exact rupee variances across mismatched transactions and unpaid invoices in RetailMart.
p.amount - o.net_total yields NULL! Wrap amounts in COALESCE(col, 0) before subtracting.WHERE COALESCE(o.net_total, 0) != COALESCE(p.amount, 0) to eliminate clean matches and isolate balance errors.SELECT
COALESCE(o.order_id, p.order_id) AS order_id,
ROUND(COALESCE(o.net_total, 0), 2) AS billed_rupees,
ROUND(COALESCE(p.amount, 0), 2) AS received_rupees,
ROUND(COALESCE(p.amount, 0) - COALESCE(o.net_total, 0), 2) AS variance_rupees,
CASE
WHEN o.order_id IS NULL THEN 'Orphan Gateway Payment'
WHEN p.payment_id IS NULL THEN 'Completely Unpaid'
WHEN p.amount < o.net_total THEN 'Underpaid Order'
WHEN p.amount > o.net_total THEN 'Overpaid Order'
ELSE 'Exact Match'
END AS reconciliation_status
FROM sales.orders o
FULL OUTER JOIN sales.payments p
ON o.order_id = p.order_id
WHERE COALESCE(o.net_total, 0) != COALESCE(p.amount, 0)
ORDER BY ABS(COALESCE(p.amount, 0) - COALESCE(o.net_total, 0)) DESC, order_id DESC
LIMIT 15;
Click below to trace department vacancies, unallocated personnel, and rogue headcount records.
Filtering for rows that exist in either System A or System B, but never in both simultaneously.
(A Δ B) = (A − B) ∪ (B − A). It excises matching records to show only pure gaps.WHERE table_a.pk IS NULL OR table_b.pk IS NULL. All matched rows vanish immediately.SELECT
COALESCE(o.order_id, s.order_id) AS audit_order_id,
o.order_status,
s.courier_name,
s.status AS shipment_status,
CASE
WHEN o.order_id IS NOT NULL AND s.shipment_id IS NOT NULL THEN 'Fulfilled & Shipped'
WHEN o.order_id IS NOT NULL AND s.shipment_id IS NULL THEN 'Unshipped / Cancelled'
ELSE 'Orphan Courier Tracking'
END AS fulfillment_audit
FROM sales.orders o
FULL OUTER JOIN sales.shipments s
ON o.order_id = s.order_id
WHERE o.order_id IS NULL OR s.shipment_id IS NULL
ORDER BY audit_order_id DESC
LIMIT 15;
Avoid these four classic traps when conducting two-sided reconciliation audits.
SELECT o.order_id emits NULL for all right-only orphan records! Always write COALESCE(o.order_id, p.order_id) to guarantee identity.WHERE p.amount > 1000 destroys the FULL OUTER JOIN! Right-only or left-only NULL amounts fail the filter, silently demoting to a RIGHT JOIN.p.amount - o.net_total yields NULL whenever an order is unpaid or orphan! Always wrap numbers: COALESCE(p.amount, 0) - COALESCE(o.net_total, 0).WHERE a.id IS NULL OR b.id IS NULL. Writing AND returns zero rows because no row can be missing on both sides!Write and execute two-sided reconciliation audits in PostgreSQL 18 across 4 difficulty tiers.
Authentic technical interview questions asked by senior engineering panels at top tech firms.
Click each card to reveal the underlying architectural answer and test your retention.
The four core architectural laws governing two-sided relational audits.
FULL OUTER JOIN preserves 100% of rows from both tables, exposing matches, left-only gaps, and right-only orphans in one unified pass.COALESCE(left.pk, right.foreign_key) to guarantee every audit record displays an identifier.ON clause. Placing right-table criteria in WHERE demotes the query into a RIGHT JOIN.WHERE a.id IS NULL OR b.id IS NULL to discard matched records and extract pure discrepancies for automated compliance alerts.Production SQL templates for two-sided reconciliation, COALESCE keys, and discrepancy audits.
SELECT COALESCE(o.order_id, p.order_id) AS reconciled_id, o.net_total, p.amount FROM sales.orders o FULL OUTER JOIN sales.payments p ON o.order_id = p.order_id ORDER BY reconciled_id DESC LIMIT 10;
SELECT
COALESCE(o.order_id, p.order_id) AS order_id,
CASE
WHEN o.order_id IS NOT NULL AND p.payment_id IS NOT NULL THEN 'Reconciled'
WHEN o.order_id IS NOT NULL THEN 'Unpaid'
ELSE 'Orphan Payment'
END AS status
FROM sales.orders o
FULL OUTER JOIN sales.payments p ON o.order_id = p.order_id
ORDER BY order_id DESC LIMIT 10;
SELECT COALESCE(o.order_id, s.order_id) AS audit_order_id, o.order_status, s.courier_name FROM sales.orders o FULL OUTER JOIN sales.shipments s ON o.order_id = s.order_id WHERE o.order_id IS NULL OR s.shipment_id IS NULL ORDER BY audit_order_id DESC LIMIT 10;
SELECT COALESCE(o.order_id, p.order_id) AS order_id, ROUND(COALESCE(p.amount, 0) - COALESCE(o.net_total, 0), 2) AS variance FROM sales.orders o FULL OUTER JOIN sales.payments p ON o.order_id = p.order_id WHERE COALESCE(o.net_total, 0) != COALESCE(p.amount, 0) ORDER BY ABS(COALESCE(p.amount, 0) - COALESCE(o.net_total, 0)) DESC, order_id DESC LIMIT 10;
Three enterprise reconciliation challenges to test your mastery of FULL OUTER JOIN.
sales.orders and sales.payments. Filter for unpaid orders where payment is missing. Calculate total unpaid rupee revenue.sales.orders against sales.shipments with symmetric difference: WHERE o.order_id IS NULL OR s.shipment_id IS NULL.core.dim_department and stores.employees with FULL OUTER JOIN. Group by unified department key and report total staff count and average salary.You have mastered single-sided and two-sided relational joining. Next: Cartesian matrices.
Generating combinatorial grids, matrix reports, and exhaustive test combinations in PostgreSQL 18.
SELECT c.category_name, s.store_name FROM core.dim_category c CROSS JOIN stores.stores s WHERE s.store_id <= 3 AND c.category_id <= 4 ORDER BY c.category_name ASC, s.store_name ASC LIMIT 12;