Non-Equi JOIN in SQL with Range-Based Conditions

Every JOIN example covered so far in this module has relied on equality: matching a department_id to a department_id, or an employee_id to a manager_id. But not every real-world relationship between tables is based on exact equality. Sometimes a value in one table needs to fall within a range defined by another table — an employee's salary falling within a salary band's minimum and maximum, or a transaction date falling between a promotional campaign's start and end dates.

This is exactly what a Non-Equi JOIN handles: a JOIN whose condition uses comparison operators like BETWEEN, greater than, less than, or greater-than-or-equal instead of a simple equals sign. Non-Equi JOINs are less commonly taught but appear frequently in real analytical and reporting queries, and are a genuine differentiator in advanced SQL interviews.

Key Definitions

• Non-Equi JOIN: A JOIN whose condition uses a comparison operator other than equality, such as BETWEEN, <, >, <=, or >=.
• Range table: A table that defines boundaries, such as a salary_bands table with min_salary and max_salary columns, used to classify values from another table.
• BETWEEN operator: A SQL operator that checks whether a value falls within an inclusive range, often used as the core condition in a Non-Equi JOIN.
• Equi-join: A JOIN based on an equality condition, the type covered in most of this module prior to this lesson, in contrast to Non-Equi JOIN.

What You'll Learn

• Define Non-Equi JOIN and explain how it differs from standard equality-based joins.
• Write a Non-Equi JOIN using BETWEEN to match a value against a range defined in another table.
• Apply Non-Equi JOIN to a realistic salary-band classification scenario.
• Recognize other common real-world use cases for Non-Equi JOIN, such as date-range matching.
• Understand the performance implications of range-based join conditions compared to equality joins.

Detailed Explanation

A Non-Equi JOIN uses the exact same JOIN syntax as any other JOIN, with the ON clause simply containing a range-based condition instead of an equality check. Consider a salary_bands table with columns band_name, min_salary, and max_salary, defining bands like 'Junior' (30000 to 50000), 'Mid' (50001 to 80000), and 'Senior' (80001 to 120000). To classify each employee into a band based on their salary, you cannot use equality, since an employee's exact salary will almost never match a band's min or max value precisely. Instead, you join using 'employees.salary BETWEEN salary_bands.min_salary AND salary_bands.max_salary'.

This works exactly like any other JOIN mechanically: for every employee row, the database checks each row in salary_bands to see if the employee's salary falls within that band's range, and produces a combined row when the condition is satisfied. Because BETWEEN is inclusive on both ends, care must be taken when designing the ranges themselves to avoid overlapping bands, which would cause a single employee to match multiple bands and appear duplicated in the JOIN result.

Another extremely common real-world Non-Equi JOIN scenario involves date ranges. A sales table might need to be joined against a promotions table to determine which promotional campaign was active on the date of each sale, using a condition like 'sales.sale_date BETWEEN promotions.start_date AND promotions.end_date'.

Performance is an important consideration with Non-Equi JOINs. Unlike equality joins, which can leverage highly efficient index lookups (especially on primary key or foreign key columns), range-based conditions often require the database to scan a wider set of rows to determine matches, since a range comparison cannot always use the same index optimization as a direct equality match. On very large range tables, this can make Non-Equi JOINs noticeably slower, and understanding this tradeoff is valued in advanced-level SQL discussions.

Visual Summary

Draw a number line from 0 to 130000 representing salary values. Mark three shaded bands on the line: 'Junior' from 30000-50000, 'Mid' from 50001-80000, 'Senior' from 80001-120000. Plot individual employee salary points on the line (such as 45000, 62000, 95000) and draw dotted vertical lines connecting each point down to the band it falls within, captioned 'Non-Equi JOIN matches a value against a range, not an exact value.'

Quick Reference

employee_name	salary	Matched Band (via Non-Equi JOIN)
Arjun Das	45000	Junior (30000–50000)
Rahul Sharma	62000	Mid (50001–80000)
Asha Mehta	95000	Senior (80001–120000)

SQL Example

CREATE TABLE salary_bands (
  band_name   VARCHAR(50),
  min_salary  DECIMAL(10,2),
  max_salary  DECIMAL(10,2)
);

INSERT INTO salary_bands VALUES
  ('Junior', 30000, 50000),
  ('Mid',    50001, 80000),
  ('Senior', 80001, 120000);

-- Non-Equi JOIN using BETWEEN to classify employees into salary bands
SELECT
  e.employee_name,
  e.salary,
  sb.band_name
FROM employees e
JOIN salary_bands sb
  ON e.salary BETWEEN sb.min_salary AND sb.max_salary;

-- Non-Equi JOIN for date-range matching against active promotions
SELECT
  s.sale_id,
  s.sale_date,
  p.promotion_name
FROM sales s
JOIN promotions p
  ON s.sale_date BETWEEN p.start_date AND p.end_date;

The first query classifies each employee into a salary band by checking whether their exact salary falls within a band's min_salary and max_salary range, using BETWEEN as the join condition instead of equality. The second query applies the same range-matching principle to dates, joining each sale to whichever promotion was active on that specific sale_date, again using BETWEEN rather than an equals sign.

Real-World Examples

