Common SQL Join Mistakes That Quietly Duplicate Your Rows

The Problem: Your Count Just Doubled (And You Don’t Know Why)

Ever written a SQL query that felt right, only to look at the results and realize something is off?

You expected 1,000 rows—maybe one per customer—but the database handed you 2,000. Or 5,000. Or 10,000.

This is one of the most common frustrations in data analysis. It’s not a syntax error. The database doesn’t throw a warning. It just quietly multiplies your rows, turning a simple report into a mess. If your query calculates total revenue and your rows have doubled, your revenue comes out 2x higher than reality. That’s the difference between a good quarter and a reporting disaster.

Let’s run a straightforward query on some retail data and see what happens.

import pandas as pd
import sqlite3

# Let's set up a quick in-memory database
conn = sqlite3.connect(':memory:')

# Create an orders table
orders = pd.DataFrame({
    'order_id': [101, 102],
    'customer_name': ['Alice', 'Bob'],
    'total_amount': [50.00, 75.00]
})

# Create an order_items table (Alice bought 2 things, Bob bought 2 things)
items = pd.DataFrame({
    'item_id': [1, 2, 3, 4],
    'order_id': [101, 101, 102, 102],
    'product': ['Apple', 'Banana', 'Cherry', 'Date']
})

orders.to_sql('orders', conn, index=False)
items.to_sql('items', conn, index=False)

# The naive query: We just want to see orders with their items
query = """
SELECT 
    o.order_id, 
    o.total_amount,
    i.product
FROM orders o
JOIN items i ON o.order_id = i.order_id
"""

result = pd.read_sql(query, conn)
print(f"Original Orders Count: {len(orders)}")
print(f"Resulting Rows: {len(result)}")
print(result)

What happened? We started with 2 orders but ended up with 4 rows. If we tried to SUM(total_amount) on this result, we’d get $250 instead of the actual $125. The join didn’t just combine data. It multiplied it.

How Joins Actually Work: Cartesian Products Under the Hood

To fix this, we have to change our mental model. We often think of a JOIN as a way to “lookup” information, like a VLOOKUP in Excel. But in SQL, a join is fundamentally a multiplication operation.

When you join Table A to Table B, SQL looks at every row in Table A. For each row, it finds every matching row in Table B. If one row in Table A matches three rows in Table B, SQL creates three rows in your output.

This is a Cartesian Product of the matching subsets.

What this actually means: Joins don’t filter your data unless nothing matches. Their default behavior is to expand. A one-to-many relationship will multiply your row count, and the join type — INNER or LEFT — doesn’t change that.

Mistake #1: Joining on a One-to-Many Without Realizing It

This is the most frequent culprit. You join Customers to Orders and think, “I just want the customer’s email next to their order.”

A customer can have many orders, though. Join from the Customer table to the Order table and you get one row for every order that customer ever placed. Join that result to Order_Items and you get one row for every item in every order.

# Let's add a third layer: order_items to categories
categories = pd.DataFrame({
    'product': ['Apple', 'Apple', 'Banana', 'Cherry', 'Date'],  # Oops! Apple has a duplicate category entry
    'category': ['Fruit', 'Snack', 'Fruit', 'Fruit', 'Fruit']
})
categories.to_sql('categories', conn, index=False)

multi_join_query = """
SELECT o.order_id, i.product, c.category
FROM orders o
JOIN items i ON o.order_id = i.order_id
JOIN categories c ON i.product = c.product
"""

explosion = pd.read_sql(multi_join_query, conn)
print(f"Row count after joining categories: {len(explosion)}")

The row count keeps growing at every step — 2 orders become 4 rows after the items join, and 5 rows after the categories join, because categories now covers all four products and still has that duplicate Apple entry. This is why GROUP BY or DISTINCT end up used as panic buttons — but they just paper over a deeper misunderstanding of the data relationship.

Mistake #2: Joining to a Table With Duplicates You Didn’t Know About

Sometimes the logic is fine but the data is dirty. Say your Product_Catalog table should have one row per product ID, but an ETL error inserted “Product 501” twice.

Join that to your Sales table and every sale of Product 501 doubles.

How to spot it: Check your reference table before joining:

