TRENDING

TECHNOLOGY NEWS FROM AROUND THE WORLD

→
Find missing branch records with careful data preparation and LEFT JOIN
Artificial Intelligence · 2021
Artificial Intelligence2021

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.

Yazılımcının GünlüğüPublished: Nov 16, 20263 min read

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.

0 reads

Comments

No comments yet. Be the first to contribute.

✎ Write a comment

Your comment appears after submission; spam and duplicate submissions are blocked.