SQL Cross Join

 A cross join is a join operation that produces the Cartesian product of two or more tables.

In Math, a Cartesian product is a mathematical operation that returns a product set of multiple sets.

For example, with two sets A {x,y,z} and B {1,2,3}, the Cartesian product of A x B is the set of all ordered pairs (x,1), (x,2), (x,3), (y,1) (y,2), (y,3), (z,1), (z,2), (z,3).

Suppose the A table has n rows and the B table has m rows, the result of the cross join of the A and B tables have n x m rows.

Syntax:

The syntax of the CROSS JOIN in SQL will look like the below syntax:

SELECT ColumnName_1,ColumnName_2,ColumnName_N

FROM [Table_1] CROSS JOIN [Table_2]

Or we can use the following syntax instead of the previous one. This syntax does not include the CROSS JOIN keyword; only we will place the tables that will be joined after the FROM clause and separated with a comma.

SELECT ColumnName_1,ColumnName_2,ColumnName_N FROM [Table_1],[Table_2]

The result set does not change for either of these syntaxes. In addition, we must notice one point about the CROSS JOIN. Unlike the INNER JOIN, LEFT JOIN and FULL OUTER JOIN, the CROSS JOIN does not require a joining condition

Example:

Let's consider the below table.

CREATE TABLE Meals(MealName VARCHAR(100)) CREATE TABLE Menus(MenuName VARCHAR(100))

INSERT INTO Menus VALUES('Chicken Tikka'), ('Egg Omlet'), ('Chicken Noodles')

INSERT INTO Meals VALUES('Non Veg'), ('Starter'), ('Fast Food')

SQL Code:

SELECT * from Meals CROSS JOIN Menus

Output:

MealName	MenuName
Non Veg	Chicken Tikka
Starter	Chicken Tikka
Fast Food	Chicken Tikka
Non Veg	Egg Omlet
Starter	Egg Omlet
Fast Food	Egg Omlet
Non Veg	Chicken Noodles
Starter	Chicken Noodles
Fast Food	Chicken Noodles

Please visit related article below:

• INNER JOIN
• LEFT OUTER JOIN: 
• RIGHT OUTER JOIN: 
• FULL OUTER JOIN: 
• Self Join
• CROSS JOIN