Counting, summing, pre-aggregating, and handling NULLs correctly after one-to-many and outer joins.
Avoid wrong counts and duplicated totals after joining.
Protect aggregates from one-to-many duplication.
SELECT c.customer_id,
COUNT(DISTINCT o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id;A one-to-many join can inflate counts unless you count distinct entity keys.
Summarize large fact tables first, then join the result.
WITH order_totals AS (
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
)
SELECT c.customer_id, c.name, ot.total_spent
FROM customers c
LEFT JOIN order_totals ot ON ot.customer_id = c.customer_id;This often improves both correctness and performance.
Replace `NULL` with a friendlier value in results.
SELECT c.customer_id,
COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id;Outer joins can produce `NULL` totals for unmatched rows. `COALESCE` turns them into `0` or another default.
Understand how NULL affects output and predicates.
Missing matches appear as NULL-valued columns.
LEFT JOIN → right-side columns may be NULL
RIGHT JOIN → left-side columns may be NULL
FULL OUTER JOIN → either side may be NULLThis is normal and often what makes outer joins useful.