✏️ Explanatory Question
Level: Intermediate to Advanced — Tests your understanding of the operators used in join conditions.
The difference comes down to the operator used in the join condition:
=). This is the most common join — matching a foreign key to a primary key.= — such as <, >, <=, >=, !=, or BETWEEN.| Feature | EQUI JOIN | NON-EQUI JOIN |
|---|---|---|
| Operator used | Only = |
<, >, <=, >=, !=, BETWEEN |
| Purpose | Match exact related values | Match values in a range |
| Frequency | Very common | Rare / specialized |
| Typical use | PK–FK relationships | Grades, tiers, ranges |
Assign each employee a salary grade from a salary_grades table that defines ranges (low–high) — a perfect case for a range-based (non-equi) join:
| grade | low_salary | high_salary |
|---|---|---|
| A | 70000 | 100000 |
| B | 40000 | 69999 |
| C | 10000 | 39999 |
-- EQUI JOIN: match employee's dept to a department (uses =)
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;
-- NON-EQUI JOIN: place each employee into a salary grade (uses BETWEEN)
SELECT e.name, e.salary, g.grade
FROM employees e
JOIN salary_grades g
ON e.salary BETWEEN g.low_salary AND g.high_salary;
-- NON-EQUI JOIN with > : find all employees earning more than a benchmark
SELECT a.name, b.name AS earns_less
FROM employees a
JOIN employees b ON a.salary > b.salary;