1. Introduction

A band join is a special type of nonequijoin in which key values in one data set must fall within the specified range (“band”) of the second data set. The same table can serve as both the first and second data sets.

A Band Join in Oracle is a type of join where rows from two tables are matched based on a range of values. Unlike an equijoin (where values are matched using the = operator), a band join uses range conditions like BETWEEN, <, or >.

This type of join is helpful when data in one table needs to fall within a range defined in another table, such as categorizing data into ranges or bins.

2. Use Case Examples

• Grade Assignment: Match student scores to grade ranges.
• Salary Banding: Categorize employees into salary bands.
• Date Ranges: Associate events with their respective time periods.

3. Example

We’ll use the following tables to understand band join.

Student table. Contains the score of students.

+------------+--------------+-------+
| STUDENT_ID | STUDENT_NAME | SCORE |
+------------+--------------+-------+
| 1          | Alice        | 85    |
+------------+--------------+-------+
| 2          | Bob          | 92    |
+------------+--------------+-------+
| 3          | Charlie      | 78    |
+------------+--------------+-------+
| 4          | Diana        | 60    |
+------------+--------------+-------+
| 5          | Eve          | 45    |
+------------+--------------+-------+

Grade table. Defines the grade ranges.

+-------+-----------+-----------+
| GRADE | MIN_SCORE | MAX_SCORE |
+-------+-----------+-----------+
| A     | 90        | 100       |
+-------+-----------+-----------+
| B     | 80        | 89        |
+-------+-----------+-----------+
| C     | 70        | 79        |
+-------+-----------+-----------+
| D     | 60        | 69        |
+-------+-----------+-----------+
| F     | 0         | 59        |
+-------+-----------+-----------+

Band Join Query

The goal is to assign grades to students based on their scores. A band join matches the score from the student table to the min_score and max_score range in the grade table.

SELECT 
    s.student_id, 
    s.student_name, 
    s.score, 
    g.grade
FROM 
    students s
JOIN 
    grades g
ON 
    s.score BETWEEN g.min_score AND g.max_score;

Output

+------------+--------------+-------+-------+
| Student_ID | Student_Name | Score | Grade |
+------------+--------------+-------+-------+
| 1          | Alice        | 85    | B     |
+------------+--------------+-------+-------+
| 2          | Bob          | 92    | A     |
+------------+--------------+-------+-------+
| 3          | Charlie      | 78    | C     |
+------------+--------------+-------+-------+
| 4          | Diana        | 60    | D     |
+------------+--------------+-------+-------+
| 5          | Eve          | 45    | F     |
+------------+--------------+-------+-------+

Explanation

• The JOIN condition uses BETWEEN to match each student’s score with the appropriate grade range.
• For each row in student, Oracle compares the score against the min_score and max_score columns in the grade table.

Notes

• Performance Consideration: Band joins can be expensive for large datasets because they evaluate ranges for each row. Ensure indexes are used effectively.
• Alternative Syntax: You can use >= and <= for explicit range matching instead of BETWEEN:

ON s.score >= g.min_score AND s.score <= g.max_score