• HR systems use Non-Equi JOIN to automatically classify employees into pay grades or salary bands based on defined salary ranges.
• Retail analytics platforms use Non-Equi JOIN with date ranges to attribute each sale to the specific marketing campaign or promotion that was active at the time of purchase.
• Credit scoring systems use Non-Equi JOIN to classify customers into risk tiers based on which score range (such as 300-579, 580-669, 670-850) their credit score falls into.
• Shipping and logistics systems use Non-Equi JOIN to match a package's weight against a shipping_rate_bands table to determine the correct shipping cost tier.
• Academic grading systems use Non-Equi JOIN to convert a numeric exam score into a letter grade by matching it against a grade_boundaries table defining score ranges for A, B, C, and so on.

Common Mistakes to Avoid

• Designing a range table (like salary_bands) with overlapping boundaries, causing duplicate matches in the Non-Equi JOIN result.
• Assuming Non-Equi JOIN requires special SQL syntax, when it actually uses the exact same JOIN keyword, just with a different type of condition in ON.
• Not considering the performance impact of range-based conditions on very large range tables, especially without appropriate indexing strategies.
• Forgetting that BETWEEN is inclusive on both ends, which can cause boundary values to unexpectedly match two adjacent, improperly designed ranges.

Interview Questions

Q1. What is a Non-Equi JOIN and how does it differ from a standard JOIN?

A Non-Equi JOIN uses a comparison operator other than equality — such as BETWEEN, greater than, or less than — in its ON clause, whereas a standard equi-join matches rows based on exact equality between columns, such as matching a foreign key to a primary key.

Q2. Give a practical example of when a Non-Equi JOIN would be necessary.

Classifying employees into salary bands is a classic example: an employee's exact salary rarely matches a band's boundary exactly, so the join must use a range condition like BETWEEN min_salary AND max_salary rather than an equality match.

Q3. What risk exists if salary bands (or any range table) have overlapping ranges?

If ranges overlap, a single employee's salary could satisfy the BETWEEN condition for more than one band, causing that employee to appear duplicated in the JOIN result, once for each matching band. Range tables should be carefully designed to avoid overlaps.

Q4. Why can Non-Equi JOINs be slower than equality-based joins on large tables?

Equality joins can often use efficient index lookups, particularly on primary or foreign key columns, to quickly find exact matches. Range-based conditions in a Non-Equi JOIN typically require scanning a wider set of rows to evaluate the range comparison, which can be less efficient, especially on very large range tables.

Practice MCQs

1. A Non-Equi JOIN uses a join condition based on:

1. Exact equality only
2. A comparison operator other than equality, such as BETWEEN
3. Only the CROSS JOIN keyword
4. Table aliases

Answer: B. A comparison operator other than equality, such as BETWEEN

Explanation: Non-Equi JOIN is specifically defined by its use of range or inequality comparisons instead of a simple equals sign.

2. Classifying employees into salary bands typically requires:

1. An equi-join on employee_id
2. A Non-Equi JOIN using BETWEEN on the salary column
3. A CROSS JOIN
4. A SELF JOIN

Answer: B. A Non-Equi JOIN using BETWEEN on the salary column

Explanation: Since exact salaries rarely match a band's boundary values precisely, a range-based BETWEEN condition is required rather than equality.

3. What happens if salary bands in a range table overlap?

1. The query fails with an error
2. An employee's salary could match multiple bands, duplicating that employee's row in the result
3. Overlaps are automatically resolved by SQL
4. Only the first matching band is ever returned

Answer: B. An employee's salary could match multiple bands, duplicating that employee's row in the result

Explanation: Overlapping ranges mean a single value can satisfy the BETWEEN condition for more than one row in the range table, producing duplicate matches.

4. Why can Non-Equi JOINs be less performant than equi-joins on large tables?

1. They cannot use any indexes at all
2. Range comparisons often cannot use the same efficient lookup optimizations as exact equality matches
3. They are not supported in MySQL
4. They require CROSS JOIN internally

Answer: B. Range comparisons often cannot use the same efficient lookup optimizations as exact equality matches

Explanation: Equality joins benefit from precise index lookups, while range-based conditions typically require scanning a broader set of rows to evaluate the comparison.

Quick Revision Points

• Non-Equi JOIN uses a comparison operator other than equality (BETWEEN, <, >, <=, >=) in its ON clause.
• The classic teaching example is classifying a value (like salary) into a range defined by another table (like salary_bands).
• Overlapping ranges in a range table can cause duplicate matches; range tables must be carefully designed to avoid this.
• Non-Equi JOINs are generally less performant than equi-joins since range comparisons cannot always use the same index optimizations as exact matches.

Conclusion

• Non-Equi JOIN extends the JOIN concept beyond equality, matching values against ranges instead of exact values.
• Salary-band classification and date-range attribution are the two most common, practical real-world Non-Equi JOIN scenarios.
• Careful range design (avoiding overlaps) is essential to prevent unintended duplicate matches.
• Performance considerations differ meaningfully between Non-Equi JOINs and standard equi-joins, especially at scale.

A Non-Equi JOIN uses a comparison operator other than equality — most commonly BETWEEN — to match rows based on whether a value falls within a range defined by another table, rather than matching an exact value. Classic use cases include classifying employees into salary bands and attributing sales to active promotional campaigns based on date ranges. While mechanically identical in syntax to any other JOIN, Non-Equi JOINs require careful range design to avoid overlapping matches and typically carry different performance characteristics than standard equality-based joins.