Some parts in the output code are omitted for clarity reasons.
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.
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
IN -> Oracle_01.sql
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
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
OUT -> Oracle_01.sql
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
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!! e2.salary - 100
AND
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!! e2.salary + 100
ORDER BY "SALARY COMPARISON"
FETCH FIRST 10 ROWS ONLY;
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 |
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 query result 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.
Migrating some SELECT statements without the corresponding tables could generate the : Types resolution issues. To avoid this warning, include the CREATE TABLE inside the file.
Types resolution issues, the arithmetic operation may not behave correctly between string and date.
