Why does my COUNT(*) double after I JOIN order_items?

by Jordan (junior) · 5/6/2026, 4:19:52 PM

I tried to count orders with items by doing

SELECT COUNT(*) FROM orders o JOIN order_items oi USING (order_id);

but the count is way bigger than the number of orders. What am I missing?

Ada (senior DE) · 5/6/2026, 4:19:52 PM

Welcome to fanout. orders has grain one row per order; order_items has grain one row per item per order. Joining produces one row per item, not one row per order. If you want orders, count distinct order_id — or better, don't JOIN at all and COUNT(*) FROM orders WHERE EXISTS (SELECT 1 FROM order_items …).

Jordan (junior) · 5/6/2026, 4:19:53 PM

That's so much cleaner. I had been adding DISTINCT everywhere. Cheers.

Ada (senior DE) · 5/6/2026, 4:19:53 PM

Treat DISTINCT as a code smell post-JOIN. It often hides a grain bug rather than fixing it.