Multi-Key and Range Condition Joins in SQL

Question

Given `promotions` (promo_code, start_date, end_date, discount_pct) and `orders` (order_id, promo_code, order_date), write a query to join orders with their applicable promotion, verifying that `order_date` falls within `start_date` and `end_date`.

Accepted Answer

Optimal Solution:
SELECT 
  o.order_id,
  o.promo_code,
  p.discount_pct
FROM orders o
INNER JOIN promotions p 
  ON o.promo_code = p.promo_code
 AND o.order_date BETWEEN p.start_date AND p.end_date;

Technical Explanation:
The `ON` clause supports multiple conditions combining equality (`promo_code`) and range validation (`BETWEEN`).

order_id	promo_code	order_date	start_date	end_date
1	DIWALI50	2026-10-20	2026-10-15	2026-10-25
2	DIWALI50	2026-11-05	2026-10-15	2026-10-25