Duplicate Rows from Joins and Finding Unmatched Records

Concept

A join does not just filter - it can multiply. If one row on the left matches three rows on the right, that left row appears three times. Every value from the left is then repeated three times, and any SUM over it is triple counted.

Example: the multiplication

-- Order 1000 has 2 line items, order 1003 has 2, the rest have 1
SELECT o.id, o.total, oi.product_id, oi.quantity
FROM   orders o
JOIN   order_items oi ON oi.order_id = o.id
ORDER BY o.id;

-- WRONG: o.total is counted once per line item
SELECT SUM(o.total) AS inflated_revenue
FROM   orders o
JOIN   order_items oi ON oi.order_id = o.id;

-- RIGHT: aggregate the child first, then join one row to one row
SELECT SUM(o.total) AS revenue
FROM   orders o;

-- RIGHT: or aggregate the detail and join the summary
SELECT o.id, o.total, li.line_count, li.line_value
FROM   orders o
JOIN  (SELECT order_id,
              COUNT(*)                     AS line_count,
              SUM(quantity * unit_price)   AS line_value
       FROM   order_items
       GROUP BY order_id) li ON li.order_id = o.id;

Explanation

The fix is never SELECT DISTINCT. DISTINCT removes duplicate rows, but the sum was already wrong before de duplication, and two orders that genuinely have the same total would be collapsed into one. The fix is to aggregate the many side before joining, so each parent row meets exactly one summary row.

Diagnosing it

-- Does the join change the row count?
SELECT COUNT(*) FROM orders;                                    -- 5
SELECT COUNT(*) FROM orders o JOIN order_items oi ON oi.order_id = o.id;  -- 7

-- Which keys are duplicated on the right hand side?
SELECT order_id, COUNT(*) AS matches
FROM   order_items
GROUP BY order_id
HAVING COUNT(*) > 1;

Finding unmatched records: the anti join

Three ways to answer which departments have no employees, in order of preference:

-- 1. NOT EXISTS - NULL safe, usually the fastest, always correct
SELECT d.name
FROM   departments d
WHERE  NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

-- 2. LEFT JOIN ... IS NULL - the classic anti join
SELECT d.name
FROM   departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE  e.id IS NULL;

-- 3. NOT IN - correct ONLY if the subquery column can never be NULL
SELECT d.name
FROM   departments d
WHERE  d.id NOT IN (SELECT dept_id FROM employees WHERE dept_id IS NOT NULL);

Form 2 works because an unmatched department gets NULL in every employee column; testing the employee primary key for NULL reliably identifies those rows. Test the key, not a nullable column - WHERE e.email IS NULL would also return departments whose only employee has no email.

-- Customers who have never ordered
SELECT c.name
FROM   customers c
WHERE  NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Products that have never been sold
SELECT p.name
FROM   products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE  oi.product_id IS NULL;

Important rules

• A join to the many side of a one to many relationship multiplies rows.
• DISTINCT hides duplicates; it does not fix incorrect aggregates.
• In a LEFT JOIN ... IS NULL anti join, always test the right table's primary key.
• NOT IN returns nothing if the subquery yields even one NULL.
• EXISTS stops at the first match, so it does not care how many matches there are.

Common mistakes

• Reaching for DISTINCT the moment a total looks too high.
• Summing a parent column across a child join.
• Anti joining on a nullable column instead of the key.
• Using NOT IN against a nullable column and getting an empty result.

Best practices

• Know the grain of every query before aggregating; state it in a comment.
• Pre aggregate the many side in a CTE, then join summary to summary.
• Use NOT EXISTS as the default anti join.
• Compare row counts before and after adding a join - a change you did not expect is a bug.

Practice

1. Write a query returning each order with its line count and line value, without inflating the order total.
2. Find every product never sold, using both the NOT EXISTS and LEFT JOIN ... IS NULL forms.
3. Explain why SELECT DISTINCT o.id, o.total FROM orders o JOIN order_items oi ... gives the right row count but is still the wrong approach for a revenue total.