Cartesian Products

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 added because the result ordering 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.

Implicit Syntax

Oracle

-- 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;

Snowflake

-- 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;

Cross Join Syntax

Oracle

-- 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;

Snowflake

-- 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.

Related EWIs

No related EWIs.

PreviousSelf JoinNextInner Join

Last updated