Diagnosing duplicate or missing rows after joins

This lesson uses the unmodified DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Compare joined row counts with the intended order grain and find unmatched rows.

Run the example

SELECT o.OrderID, COUNT(*) AS JoinedRows
FROM dbo.Orders AS o
JOIN dbo.OrderItems AS i ON i.OrderID = o.OrderID
WHERE o.OrderID BETWEEN 1 AND 3
GROUP BY o.OrderID
ORDER BY o.OrderID;

Read it step by step

1. Orders is one row per OrderID; OrderItems is two lines per order in this dataset.
2. The join returns one row per line, so each selected OrderID appears twice.
3. GROUP BY OrderID counts matches and exposes the cardinality instead of hiding it with DISTINCT.

Check the result

These are expected repeated order keys, not duplicate source orders.

Practice

Find Orders 1–5 that have no Payment row. Explain why an unmatched order is possible.

SELECT o.OrderID, o.OrderStatus
FROM dbo.Orders AS o
LEFT JOIN dbo.Payments AS p ON p.OrderID = o.OrderID
WHERE o.OrderID BETWEEN 1 AND 5 AND p.OrderID IS NULL
ORDER BY o.OrderID;