JOINs and Row Counts

The one idea: A join can quietly change how many rows you have, and that's behind most "the numbers don't match" surprises. Before trusting a joined query, ask how many rows you'd expect and whether the join type could be dropping rows or multiplying them.

⚠️ The trap: A JOIN just glues the matching columns from the second table onto my existing rows, so my row count stays the same. That feels right and it's wrong. A join can shrink your rows (INNER drops the non-matches) or multiply them (one customer with 3 orders becomes 3 rows) — and any SUM taken afterward silently inherits that inflation.

If there's one SQL concept worth mastering above all others for reading dashboards, it's this: a JOIN can change how many rows you have, and the type of join determines exactly how. Most "the numbers don't match" incidents trace back to a join silently inflating or shrinking a result.

The mental model

Picture two tables: customers (one row per customer) and orders (one row per order — a customer can have many, or zero).

• INNER JOIN keeps only rows that match in both tables. A customer with zero orders disappears entirely. A customer with 5 orders appears 5 times.
• LEFT JOIN keeps every row from the left (first-listed) table, whether or not it matches. A customer with zero orders still appears once, with the order columns filled in as NULL.
• RIGHT JOIN is the mirror image of LEFT — rare in practice, since you can always rewrite it as a LEFT JOIN by swapping table order.

SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o…