SQL CROSS JOIN will return all records where each row from the first table is combined with each row from the second table. Which also mean CROSS JOIN returns the Cartesian product of the sets of rows from the joined tables.

A CROSS JOIN can be specified in two ways: using the JOIN syntax or by listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria.

SQL CROSS JOIN syntax:

SELECT * FROM [TABLE 1] CROSS JOIN [TABLE 2]

OR

SELECT * FROM [TABLE 1], [TABLE 2]


EXAMPLE :

Let's try with 2 tables below:

Table 1: GameScores

PlayerNameDepartmentIdScores
Jason13000
Irene11500
Jane21000
David22500
Paul32000
James32000

Table 2: Departments

DepartmentIdDepartmentName
1IT
2Marketing
3HR

SQL statement :

SELECT* FROM GameScores CROSS JOIN Departments

Result:

PlayerNameDepartmentIdScoresDepartmentIdDepartmentName
Jason130001IT
Irene115001IT
Jane210001IT
David225001IT
Paul320001IT
James320001IT
Jason130002Marketing
Irene115002Marketing
Jane210002Marketing
David225002Marketing
Paul320002Marketing
James330002Marketing
Jason130003HR
Irene115003HR
Jane210003HR
David225003HR
Paul320003HR
James330003HR