SELECT join_key, COUNT(*)
FROM reference_table
GROUP BY join_key
HAVING COUNT(*) > 1;

If this returns anything, your join will duplicate rows.

Mistake #3: Joining on Non-Unique Keys (The Silent Killer)

This is the hardest part of causal debugging in SQL. You join on last_name, thinking it’s unique enough for your small test set. Then you run it against the full database, and “Smith” matches 5,000 people.

Even columns that look like IDs can be non-unique. Joining on zip_code or store_id might seem safe, until you realize those IDs are shared across regions or time periods.

Mistake #4: Multiple Joins Creating Compounding Multiplication

Multiplication compounds. If the first join doubles your rows (2x) and the second triples each of those (3x), you’re at 6x your starting data.

In a complex query with 5 or 6 joins, a single one-to-many relationship early in the chain can turn 100 rows into 100,000 by the end. Now your data is wrong, and your query is slow.

Spotting the Mistake: How to Tell If Your Join Is Duplicating Rows

Before you trust your results, run this 30-second diagnostic. Compare the count of your primary key to the count of distinct primary keys.

diagnostic_query = """
SELECT 
    COUNT(*) as total_rows,
    COUNT(DISTINCT order_id) as unique_orders
FROM (
    SELECT o.order_id
    FROM orders o
    JOIN items i ON o.order_id = i.order_id
)
"""
check = pd.read_sql(diagnostic_query, conn)
print(check)

If total_rows is 4 and unique_orders is 2, you’ve got a 2x duplication.

Fix #1: Use DISTINCT to Remove Duplicates (The Quick Fix)

SELECT DISTINCT is the last resort. It tells SQL: after all the joining and multiplying, look at the final rows and collapse any that are identical.

It works, but it’s expensive. The database has to sort the full result set to find duplicates. Use it only when you truly have identical rows you can’t avoid through better logic.

Fix #2: Use GROUP BY to Aggregate Before Joining

This is the standard fix for most duplication. If you need data from a “many” table (like Order_Items) to live in a “one” table (like Orders), aggregate it first.

Join to a summary of items, not the raw rows.

fix_query = """
SELECT 
    o.order_id, 
    o.total_amount,
    item_summary.item_count
FROM orders o
JOIN (
    SELECT order_id, COUNT(*) as item_count 
    FROM items 
    GROUP BY order_id
) item_summary ON o.order_id = item_summary.order_id
"""
print(pd.read_sql(fix_query, conn))

Now we have 2 rows for 2 orders. The count is correct because we turned the “many” into a “one” before the join.

Fix #3: Use Subqueries or CTEs to Control Join Order

Common Table Expressions (CTEs) keep your logic readable. You define your “clean” tables up front in the query, so you’re not wrestling duplicates inside a 50-line block of code.

cte_query = """
WITH clean_items AS (
    SELECT order_id, COUNT(*) as total_items
    FROM items
    GROUP BY order_id
)
SELECT o.order_id, o.total_amount, ci.total_items
FROM orders o
JOIN clean_items ci ON o.order_id = ci.order_id
"""
print(pd.read_sql(cte_query, conn))

Fix #4: Use Window Functions to Rank and Filter Rows

What if you want the most recent item for each order, without aggregating?

ROW_NUMBER() ranks rows within a group so you can keep just the top one.

window_query = """
WITH ranked_items AS (
    SELECT 
        order_id, 
        product, 
        ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY item_id DESC) as rn
    FROM items
)
SELECT order_id, product
FROM ranked_items
WHERE rn = 1
"""
print(pd.read_sql(window_query, conn))

One row per order. The specific item we asked for, no duplication.

Putting It Together: A Real-World Example

Say you’re asked for “Total Revenue per Category.”

1. Naive approach: Join Orders -> Items -> Categories. Run SUM(total_amount) over the result and you get $300 — not because of one bug, but two compounding ones: Categories now has 5 rows for 4 products (Apple shows up twice, mapped to both “Fruit” and “Snack”), and Items already puts one row on the board per item, so every order’s revenue gets repeated once per item it contains.
2. Diagnostic: The naive query’s total reads $300. Your orders table — the actual source of truth — sums to $125. Running GROUP BY product HAVING COUNT(*) > 1 on categories returns Apple, confirming one of the two causes.
3. The Fix: Deduplicating categories with a CTE fixes the category-duplication half — the query drops to 4 rows and $250 — but that’s still 2x too high, because Items is still putting one row on the board per item and total_amount is order-level, not item-level. The rest of the fix is Fix #2’s pattern: aggregate items down to one row per order before joining to orders. Do that and the query lands on exactly $125 — the original two orders, dollar for dollar.

