Non-equi joins in SQL

Introduction

Non-equi joins help you combine data from two tables using conditions other than just equal matches. This lets you find related data based on ranges or inequalities.

When you want to find all products with prices within a certain range of a discount table.

When matching employees to salary grades based on salary ranges.

When linking events that happened before or after a certain date in another table.

When you need to join records where one value is greater than or less than another.

Syntax

SQL

SELECT columns
FROM table1
JOIN table2 ON table1.column operator table2.column;

The operator can be <, >, <=, >=, or BETWEEN, not just =.

Non-equi joins often use INNER JOIN or LEFT JOIN depending on whether you want unmatched rows.

Examples

Join employees to salary grades where employee salary falls within the grade range.

SQL

SELECT e.name, s.grade
FROM employees e
JOIN salary_grades s ON e.salary BETWEEN s.min_salary AND s.max_salary;

Find products with prices inside discount ranges.

SQL

SELECT p.product_name, d.discount
FROM products p
JOIN discounts d ON p.price > d.min_price AND p.price < d.max_price;

Match orders to customers where order date is on or after customer signup.

SQL

SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.order_date >= c.signup_date;

Sample Program

This query joins employees to their salary grades based on salary ranges using a non-equi join with BETWEEN.

SQL

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  salary INT
);

CREATE TABLE salary_grades (
  grade CHAR(1),
  min_salary INT,
  max_salary INT
);

INSERT INTO employees VALUES
(1, 'Alice', 4500),
(2, 'Bob', 3200),
(3, 'Charlie', 6000);

INSERT INTO salary_grades VALUES
('A', 3000, 3999),
('B', 4000, 4999),
('C', 5000, 6999);

SELECT e.name, e.salary, s.grade
FROM employees e
JOIN salary_grades s ON e.salary BETWEEN s.min_salary AND s.max_salary
ORDER BY e.id;

Important Notes

Non-equi joins can be slower than equi-joins because they often prevent the use of indexes.

Make sure the join condition logically matches your data ranges to avoid missing or duplicate rows.

You can combine multiple conditions with AND or OR for complex non-equi joins.

Summary

Non-equi joins match rows using conditions other than equality, like ranges or inequalities.

They are useful for matching data based on intervals or thresholds.

Use BETWEEN, <, >, <=, >= in the JOIN condition to create non-equi joins.