Non-Equality Joins

The Price Tier Problem

We had a `price_tiers` table: Tier 1 = $0-50, Tier 2 = $51-100, etc. I needed to classify every product into a tier, but the key wasn't exact—it was a range.

The Quest: The Range Join

You don't always join on `=`. Sometimes you join using `BETWEEN`, `>`, or `<`. This is called a **Non-Equi Join** or **Range Join**.

The Implementation: The Syntax

SELECT

p.product_name,

p.price,

t.tier_name

FROM products p

JOIN price_tiers t ON p.price BETWEEN t.min_price AND t.max_price;

Other Examples

Assign an employee to a pay grade based on salary range.

Match an event to a time window.

Link a transaction to a fiscal quarter.

The "Oops" Moment

My first range join returned duplicate results. A product priced at exactly $50 matched BOTH Tier 1 (0-50) and Tier 2 (50-100) because my range boundaries overlapped!

**Pro Tip**: Make sure your range boundaries are exclusive on one side. For example, use `price >= min AND price < max` to avoid double-counting.

The Victory

Every product was now correctly labeled with its pricing tier, all in a single query. Range joins are perfect for classification problems.

Your Task for Today

Create a table of categories with price ranges (e.g., 'Cheap', 'Mid', 'Premium') and use a range join to classify your products.

*Day 42: Join Performance Tuning.*