Band Join

Description

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. (Oracle SQL Language Reference BandJoin)

In this section, we will see how a band join is executed in Snowflake and the execution plan is very similar to the improved version of Oracle.

Sample Source Patterns

Order by clause added because the result order may vary between Oracle and Snowflake.

Since the result set is too large, Row Limiting Clause was added. You can remove it to retrieve the entire result set.

Check this section to set up the sample database.

If you migrate this code without the create tables, the converter won’t be able to load semantic information of the columns and a warning will appear on the arithmetic operations.

Basic Band Join case

Oracle

Query

SELECT  e1.last_name || 
        ' has salary between 100 less and 100 more than ' || 
        e2.last_name AS "SALARY COMPARISON"
FROM    employees e1, 
        employees e2
WHERE   e1.salary 
BETWEEN e2.salary - 100 
AND     e2.salary + 100
ORDER BY "SALARY COMPARISON"
FETCH FIRST 10 ROWS ONLY

Result

SALARY COMPARISON                                              |
---------------------------------------------------------------+
Abel has salary between 100 less and 100 more than Abel        |
Abel has salary between 100 less and 100 more than Cambrault   |
Abel has salary between 100 less and 100 more than Raphaely    |
Ande has salary between 100 less and 100 more than Ande        |
Ande has salary between 100 less and 100 more than Mavris      |
Ande has salary between 100 less and 100 more than Vollman     |
Atkinson has salary between 100 less and 100 more than Atkinson|
Atkinson has salary between 100 less and 100 more than Baida   |
Atkinson has salary between 100 less and 100 more than Gates   |
Atkinson has salary between 100 less and 100 more than Geoni   |

Snowflake

Query

SELECT
NVL(  e1.last_name :: STRING, '') ||
' has salary between 100 less and 100 more than ' || NVL(
e2.last_name :: STRING, '') AS "SALARY COMPARISON"
FROM employees e1, employees e2
WHERE   e1.salary
BETWEEN e2.salary - 100
AND     e2.salary + 100
ORDER BY "SALARY COMPARISON"
FETCH FIRST 10 ROWS ONLY;

Result

SALARY COMPARISON                                              |
---------------------------------------------------------------+
Abel has salary between 100 less and 100 more than Abel        |
Abel has salary between 100 less and 100 more than Cambrault   |
Abel has salary between 100 less and 100 more than Raphaely    |
Ande has salary between 100 less and 100 more than Ande        |
Ande has salary between 100 less and 100 more than Mavris      |
Ande has salary between 100 less and 100 more than Vollman     |
Atkinson has salary between 100 less and 100 more than Atkinson|
Atkinson has salary between 100 less and 100 more than Baida   |
Atkinson has salary between 100 less and 100 more than Gates   |
Atkinson has salary between 100 less and 100 more than Geoni   |

Migrating some SELECT statements without the corresponding tables could generate the MSCEWI3036 TYPES RESOLUTION EWI. To avoid this warning, include the CREATE TABLE inside the file.

The results are the same making the BAND JOIN functional equivalent.

Execution plan

As extra information, the special thing about the band joins is the execution plan.

The following image shows the enhanced execution plan (implemented since Oracle 12c) for the test query:

And in the following image, we will see the execution plan in Snowflake:

The execution plan in Snowflake is very similar to Oracle’s optimized version. The final duration and performance of the query will be affected by many other factors and are completely dependent on each DBMS internal functionality.

Known Issues

1. Results ordering mismatch between languages

The result of the query will have the same content in both database engines but the order might be different if no Order By clause is defined in the query.

Related EWIs

No related EWIs.