Why Non-equi joins in SQL? - Purpose & Use Cases

The Big Idea

What if you could match data not just by exact values, but by ranges and conditions, all in one simple step?

The Scenario

Imagine you have two lists: one with students' test scores and another with grade boundaries. You want to find out which grade each student got. Doing this by hand means checking each score against every boundary one by one.

The Problem

Manually comparing each score to multiple grade ranges is slow and easy to mess up. It's hard to keep track of all the conditions and you might miss some matches or make mistakes in calculations.

The Solution

Non-equi joins let you automatically match rows based on conditions other than equality, like greater than or less than. This means you can easily link scores to grade ranges in one simple query.

Before vs After

✗ Before

for score in scores:
  for boundary in grade_boundaries:
    if score >= boundary.min and score < boundary.max:
      print(score, boundary.grade)

✓ After

SELECT s.score, g.grade
FROM scores s
JOIN grade_boundaries g ON s.score >= g.min AND s.score < g.max;

What It Enables

It enables powerful data matching based on ranges or inequalities, making complex comparisons easy and accurate.

Real Life Example

Assigning customers to discount tiers based on their purchase amounts, where each tier covers a range of spending.

Key Takeaways

Manual comparisons for ranges are slow and error-prone.

Non-equi joins let you match data using conditions like > or <.

This makes complex data matching tasks simple and reliable.