What is Cross Join and in which scenario do we use Cross Join?

CROSS JOIN: Cross join is used to return all records where each row from first table is combined with each row in second table.

Cross join is also called as Cartesian Product join.

The cross join does not apply any predicate to filter records from the joined table. Programmers can further filter the results of a cross join by using a WHERE clause.

For Example:- We have following two tables.

Look at the "Product" table:

Note that the "P_Id" column is the primary key in the "Product" table.

Next, we have the "SubProduct" table:

Note that the "Sub_Id" column is the primary key in the "SubProduct" table.

There are lots of scenarios where we use the cross join(permutation and combination), below are the example of hotel where customer's gets the detail of combined product and its total cost, So that it is easy to select their respective choice.

Query:- select
Product.ProductName,SubProduct.SubProductName,(Product.Cost+SubProduct.Amount)as
TotalCost from Product cross join SubProduct

The output look like below: