Answer:
 

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:

P_Id	ProductName	Cost
1	SimplePizza	100.00
2	CheezePizza	200.00
3	DoubleCheezePizza	300.00
4	ChickenPizza	250.00

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

Next, we have the "SubProduct" table:

Sub_Id	SubProductName	Cost
1	Cold Drink	100.00
2	Bread	15.00

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:

ProductName	SubProductName	TotalCost
SimplePizza	Coldrink	200.00
CheezePizza	Coldrink	300.00
DoubleCheezePizza	Coldrink	400.00
ChickenPizza	Coldrink	350.00
SimplePizza	Bread	115.00
CheezePizza	Bread	215.00
DoubleCheezePizza	Bread	315.00
ChickenPizza	Bread	265.00

Regards,

Please click here to see more .NET and SQL Server interview questions