Cross Join

The CROSS JOIN or CARTESIAN JOIN returns the Cartesian product of the sets of records from two or more joined tables. CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table. The result is called as Cartesian Product. If WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.

/* Example SQL Code */
CREATE TABLE IF NOT EXISTS Departments
(
 DepartmentId Integer Primary Key AutoIncrement
,DepartmentName TEXT
);

INSERT INTO Departments(DepartmentName) VALUES('Administration');
INSERT INTO Departments(DepartmentName) VALUES('Sales & Marketing');
INSERT INTO Departments(DepartmentName) VALUES('HR');
INSERT INTO Departments(DepartmentName) VALUES('Accounts');

DROP TABLE IF EXISTS EmployeeMaster;
CREATE TABLE IF NOT EXISTS EmployeeMaster
(
EmployeeId Integer Primary Key AutoIncrement,
FirstName Text,
LastName Text,
DepartmentId Integer,
ReportsTo Integer
);

INSERT INTO EmployeeMaster values (1,'John','Dekota', 2,7);
INSERT INTO EmployeeMaster values (2,'Mary','Denware',3,2);
INSERT INTO EmployeeMaster values (3,'Scott','Hamilton',2,7);
INSERT INTO EmployeeMaster values (4,'Guru','Manna',3,2);
INSERT INTO EmployeeMaster values (5,'Robert','Olipo',1,3);
INSERT INTO EmployeeMaster values (6,'Mark','Netcal',2,7);
INSERT INTO EmployeeMaster values (7,'Peter','adam',3,2);
INSERT INTO EmployeeMaster values (8,'Martin','Jerico',2,7);
INSERT INTO EmployeeMaster values (9,'Davis','Ocee',1,3);

Here is an example of cross join in SQL between two tables defined above.

SELECT DepartmentName
, EmployeeId as [EmployeeId]
, FirstName, LastName 
FROM Departments CROSS JOIN EmployeeMaster;