Row multiplication occurs when a row from one table matches multiple rows from another table, causing the result to contain multiple combinations of the matching records.
Row multiplication can significantly affect aggregates such as SUM and COUNT and may lead to misleading business results.
A customer with 5 orders will produce 5 joined rows when the customer is joined to the orders table.
SELECT
c.CustomerName,
o.OrderID,
od.ProductID
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
INNER JOIN OrderDetails od
ON o.OrderID = od.OrderID;Critical for financial reporting, sales analysis, transaction reporting, and complex multi-table queries.
- One-to-many relationships multiply rows.
- Multiple one-to-many JOINs can multiply rows further.
- Aggregates can be affected by row multiplication.
- Understand table relationships before calculating totals.
- Pre-aggregation can sometimes prevent unwanted multiplication.
What is row multiplication in SQL?
It occurs when one row matches multiple rows in another table, creating multiple result rows.
Why is row multiplication dangerous for SUM?
The same business amount can appear multiple times in the joined result and therefore be counted more than once.