SQL Joins SQL Topic exerciseMediumVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

Inequality JOIN — Map Salaries to Pay Bands (ON ... BETWEEN ...)

Join employees to every job range containing their salary and return all matching bands.

  • Joins
  • Sorting

Exercise brief

Understand the request

Compensation architecture analyst A salary can fit more than one published pay band, so the audit needs every valid range match.

True inequality JOIN: pair every employee with EVERY job whose pay band their current salary falls inside. Some employees fit multiple bands (job_id ≠ employee.job_id is allowed). The ON predicate is a range check, NOT equality. Return employee_id, first_name, last_name, salary, fitting_job_id, fitting_job_title, min_salary, max_salary — ordered by employee_id, fitting_job_id.

Return

  • Return employee_id, first_name, last_name, salary, fitting_job_id, fitting_job_title, min_salary, max_salary in this exact left-to-right order.

Constraints

  • Use a non-equality range predicate in ON.
  • Do not assume one band per employee.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

employees

  • employee_idINTEGER
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)
  • salaryINTEGER

jobs

  • job_idVARCHAR(20)
  • job_titleVARCHAR(100)
  • min_salaryINTEGER
  • max_salaryINTEGER

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

ON e.salary BETWEEN j.min_salary AND j.max_salary — a RANGE predicate, not an equality.

Hint 2

Expect fan-out: an employee whose salary is 70000 fits Software Developer (60-120k), Marketing Manager (70-110k), Financial Analyst (50-85k) etc.

Hint 3

Inequality joins cannot use index lookups — they often fall back to nested-loop join. Indexes on min/max help only partially.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT e.employee_id, e.first_name, e.last_name, e.salary, j.job_id AS fitting_job_id, j.job_title AS fitting_job_title, j.min_salary, j.max_salary FROM employees e INNER JOIN jobs j ON e.salary BETWEEN j.min_salary AND j.max_salary ORDER BY e.employee_id, j.job_id;

Why this works

Inequality joins are the answer to 'find every X that fits inside Y\'s range' — pay bands, time windows, geo boxes, version ranges. Performance-wise they are slower than equality joins and should use range-friendly indexes (B-tree on the bound columns).

Success check

Every valid employee-band match appears, including employees that fit overlapping ranges.

Expected result

Use this output to verify values, aliases, ordering, and row count.

employee_idfirst_namelast_namesalaryfitting_job_idfitting_job_titlemin_salarymax_salary
100JohnSmith120000IT_MGRIT Manager90000150000
100JohnSmith120000IT_PROGSoftware Developer60000120000
101AliceJohnson85000FIN_ANALYSTFinancial Analyst5000085000
101AliceJohnson85000IT_PROGSoftware Developer60000120000
101AliceJohnson85000MKT_MGRMarketing Manager70000110000
102BobWilson80000FIN_ANALYSTFinancial Analyst5000085000
102BobWilson80000IT_PROGSoftware Developer60000120000
102BobWilson80000MKT_MGRMarketing Manager70000110000
102BobWilson80000SALES_REPSales Representative4000080000
103CarolDavis60000FIN_ANALYSTFinancial Analyst5000085000

Previewing 10 of 35 expected rows. Run the query in the editor to inspect the full result.

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.