How do you join tables using date ranges instead of exact equality?

Use BETWEEN or >= and <= operators in the ON clause. For example: ON o.order_date BETWEEN p.start_date AND p.end_date. This finds records where the order date falls within the price validity period. Always index the date columns for performance.

Can you use multiple conditions with AND/OR in a JOIN ON clause?

Yes, ON clause supports complex Boolean logic with AND/OR operators. Use parentheses for clarity: ON (condition1 AND condition2) OR (condition3 AND condition4). This is useful for customer segmentation, eligibility rules, or multi-criteria matching.

How do complex JOIN conditions affect query performance?

Complex conditions can prevent index usage and slow queries. Functions on join columns disable indexes. Range conditions are slower than equality. Multiple OR conditions may require multiple index scans. Always test with EXPLAIN and create appropriate indexes on all join condition columns.

Write a query to join orders with the correct tax rates based on order date and customer state.

SELECT o.order_id, o.order_date, c.state,

tr.tax_rate, o.total_amount * tr.tax_rate as tax_amount

FROM orders o

INNER JOIN customers c ON o.customer_id = c.customer_id

INNER JOIN tax_rates tr ON c.state = tr.state

AND o.order_date >= tr.effective_date

AND (tr.end_date IS NULL OR o.order_date <= tr.end_date)

ORDER BY o.order_date;

Complex join with state equality and date range conditions. Handles NULL end_date for current tax rates.

Find customers eligible for promotions based on spending thresholds and membership duration.

SELECT c.customer_name, c.total_spent, c.membership_start,

p.promotion_name, p.discount_percent

FROM customers c

INNER JOIN promotions p ON (

(p.type = 'high_spender' AND c.total_spent >= p.min_spend)

OR

(p.type = 'loyal_member' AND c.membership_start <= CURRENT_DATE - INTERVAL p.min_days DAY)

)

AND p.status = 'active'

AND CURRENT_DATE BETWEEN p.start_date AND p.end_date;

Multiple OR conditions for different promotion types with date range validation for active promotions.

Join products with their applicable discounts based on category and inventory levels.

SELECT p.product_name, p.category, p.stock_quantity,

d.discount_name, d.discount_percent

FROM products p

INNER JOIN discounts d ON (

p.category = d.applicable_category

AND (

(d.condition_type = 'overstocked' AND p.stock_quantity > d.threshold_value)

OR

(d.condition_type = 'clearance' AND p.stock_quantity < d.threshold_value)

)

)