Some parts in the output code are omitted for clarity reasons.
If two tables in a join query have no join condition, then Oracle Database returns their Cartesian product. Oracle combines each row of one table with each row of the other. (Oracle SQL Reference Cartesian Products Subsection)
Oracle and Snowflake are also compatible with the ANSI Cross Join syntax that has the same behavior of a cartesian product.
No special transformation is performed for this kind of Join since Snowflake supports the same syntax.
Sample Source Patterns
Order by clause was 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.
-- Resulting rows
SELECT * FROM hr.employees, hr.departments
ORDER BY first_name
FETCH FIRST 5 ROWS ONLY;
-- Resulting total rows
SELECT COUNT(*) FROM hr.employees, hr.departments;
-- Resulting rows
SELECT * FROM
hr.employees,
hr.departments
ORDER BY first_name
FETCH FIRST 5 ROWS ONLY;
-- Resulting total rows
SELECT COUNT(*) FROM
hr.employees,
hr.departments;
-- Resulting rows
SELECT * FROM hr.employees CROSS join hr.departments
ORDER BY first_name
FETCH FIRST 5 ROWS ONLY;
-- Resulting total rows
SELECT COUNT(*) FROM hr.employees CROSS join hr.departments;
-- Resulting rows
SELECT * FROM
hr.employees
CROSS join hr.departments
ORDER BY first_name
FETCH FIRST 5 ROWS ONLY;
-- Resulting total rows
SELECT COUNT(*) FROM
hr.employees
CROSS join hr.departments;
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.
