Join Duplicates: Stop Hiding Extra Rows With SELECT DISTINCT

Seeing the same order twice does not always mean the data is wrong. Join duplicates can reflect a real one-to-many relationship, but they can also inflate a total. Decide the intended result grain before reaching for DISTINCT.

Define One Row of the Result

Start by completing this sentence: one output row represents one what? An order, a payment, and an order-payment combination are different answers. Joining two tables does not preserve the row count of the first table. It returns the matching combinations requested by the join condition.

I write the result grain beside the query before changing it. That gives the review something concrete to test. A repeated order identifier is valid in a payment detail report. It is a warning when the report claims one row per order. DISTINCT cannot decide which report the business actually needs.

Create these temporary tables in one SSMS session. The payment table deliberately allows several payments for an order. Its primary key identifies a payment rather than enforcing one payment per order. The sample values make the relationship easy to inspect without relying on a large database.

CREATE TABLE #Orders
(OrderID int PRIMARY KEY, OrderAmount decimal(12,2) NOT NULL);
CREATE TABLE #Payments
(PaymentID int PRIMARY KEY, OrderID int NOT NULL,
 PaymentAmount decimal(12,2) NOT NULL);
INSERT #Orders VALUES (1,100),(2,200),(3,300);
INSERT #Payments VALUES (11,1,40),(12,1,60),(21,2,200);
SELECT o.OrderID, o.OrderAmount, p.PaymentID, p.PaymentAmount
FROM #Orders o
JOIN #Payments p ON p.OrderID = o.OrderID
ORDER BY o.OrderID, p.PaymentID;

Find Where Join Duplicates Start

The join returns the order amount once for each matching payment. Adding those repeated amounts treats payment count as an order multiplier. The problem is arithmetic applied at the wrong grain, not the existence of several legitimate payments.

SELECT SUM(o.OrderAmount) AS JoinedOrderTotal
FROM #Orders o
JOIN #Payments p ON p.OrderID = o.OrderID;
SELECT o.OrderID, COUNT_BIG(*) AS MatchingPayments
FROM #Orders o
JOIN #Payments p ON p.OrderID = o.OrderID
GROUP BY o.OrderID
HAVING COUNT_BIG(*) > 1;

Read the first result against the source order amounts. Then inspect the orders returned by the second query. They identify where one-to-many matches occur. Extend that check through a longer join chain one relationship at a time. Adding a second detail table can multiply combinations again.

Also inspect the join columns. A missing key component can create accidental matches even when each source table is valid. Joining only CustomerID when the relationship also requires RegionID is a different problem from legitimate multiple payments. Fix the relationship before deciding how to summarize it.

Prevent Join Duplicates by Aggregating First

For one row per order, summarize the payment detail to the order grain first. The derived table now supplies at most one row for each OrderID. A LEFT JOIN preserves orders without payments. COALESCE presents their missing payment total as zero under this report's chosen business rule.

SELECT o.OrderID, o.OrderAmount,
       COALESCE(p.PaidAmount,0) AS PaidAmount
FROM #Orders o
LEFT JOIN
(
    SELECT OrderID, SUM(PaymentAmount) AS PaidAmount
    FROM #Payments GROUP BY OrderID
) p ON p.OrderID = o.OrderID
ORDER BY o.OrderID;

The order amount appears once because the matching payment side is unique at the join key. That is a structural guarantee from GROUP BY, not a cleanup performed afterward. Test both paid and unpaid orders before relying on the total.

If the application needs payment count, earliest payment time, or latest payment time, calculate them in the same grouped result. Keep each aggregate's meaning explicit. A latest date does not automatically identify which payment supplied another selected column. That requires a separate ranking or selection rule.

Use APPLY for a Correlated Summary

APPLY is another way to express an order-specific calculation. An aggregate without GROUP BY returns a row even when no payments match. Its SUM is NULL in that case. The outer SELECT again applies the report's zero presentation rule.

SELECT o.OrderID, o.OrderAmount,
       COALESCE(p.PaidAmount,0) AS PaidAmount
FROM #Orders o
CROSS APPLY
(
    SELECT SUM(PaymentAmount) AS PaidAmount
    FROM #Payments p WHERE p.OrderID = o.OrderID
) p
ORDER BY o.OrderID;

Do not assume APPLY is always faster or always slower than the derived table. Compare actual plans for the real predicate and indexes. An index beginning with OrderID supports payment lookup, while a broad report can favor grouped scanning. Both expressions need to preserve the same business result before comparing cost.

Use EXISTS When Only Membership Matters

Sometimes the report needs orders that have any payment, with no payment details. EXISTS expresses that membership question directly. It does not introduce one output row for each matching payment. The database can stop searching once existence has been established.

SELECT o.OrderID, o.OrderAmount
FROM #Orders o
WHERE EXISTS
(
    SELECT 1 FROM #Payments p
    WHERE p.OrderID = o.OrderID
);

I choose EXISTS when the joined table serves only as a filter. That communicates the intent better than selecting order columns and removing duplicates afterward. Which payment fact does your result actually need: existence, a summary, or every detail row?

NOT EXISTS expresses the complementary question for unpaid orders. Avoid replacing it casually with a nullable NOT IN expression. NULL handling can change membership results. The relationship's semantics deserve more attention than the shortest-looking syntax.

Know When Join Duplicates Call for DISTINCT

DISTINCT is valid when the requested result is genuinely a unique set of selected values. A list of unique customer locations is an example. Its cost depends on the plan and available ordering. The optimizer can use sorting, hashing, or existing uniqueness information.

It is unsafe to use DISTINCT as a general cure for unexplained multiplication. Selecting PaymentID keeps payment rows distinct, so the repeated order amount remains. Selecting fewer columns can collapse different legitimate orders that happen to share the same amount. SUM(DISTINCT OrderAmount) has the same problem when two orders cost an equal amount.

A DISTINCT operator is a broom, not a relationship diagram. Keep it when uniqueness is the actual requirement. Remove it only after proving the query already supplies the correct grain or after replacing a filter join with equivalent membership logic.

Verify Totals at Their Own Grain

Calculate the source order total directly from orders, and payment totals directly from payments. Compare them with the report's separately defined totals. Outstanding balance combines those facts at the order grain, rather than adding order amounts at payment grain.

Include boundary cases in the review: no payments, several payments, equal-valued orders, refunds, and payment rows missing a valid order. A foreign key addresses the last case, while aggregation addresses multiplicity. Those are different responsibilities.

Retain a detail report alongside the summary during testing. It makes discrepancies explainable without guessing which rows DISTINCT removed. Once the result grain and relationship keys are correct, tune access paths using representative data. Correct totals are the first performance requirement.

Join duplicates require a grain check before a uniqueness operator. Explain whether join duplicates represent required detail combinations or an incorrectly specified relationship.

Related reading on this blog: Modern Explicit JOIN Syntax: A Brief Note and TOP and DISTINCT: Epic Confusion: SQL in Sixty Seconds #141.

A repeated join result is not a reason to hide rows, it is a signal to verify relationship cardinality and the intended result grain.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.