Non-Equi Joins for Tier Banding and Tax Brackets in SQL

Question

Given `employees` (emp_id, name, salary) and `salary_grades` (grade, min_salary, max_salary), assign each employee to their appropriate salary grade using a non-equi join.

Accepted Answer

Optimal Solution:
SELECT 
  e.emp_id,
  e.name,
  e.salary,
  sg.grade
FROM employees e
JOIN salary_grades sg 
  ON e.salary BETWEEN sg.min_salary AND sg.max_salary
ORDER BY e.salary DESC;

Technical Explanation:
Instead of an equality join (`=`), the join condition uses `BETWEEN min_salary AND max_salary` to map each salary into its lookup bracket dynamically.

emp_id	salary	grade	min_salary	max_salary
1	45000	Grade A	0	50000
2	75000	Grade B	50001	100000

emp_id	name	salary	grade
1	Sachin	45000	Grade A
2	Shreya	75000	Grade B