Cartesian Products

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.

Check this section to set up the sample database.

Implicit Syntax

Oracle

Query

IN -> Oracle_01.sql

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

Result 1

|EMPLOYEE_ID|FIRST_NAME|LAST_NAME|EMAIL |PHONE_NUMBER|HIRE_DATE              |JOB_ID|SALARY|COMMISSION_PCT|MANAGER_ID|DEPARTMENT_ID|DEPARTMENT_ID|DEPARTMENT_NAME|MANAGER_ID|LOCATION_ID|
|-----------|----------|---------|------|------------|-----------------------|------|------|--------------|----------|-------------|-------------|---------------|----------|-----------|
|121        |Adam      |Fripp    |AFRIPP|650.123.2234|2005-04-10 00:00:00.000|ST_MAN|8200  |              |100       |50           |10           |Administration |200       |1700       |
|121        |Adam      |Fripp    |AFRIPP|650.123.2234|2005-04-10 00:00:00.000|ST_MAN|8200  |              |100       |50           |50           |Shipping       |121       |1500       |
|121        |Adam      |Fripp    |AFRIPP|650.123.2234|2005-04-10 00:00:00.000|ST_MAN|8200  |              |100       |50           |40           |Human Resources|203       |2400       |
|121        |Adam      |Fripp    |AFRIPP|650.123.2234|2005-04-10 00:00:00.000|ST_MAN|8200  |              |100       |50           |30           |Purchasing     |114       |1700       |
|121        |Adam      |Fripp    |AFRIPP|650.123.2234|2005-04-10 00:00:00.000|ST_MAN|8200  |              |100       |50           |20           |Marketing      |201       |1800       |

Result 2

|COUNT(*)|
|--------|
|2889    |

Snowflake

Query

OUT -> Oracle_01.sql

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

Result 1

|EMPLOYEE_ID|FIRST_NAME|LAST_NAME|EMAIL |PHONE_NUMBER|HIRE_DATE |JOB_ID|SALARY |COMMISSION_PCT|MANAGER_ID|DEPARTMENT_ID|DEPARTMENT_ID|DEPARTMENT_NAME|MANAGER_ID|LOCATION_ID|
|-----------|----------|---------|------|------------|----------|------|-------|--------------|----------|-------------|-------------|---------------|----------|-----------|
|121        |Adam      |Fripp    |AFRIPP|650.123.2234|2005-04-10|ST_MAN|8200.00|              |100       |50           |40           |Human Resources|203       |2400       |
|121        |Adam      |Fripp    |AFRIPP|650.123.2234|2005-04-10|ST_MAN|8200.00|              |100       |50           |20           |Marketing      |201       |1800       |
|121        |Adam      |Fripp    |AFRIPP|650.123.2234|2005-04-10|ST_MAN|8200.00|              |100       |50           |10           |Administration |200       |1700       |
|121        |Adam      |Fripp    |AFRIPP|650.123.2234|2005-04-10|ST_MAN|8200.00|              |100       |50           |50           |Shipping       |121       |1500       |
|121        |Adam      |Fripp    |AFRIPP|650.123.2234|2005-04-10|ST_MAN|8200.00|              |100       |50           |30           |Purchasing     |114       |1700       |

Result 2

|COUNT(*)|
|--------|
|2889    |

Cross Join Syntax

Oracle

Query

IN -> Oracle_02.sql

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

Result 1

|EMPLOYEE_ID|FIRST_NAME|LAST_NAME|EMAIL |PHONE_NUMBER|HIRE_DATE              |JOB_ID|SALARY|COMMISSION_PCT|MANAGER_ID|DEPARTMENT_ID|DEPARTMENT_ID|DEPARTMENT_NAME|MANAGER_ID|LOCATION_ID|
|-----------|----------|---------|------|------------|-----------------------|------|------|--------------|----------|-------------|-------------|---------------|----------|-----------|
|121        |Adam      |Fripp    |AFRIPP|650.123.2234|2005-04-10 00:00:00.000|ST_MAN|8200  |              |100       |50           |10           |Administration |200       |1700       |
|121        |Adam      |Fripp    |AFRIPP|650.123.2234|2005-04-10 00:00:00.000|ST_MAN|8200  |              |100       |50           |50           |Shipping       |121       |1500       |
|121        |Adam      |Fripp    |AFRIPP|650.123.2234|2005-04-10 00:00:00.000|ST_MAN|8200  |              |100       |50           |40           |Human Resources|203       |2400       |
|121        |Adam      |Fripp    |AFRIPP|650.123.2234|2005-04-10 00:00:00.000|ST_MAN|8200  |              |100       |50           |30           |Purchasing     |114       |1700       |
|121        |Adam      |Fripp    |AFRIPP|650.123.2234|2005-04-10 00:00:00.000|ST_MAN|8200  |              |100       |50           |20           |Marketing      |201       |1800       |

Result 2

|COUNT(*)|
|--------|
|2889    |

Snowflake

Query

OUT -> Oracle_02.sql

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