What is a non-equi join?
SQLLevel: IntermediateJOINs Deep Dive
What is a non-equi join?
Hints
- Write the join keys and relationship cardinality.
- What happens to duplicates and unmatched rows? Check WHERE versus ON.
Answer
A join where the condition is not an equality (=) but uses <, >, <=, >=, BETWEEN, or !=.
-- Find all salary bands that an employee's salary falls into
SELECT e.name, e.salary, b.band_name
FROM employees e
JOIN salary_bands b
ON e.salary BETWEEN b.min_salary AND b.max_salary;
Non-equi joins can be expensive since the database can't use the same index lookup strategies as equi-joins.
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT