Row multiplication occurs when a record matches multiple records in more than one related table, causing combinations of those matches to appear in the result.
This is a common cause of inflated SUM and COUNT results in analytical SQL.
A customer has multiple orders and multiple support tickets. Joining both child tables directly can multiply rows.
SELECT
c.CustomerName,
o.OrderID,
t.TicketID
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID
LEFT JOIN SupportTickets t
ON c.CustomerID = t.CustomerID;Critical in financial reporting, customer analytics, reconciliation, and dashboard calculations.
- Multiple one-to-many JOINs can multiply rows.
- The multiplication may be mathematically valid but unwanted.
- Aggregating each child table first can prevent inflation.
- COUNT and SUM are especially vulnerable.
- Always validate row counts after adding JOINs.
What is row multiplication in SQL?
It occurs when multiple matching child records combine with each other and create additional result rows.
How can row multiplication be prevented?
Aggregate child datasets separately, use EXISTS where appropriate, or redesign the query based on the required grain.