How to Prevent Join Mistakes in the Future

• Check counts early: Run SELECT COUNT(*) after each new join you add.
• Know your keys: Don’t assume a column is unique. Check it with GROUP BY...HAVING COUNT(*) > 1.
• Visualize the relationship: Is it 1:1, 1:N, or N:N? With 1:N, if you want one row back, you must aggregate or filter.

Recap: What You Now Know

• Joins are multiplication, not just lookups.
• One-to-many relationships are the #1 cause of “ghost” rows.
• DISTINCT is correct when the extra rows are genuinely identical; the moment they differ in any column (like category), GROUP BY or a CTE is the only fix that actually works.
• Always validate your row counts against your source tables.

Next time, we’ll look at making these joins fast — not just correct.

Check Your Understanding

Questions below move from simple recall up to open-ended design, roughly following Bloom’s Taxonomy.

Remember What is the “30-second diagnostic” the article recommends for checking whether a join duplicated rows?

Understand In your own words, explain why the article says a JOIN is fundamentally a “multiplication operation” rather than a “lookup,” using the orders/items example (2 orders becoming 4 rows).

Apply Using the article’s example, if orders has 2 rows and each order has 3 matching rows in items, and you then join that result to a categories table where each product matches 2 category rows, how many total rows would you expect in the final result?

Analyze The article distinguishes Mistake #1 (joining a genuine one-to-many relationship) from Mistake #2 (joining to a reference table with accidental duplicate rows). Walk through why the diagnostic query in Mistake #2 (GROUP BY join_key HAVING COUNT(*) > 1 on the reference table) wouldn’t catch Mistake #1 — what’s fundamentally different about the two situations?

Evaluate The article calls SELECT DISTINCT “the last resort” and recommends GROUP BY/aggregation as the sturdier fix. Critique this as a blanket rule: is there a scenario where reaching for DISTINCT is actually the more correct choice, not just the lazier one?

Create Design a diagnostic and fix for a new scenario: a report joining Employees to Department_History (which has one row per department change, so an employee has multiple rows if they changed departments) is asked to show “each employee’s current department.” Using the article’s Fix #4 pattern (window functions), sketch the query logic you’d use to get exactly one row per employee.

---

Apply What You Learned

You ship and maintain a feature-serving pipeline that joins `orders → items → categories` to produce "total revenue per category" features for a production churn model. The ML team just flagged that revenue features are running significantly too high — same symptom the article describes (\$300 reported vs \$125 actual). The service is live and the next training pull is in two hours. Find the silent duplication, prove it, and write the fix.

Deliverable: An incident report (150-250 words) containing: (1) which table is causing the duplication and why, (2) the exact diagnostic SQL that proves it — both the GROUP BY … HAVING COUNT(*) > 1 check on the culprit table and the COUNT(*) vs COUNT(DISTINCT order_id) row-count comparison, (3) the corrected query using a CTE or subquery to fix the problem before the join, and (4) why a blanket SELECT DISTINCT would fail here.

Rubric (checklist):

• Names categories as the duplication source — the product column has duplicate entries mapping ‘Apple’ to both ‘Fruit’ and ‘Snack’ (non-identical rows, not a copy-paste dupe)
• Runs GROUP BY product HAVING COUNT(*) > 1 on categories and reports it returns rows
• Runs the 30-second diagnostic (COUNT(*) vs COUNT(DISTINCT order_id)) and reports the 2× inflation ratio (e.g., 4 total rows vs 2 unique orders)
• Writes a fix using a CTE or subquery that deduplicates or aggregates before the join — not SELECT DISTINCT applied after
• Explains that DISTINCT on the final result fails because the duplicated rows carry different category values (‘Fruit’ vs ‘Snack’), so they aren’t identical rows
• Confirms the fixed query’s row count matches the original orders count (2 rows for 2 orders, not 4)