SQL Boo-Boos #3: Why My Row Count Doubled After a Join (And Why That’s Way More Dangerous Than You…

2026-05-16 · Source: Data Engineering on Medium · Field: Technology & Digital — Data Science & Analytics, Software Development & Engineering · Depth: Intermediate, medium

Summary

This article addresses a common and dangerous SQL error: the one-to-many join duplicate, which silently inflates aggregated results without throwing errors. It illustrates how joining a `customers` table (one row per customer) with an `orders` table (many rows per customer) correctly produces multiple rows per customer in the joined result. However, the "boo-boo" occurs when a second "many" table, like `customer_scores` (multiple scores per customer), is introduced. This creates an intermediate result where each customer's order rows are multiplied by their score rows, leading to drastically inflated sums, such as doubling Jane Doe's actual revenue from 700 to 1,400. The article emphasizes that SQL faithfully executes instructions, not intentions, and this multiplication of rows before aggregation is the root cause of incorrect reports.

Key takeaway

For Data Analysts and Data Scientists building revenue or customer reports, always verify the grain of your tables before joining. If a child table has multiple rows per key, pre-aggregate it to a single row per key using `GROUP BY` or `ROW_NUMBER()` to prevent silent data inflation. Your reports will be accurate, and you will avoid embarrassing discrepancies with finance.

Key insights

One-to-many joins can silently inflate aggregated results, leading to critical reporting errors.

Principles

• SQL joins connect rows, not tables.
• Understand table grain before joining.
• Aggregate child tables before joining to parents.

Method

To prevent join duplicates, aggregate child tables (e.g., `orders`, `customer_scores`) to the parent's grain (e.g., one row per `customer_id`) using `GROUP BY` or `ROW_NUMBER()` before performing the final join.

In practice

• Use `ROW_NUMBER()` to select a single row from a "many" table.
• Filter on `is_current` flags for single-row selection.
• Sanity check `COUNT(*)` per key before joining.

Topics

• SQL Joins
• Data Duplication
• One-to-Many Relationships
• Data Aggregation
• Common Table Expressions

Best for: Data Analyst, Data Scientist, Analytics Engineer

Related on AIssential

• The 8-Point SQL Checklist That Distinguishes Junior Analysts from Senior Engineers — Accurate SQL querying demands a systematic pre-execution audit covering data grain, filtering, joins, NULL handling, and aggregate validation.
• Detecting Join Duplication — Silent join duplication corrupts data pipelines; systematic auditing of join keys, row counts, and match coverage is essential.
• Window Functions: ROW_NUMBER vs RANK vs DENSE_RANK (Three Functions, One Confusion — Fixed in 5… — SQL window functions `ROW_NUMBER`, `RANK`, and `DENSE_RANK` differ primarily in how they handle ties and number skipping.
• Debunking 8 data layout myths: why Liquid Clustering outperforms partitioning — Liquid Clustering provides a flexible, performant, and scalable data layout for modern Lakehouses, overcoming partitioning's limitations.
• MySQL 9.6 Changes Foreign Key Constraints and Cascade Handling — MySQL 9.6 moves foreign key cascade handling to the SQL layer, improving data consistency for replication.
• A Set-Theoretic Approach to Detecting Logic Bugs in DBMS Inner Join Optimizations — INNER/NATURAL JOINs are semantically equivalent to set intersection operations, forming a basis for logic bug detection.

Open in AIssential →

Editorial summary, takeaway, and curation by AIssential. Original article published by Data Engineering on Medium.