SQL Server
Why SQL JOINs Create Duplicate Rows
Learn why one-to-many SQL Server joins repeat customer values, how to check join keys, and when aggregation is the right result.
On this page
After learning INNER JOIN and LEFT JOIN, you may see a customer name repeated and think the query returned duplicate data. Often the rows are correct: one customer can have several orders. Before trying to remove repetitions, ask the most useful question in join work: what should one result row represent?
One customer, multiple orders
In JaviBuildsLab, Ana Torres is one row in dbo.Customers, with CustomerId 1. She has three rows in dbo.Orders: order 1001 on 2025-02-01 for 125.50, order 1002 on 2025-04-12 for 49.99, and order 1007 on 2025-07-04 for 75.00. Joining the tables produces three rows for Ana because each row represents a different order.
SELECT
c.CustomerId,
c.FirstName,
c.LastName,
o.OrderId,
o.OrderDate,
o.Amount
FROM dbo.Customers AS c
INNER JOIN dbo.Orders AS o
ON c.CustomerId = o.CustomerId;The result includes Ana three times, once for each of her OrderIds: 1001, 1002, and 1007. David Kim (CustomerId 4) also has two orders, 1004 and 1005. Ben has order 1003, and Grace has orders 1006 and 6. Eight orders produce eight joined rows. This is the expected one-to-many behavior: one customer can relate to many order records.
- Customers: Ana (CustomerId 1) — one row
- Orders: 1001, 1002, 1007 — three matching rows
- JOIN result: Ana + 1001; + 1002; + 1007 — three rows
The diagram is about row relationships, not duplicate storage. Customer attributes repeat because the output combines a customer with each related order. OrderId, OrderDate, and Amount identify different facts. If you selected only the name columns, Ana might appear twice with identical visible values, but those result rows still came from separate orders. Adding a distinguishing order column helps you inspect what the join represents.
Think about relationship cardinality
Cardinality describes how many records can relate. In a one-to-one relationship, one row on one side matches at most one on the other. In a one-to-many relationship, one row can match several rows on the other side; Customers to Orders is this pattern. In a many-to-many relationship, several records on both sides can be related, often through a linking table. The join returns combinations that satisfy its condition, so the relationship shape influences the number of output rows.
Repeated values and duplicate rows are not the same thing. Two joined rows may share CustomerId and name but differ in OrderId. Those are distinct customer-order facts, not accidental duplicates. Determine whether rows are truly identical for the columns and meaning you need, then decide whether the query should show details or summarize them.
Check the join condition
Join on the columns that define the relationship: here c.CustomerId = o.CustomerId. A tempting but wrong condition is to join on State, because both tables have a state-like value in some data models. State is not a unique customer key. If many customers share a state, matching by state pairs unrelated records and multiplies rows. In this demo Orders does not contain State, so do not run that as a real query; the point is that a coincidentally similar or repeated attribute is not a relationship key.
An omitted ON condition can be worse: every customer may pair with every order. Ten customers and eight orders can produce 80 combinations. That Cartesian product is sometimes intentional, but it is rarely the desired result for matching customers and orders. Confirm the keys in the schema and state the relationship explicitly.
DISTINCT is not the default fix
DISTINCT removes identical selected output rows. It does not repair a wrong key or change one-to-many into one-to-one. For example:
SELECT DISTINCT c.CustomerId, c.FirstName, c.LastName
FROM dbo.Customers AS c
INNER JOIN dbo.Orders AS o
ON c.CustomerId = o.CustomerId;This returns one visible row per customer who has an order because OrderId is not selected. That can be legitimate if the explicit question is “which customers have at least one order?” But if you need order details, DISTINCT would hide the separate orders from view. First verify the join condition and desired row meaning. Use DISTINCT only when eliminating identical projected rows is genuinely part of the requested result.
Choose the result grain
“Grain” means what one row represents. The previous query has one row per customer-order pair, which is effectively one row per order because every order belongs to one customer. If the report instead needs one row per customer with a count of orders, aggregate deliberately:
SELECT
c.CustomerId,
c.FirstName,
c.LastName,
COUNT(o.OrderId) AS OrderCount
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o
ON c.CustomerId = o.CustomerId
GROUP BY
c.CustomerId,
c.FirstName,
c.LastName;This gives every customer one row, including those with no orders. COUNT(o.OrderId) returns zero for an unmatched customer because the joined OrderId is NULL and COUNT(column) ignores NULL. COUNT(*) would count the preserved customer row and report one, which is not the order count. GROUP BY changes the grain from customer-order detail to one row per customer. Later lessons cover aggregates in more depth.
Before writing any join, say the intended grain aloud: one row per order, one per customer, or one per customer per order? Then check the keys, relationship cardinality, and selected columns against that expectation. If the row count surprises you, inspect a small set with the identifiers from both sides instead of immediately hiding values.
Diagnose a result that looks repeated
Start by adding the key from each table to a temporary inspection query. For Customers and Orders, keep both c.CustomerId and o.OrderId visible. If the customer ID stays the same while OrderId changes, the query is showing separate order records. If the same pair of identifiers appears multiple times, investigate whether another table was joined at a finer grain, whether the condition is incomplete, or whether the source itself contains repeated records. The identifiers help distinguish expected multiplicity from a genuine join problem.
Next, inspect how many right-side rows are related to one left-side key. Ana provides an easy sample: CustomerId 1 relates to orders 1001, 1002, and 1007. The expected join output for Ana is three rows. David’s two orders provide another check, while Ben adds one and Grace adds two. The remaining six customers do not add rows to an INNER JOIN because they have no orders. Thinking through a few known examples is often faster and clearer than removing repetitions and hoping the totals look plausible.
The same relationship can answer different questions at different grains. For a transaction review, one row per order is useful because each row carries its own date and amount. For an outreach list, one row per customer may be required. A summary can intentionally roll detail rows up, but it must state which customer identifies each group and which measure is being calculated. GROUP BY is not a cosmetic way to hide rows; it defines a new summary result with one row for each distinct grouping key.
Be especially careful when joining more than two tables. If each order has several items and you join Customers to Orders and then OrderItems, one customer can repeat once per item. That does not mean the customer or order was copied in storage. It means the result grain has moved from customer-order to customer-order-item. If you aggregate after such a join, ensure the measure is calculated at the intended level: summing an order total once per item can overstate it. Keep keys that reveal the grain while validating a query, then remove columns only after you know the result is correct.
This lesson stays with result meaning and relationship shape. SQL Server can use different internal strategies to execute a join, but those strategies do not change the logical combinations requested by the query. First get the relationship and grain right; performance topics belong to a separate, more advanced discussion.
You can record the expected grain in a short comment above a query while it is being developed: “one row per order” or “one row per customer.” That note gives reviewers a concrete way to check whether a later join or selected column changes the result. It is particularly useful when a report evolves over time and another person adds a table. If the added table can contribute several rows for each existing row, the grain changes even if the query still runs successfully. Recheck totals and identifiers after each new relationship rather than assuming the earlier row count still applies.
Repeated display values can be especially misleading in a spreadsheet, where a viewer may visually scan names and miss the order identifier at the edge of the grid. Keep the key columns near the front while validating. Once the result is correct, you can choose a presentation that emphasizes the fields readers need, but retain enough columns or documentation to explain why a customer appears more than once.
Common mistakes and practice
Common mistakes include treating repeated customer names as proof of bad data, using DISTINCT immediately, joining on a non-key column, overlooking one-to-many relationships, and failing to define what a row means. Also remember that a LEFT JOIN preserves left-side entities but can still return multiple rows per entity when several right-side rows match.
Practice: (1) show Ana’s order IDs and totals; (2) return one row per customer with the number of orders, retaining customers with none; (3) list each customer who has at least one order without displaying order detail; (4) explain why selecting only FirstName can make different orders look alike; (5) name the grain of the first query in this lesson.
Answers
-- 1
SELECT c.FirstName, o.OrderId, o.Amount
FROM dbo.Customers AS c JOIN dbo.Orders AS o ON c.CustomerId = o.CustomerId
WHERE c.CustomerId = 1;
-- 2
SELECT c.CustomerId, c.FirstName, COUNT(o.OrderId) AS OrderCount
FROM dbo.Customers AS c LEFT JOIN dbo.Orders AS o ON c.CustomerId = o.CustomerId
GROUP BY c.CustomerId, c.FirstName;
-- 3: DISTINCT is appropriate because the question asks for customers, not order rows.
SELECT DISTINCT c.CustomerId, c.FirstName
FROM dbo.Customers AS c JOIN dbo.Orders AS o ON c.CustomerId = o.CustomerId;The next lesson introduces table and column aliases, which make joins with repeated column names easier to read. Return to JOINs Explained for the key relationship, or compare INNER and LEFT JOIN.
The Foundations hub lists the full roadmap.
Check what you learned
2 quick questions
SQL Server Foundations
Lesson 11 of 25
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetFind High CPU QueriesIdentify SQL Server queries consuming the most CPU now and historically, distinguish total CPU from CPU per execution, and choose the right next tuning step.Open cheat sheet
- RoadmapSQL Server FoundationsLearn why one-to-many SQL Server joins repeat customer values, how to check join keys, and when aggregation is the right result.View roadmap
What should I build next?
I'm building Javi Builds around practical problems data engineers actually face. Tell me what you'd like to see next.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.