1. Introduction

The Cartesian product (or cross join) in Oracle SQL is the result of joining every row from one table with every row from another table. This type of join is typically unfiltered, meaning there’s no condition to match rows between the two tables.

2. Key Characteristics of Cartesian Product

• Row Multiplication: The result contains all possible combinations of rows from the two tables.
  • If Table A has m rows and Table B has n rows, the Cartesian product will contain m×n rows.
• No Join Condition: A Cartesian product occurs when a JOIN is executed without a ON condition or when tables are simply listed in the FROM clause without any WHERE clause.
• Usage: It’s rarely used intentionally but can be useful in scenarios like testing or when combining unrelated datasets.

3. Syntax

Using CROSS JOIN:

SELECT *
FROM table1
CROSS JOIN table2;

Without a Join Condition:

SELECT *
FROM table1, table2;

4. Example

We’ll use following two tables to understand cartesian product.

Employee

+-------------+------------+-----------+---------------+
| EMPLOYEE_ID | FIRST_NAME | LAST_NAME | DEPARTMENT_ID |
+-------------+------------+-----------+---------------+
| 101         | John       | Doe       | 1             |
+-------------+------------+-----------+---------------+
| 102         | Jane       | Smith     | 2             |
+-------------+------------+-----------+---------------+
| 103         | Sam        | Brown     | 3             |
+-------------+------------+-----------+---------------+
| 104         | Sue        | Green     | 2             |
+-------------+------------+-----------+---------------+

Department

+---------------+-----------------+
| DEPARTMENT_ID | DEPARTMENT_NAME |
+---------------+-----------------+
| 1             | Human Resources |
+---------------+-----------------+
| 2             | Finance         |
+---------------+-----------------+
| 3             | IT              |
+---------------+-----------------+

Cartesian product query

select * from employee
cross join  department;

Output

+-------------+------------+-----------+---------------+-----------------+-----------------+
| EMPLOYEE_ID | FIRST_NAME | LAST_NAME | DEPARTMENT_ID | DEPARTMENT_ID_1 | DEPARTMENT_NAME |
+-------------+------------+-----------+---------------+-----------------+-----------------+
| 101         | John       | Doe       | 1             | 1               | Human Resources |
+-------------+------------+-----------+---------------+-----------------+-----------------+
| 102         | Jane       | Smith     | 2             | 1               | Human Resources |
+-------------+------------+-----------+---------------+-----------------+-----------------+
| 103         | Sam        | Brown     | 3             | 1               | Human Resources |
+-------------+------------+-----------+---------------+-----------------+-----------------+
| 104         | Sue        | Green     | 2             | 1               | Human Resources |
+-------------+------------+-----------+---------------+-----------------+-----------------+
| 101         | John       | Doe       | 1             | 2               | Finance         |
+-------------+------------+-----------+---------------+-----------------+-----------------+
| 102         | Jane       | Smith     | 2             | 2               | Finance         |
+-------------+------------+-----------+---------------+-----------------+-----------------+
| 103         | Sam        | Brown     | 3             | 2               | Finance         |
+-------------+------------+-----------+---------------+-----------------+-----------------+
| 104         | Sue        | Green     | 2             | 2               | Finance         |
+-------------+------------+-----------+---------------+-----------------+-----------------+
| 101         | John       | Doe       | 1             | 3               | IT              |
+-------------+------------+-----------+---------------+-----------------+-----------------+
| 102         | Jane       | Smith     | 2             | 3               | IT              |
+-------------+------------+-----------+---------------+-----------------+-----------------+
| 103         | Sam        | Brown     | 3             | 3               | IT              |
+-------------+------------+-----------+---------------+-----------------+-----------------+
| 104         | Sue        | Green     | 2             | 3               | IT              |
+-------------+------------+-----------+---------------+-----------------+-----------------+

A Cartesian product can occur accidentally if you use INNER JOIN or OUTER JOIN but forget the ON condition.