CROSS JOIN

 The CROSS JOIN in Oracle (and in SQL in general) is used to combine every row from one table with every row from another table. The result is a Cartesian product of the two tables. Each row of the first table is combined with each row of the second table. It produces a result set with all possible combinations of the rows from both tables.

1. Syntax

SELECT column_list

FROM table1

CROSS JOIN table2;

Or alternatively:

SELECT column_list

FROM table1, table2;

Here:

• table1 and table2 are the tables you want to join.
• column_list is a list of columns you want to select from the result set.

2. How CROSS JOIN Works

When performing a CROSS JOIN:

• If Table 1 has m rows and Table 2 has n rows, the result will be a result set with m * n rows.
• The CROSS JOIN does not require any condition to join the tables (no ON clause, as in an INNER JOIN or LEFT JOIN).

Example:

Consider the following two tables:

• Products table:

Product_ID	Product_Name
1	Apple
2	Banana

• Colors table:

Color_ID	Color_Name
1	Red
2	Green

If you perform the CROSS JOIN between these two tables:

SELECT p.Product_Name, c.Color_Name

FROM Products p

CROSS JOIN Colors c;

The result will be a Cartesian product:

Product_Name	Color_Name
Apple	Red
Apple	Green
Banana	Red
Banana	Green

This creates a total of 2 * 2 = 4 combinations.

3. Key Points to Remember

·        Cartesian Product: The result set will always be the Cartesian product of the two tables. This means that the number of rows in the result will be the product of the row counts of the tables involved.

·        No Conditions: Unlike other types of joins (like INNER JOIN or LEFT JOIN), the CROSS JOIN does not require any join condition or relationship between the tables.

·        Performance Considerations: Because CROSS JOIN returns a Cartesian product, if you are working with large tables, the result set can grow exponentially. For example, joining a table with 100 rows to a table with 1000 rows will result in 100,000 rows, which can cause performance issues if not used carefully.

·        Usage Scenario: CROSS JOIN is often used for generating test data, combining values, or scenarios where each row needs to be combined with each row from another set. It can be useful when you need to get all combinations of rows, such as creating all pairs of products and customers, or generating all permutations of sets.

4. CROSS JOIN vs. INNER JOIN

While both CROSS JOIN and INNER JOIN work by combining rows from two tables, they differ significantly:

• INNER JOIN: Combines rows based on matching values in a column (requires an ON condition).
• CROSS JOIN: Combines every row from one table with every row from another table, regardless of any column values.

The result of an INNER JOIN is always smaller or equal to the result of a CROSS JOIN, as it depends on matching values.

5. Practical Example

Let’s consider two tables for a simple sales analysis.

• Orders table:

Order_ID	Customer_ID	Order_Date
1	101	2025-01-10
2	102	2025-01-11

• Products table:

Product_ID	Product_Name
201	Laptop
202	Smartphone

A CROSS JOIN between these two tables would look like this:

SELECT o.Order_ID, o.Customer_ID, o.Order_Date, p.Product_Name

FROM Orders o

CROSS JOIN Products p;

The result would be:

Order_ID	Customer_ID	Order_Date	Product_Name
1	101	2025-01-10	Laptop
1	101	2025-01-10	Smartphone
2	102	2025-01-11	Laptop
2	102	2025-01-11	Smartphone

This generates every possible combination of orders and products.

6. Performance Considerations

While CROSS JOIN is useful, it can be inefficient with large tables since it creates a large result set. For instance, if one table has 1,000 rows and another has 1,000 rows, the result will contain 1,000,000 rows. Always ensure that the number of rows being produced is manageable.

If the query is too large, consider:

• Limiting rows with WHERE or FETCH FIRST.
• Using it sparingly when the result set is expected to be large.

Conclusion

CROSS JOIN in Oracle (and SQL) can be a powerful tool when you need to combine every row from one table with every row from another. However, it's important to use it judiciously, as it can produce large result sets that are resource-intensive.