Non-Equi Joins

Every join example so far has matched rows using equality — c.customer_id = o.customer_id. That covers the vast majority of real-world joins, since most relationships are expressed as a foreign key that equals a primary key. But the ON clause is not limited to equality: it can be any boolean expression, including comparisons like <, >, <=, >=, or BETWEEN. A join whose condition uses anything other than equality is called a non-equi join.

Classic use case: matching a value into a range

The textbook example of a non-equi join is matching a numeric value against a table of ranges — for instance, matching each sale's amount against a tax_brackets table where each bracket defines a low and high bound, rather than a single matching ID.

sales and tax_brackets tables

SQL

CREATE TABLE sales (
  sale_id INT PRIMARY KEY,
  amount  DECIMAL(10, 2)
);

CREATE TABLE tax_brackets (
  bracket_name VARCHAR(20),
  low_bound    DECIMAL(10, 2),
  high_bound   DECIMAL(10, 2),
  tax_rate     DECIMAL(4, 2)
);

INSERT INTO sales (sale_id, amount) VALUES
  (1, 45.00),
  (2, 150.00),
  (3, 320.00);

INSERT INTO tax_brackets (bracket_name, low_bound, high_bound, tax_rate) VALUES
  ('Low',    0.00,   99.99,  0.05),
  ('Medium', 100.00, 249.99, 0.08),
  ('High',   250.00, 999.99, 0.12);

There is no single column in sales that equals a single column in tax_brackets — a sale of 150.00 does not equal 100.00 or 249.99, it falls between them. A non-equi join, using BETWEEN in the ON clause, expresses that relationship directly.

Worked example

Matching each sale to its tax bracket

SQL

SELECT s.sale_id, s.amount, t.bracket_name, t.tax_rate
FROM sales s
JOIN tax_brackets t
  ON s.amount BETWEEN t.low_bound AND t.high_bound;

sale_id | amount | bracket_name | tax_rate
--------+--------+--------------+---------
1       | 45.00  | Low          | 0.05
2       | 150.00 | Medium       | 0.08
3       | 320.00 | High         | 0.12

Each sale lands in exactly one bracket because the ranges in tax_brackets do not overlap, so the join produces one row per sale — but note that BETWEEN is inclusive on both ends (equivalent to low_bound <= s.amount AND s.amount <= high_bound), so range boundaries must be defined carefully to avoid gaps or overlaps.

Other non-equi patterns

The same idea extends to plain comparison operators without BETWEEN — for example, joining an events table to a price_changes table using event_date >= effective_date to find the price that was in effect on a given date, or pairing rows from the same table using a < comparison to generate all combinations that come before one another chronologically or numerically.

A comparison-based non-equi join

SQL

-- Find the effective price for each event based on the most
-- recent price change that happened on or before the event date.
SELECT e.event_id, e.event_date, p.price, p.effective_date
FROM events e
JOIN price_changes p
  ON p.effective_date <= e.event_date;

Less common, but valuable for range-matching

Non-equi joins show up far less often than ordinary equi-joins, since most relationships in a well-designed schema are expressed with equality between keys. But whenever the relationship genuinely is "falls within a range" rather than "matches exactly," a non-equi join is the natural, direct way to express that — cleaner and usually faster than trying to force the same logic through equality comparisons and extra application code.

A non-equi join uses a comparison other than equality — such as <, >, or BETWEEN — in the ON clause.
Matching a value into a range (like a sale amount into a tax bracket) is the classic non-equi join use case.
BETWEEN is inclusive on both ends, so range boundaries need to be defined without gaps or overlaps.
Non-equi joins are less common than equi-joins but are the right tool whenever "falls within a range" is the actual relationship.