SQL Cross Join

SQL CROSS JOIN is defined as combining each row of the first table with each row of the second table.

CROSS JOIN is also called as Cartesian JOIN

The CROSS JOIN can be written in two ways:

  • Using JOIN
  • The table in the FROM clause without using WHERE condition

 

The syntax for CROSS JOIN is given as

SELECT * FROM table1 CROSS JOIN table2;

                                               Or

SELECT * FROM table1, table2;

 

Example

1.Employee table

Id Employee_Name Empl_Age
1 Sushil 22
2 Bhargav 32
3 Mohanthy 26
4 Nithin 38

 


2.Payment Table

Payment_ID Employee_Id Amount
150 1 2500
151 2 5000
152 4 6500

 

Now by applying join

SELECT *
FROM
Employee CROSS JOIN Payment;

 

The result will be displayed as

Id Employee_Name Empl_Age Payment_ID Employee_Id Amount
1 Sushil 22 150 1 2500
2 Bhargav 32 150 1 2500
3 Mohanthy 26 150 1 2500
4 Nithin 38 150 1 2500
1 Sushil 22 151 2 5000
2 Bhargav 32 151 2 5000
3 Mohanthy 26 151 2 5000
4 Nithin 38 151 2 5000
1 Sushil 22 152 4 6500
2 Bhargav 32 152 4 6500
3 Mohanthy 26 152 4 6500
4 Nithin 38 152 4 6500