Find missing branch records with careful data preparation and LEFT JOIN
INNER JOIN drops unmatched branches. A data-quality audit often needs to keep missing records visible, so join type must follow the question.
Why unmatched rows matter
A branch directory may contain one hundred locations while the daily sales table has rows for only some of them. INNER JOIN returns matched combinations and hides locations without a sales record. A branch with genuinely zero sales must be distinguished from a branch whose integration failed to deliver data. Treating both as zero can distort operational decisions.
Expose missing records with LEFT JOIN
Start from the Branches table and look for Sales entries for a particular date. LEFT JOIN retains the branches and allows unmatched Sales.BranchId values to appear as NULL. Filtering the right-hand table in WHERE can inadvertently remove unmatched rows; a date predicate in the ON condition is often more appropriate for this audit.
SELECT b.BranchId, s.Amount FROM Branches b LEFT JOIN Sales s ON s.BranchId=b.BranchId AND s.SalesDate=@Day;Row multiplication and validation
When a branch has several sales records, the join creates several result rows. Use COUNT(DISTINCT b.BranchId) when counting branches rather than assuming COUNT(*) is the number of locations. Include a branch with no sales, a branch with two transactions and an invalid BranchId in test data. Check duplicate transactions by stable transaction identifiers.
2021 context and today's verification
This newly written article revisits data preparation questions relevant to 2021; it does not invent an earlier publication. Compare report totals before and after changing a cleaning rule. Pass missing-data status into downstream BI or machine-learning systems instead of silently replacing every NULL with zero. A missing value is not proof of no activity.
Comments
No comments yet. Be the first to contribute.
✎ Write a comment