Course contentsShow
← Data Science
Lesson 941 of 2,14524. SQL JoinsFree lesson

Use Cases: Data Reconciliation

Applying FULL OUTER JOIN to find discrepancies between datasets and audit data completeness.

Use Cases: Data Reconciliation

What you'll learn: How FULL OUTER JOIN helps you spot differences and missing records when comparing two datasets.

Why Data Reconciliation Matters

Imagine you're comparing two inventory lists—one from your warehouse system and one from your accounting software. You need to find items that exist in one system but not the other, or items where the quantities don't match. This is data reconciliation: verifying that two datasets agree and identifying discrepancies.

FULL OUTER JOIN as Your Audit Tool

FULL OUTER JOIN is perfect for reconciliation because it shows you everything from both tables, even when there's no match. You can then identify three critical categories:

  1. Records only in the left table (e.g., products in warehouse but not accounting)
  2. Records only in the right table (e.g., products in accounting but not warehouse)
  3. Matched records with discrepancies (e.g., different quantities for the same product)

Real-World Example Pattern

SELECT 
    w.product_id AS warehouse_id,
    a.product_id AS accounting_id,
    w.quantity AS warehouse_qty,
    a.quantity AS accounting_qty
FROM warehouse w
FULL OUTER JOIN accounting a ON w.product_id = a.product_id
WHERE w.product_id IS NULL          -- Only in accounting
   OR a.product_id IS NULL          -- Only in warehouse
   OR w.quantity != a.quantity;     -- Quantities don't match

This query returns only the problems—records that need investigation. Missing or mismatched data becomes immediately visible for auditing and correction.

Key Takeaway: FULL OUTER JOIN is essential for data reconciliation tasks, revealing missing records on either side and enabling quality checks by exposing all discrepancies between two datasets.