The ON of a join can be any condition, not just a.id = b.id.
- Ranges. Put each product in a price band from a lookup table:
ON p.price BETWEEN b.min_price AND b.max_price.
- Date ranges. Find the price that applied on the day of an order from a price history:
ON o.order_date >= h.valid_from AND (h.valid_to IS NULL OR o.order_date <= h.valid_to). The open end (valid_to is NULL for the current price) must be handled, or current prices drop out.
- Several columns. When a row is identified by two columns, join on both:
ON po.product_id = ps.product_id AND po.supplier_id = ps.supplier_id. Joining on only one multiplies rows.
Ranges that overlap make one row match several others. That can be right (an order during two campaigns belongs to both) or a data problem, so count your rows before and after the join.