Fan-out from single-column join on composite key

Question

Accepted Answer

Joining only on product_id ignores the region column, so each order matches both price rows (US and EU). Order 1 matches prices for US and EU (2 rows), and order 2 does the same (2 more rows) - 4 total, which is wrong. The fix is to also join on region: ON o.product_id = p.product_id AND o.region = p.region, which correctly returns 2 rows with the right prices.