Cross Join in SQL

SQL

A Cross Join, also known as a Cartesian Join, produces a result set that is the product of two tables. Unlike other joins, it doesn’t consider any condition for the combination; instead, it forms all possible combinations of rows between the tables involved.

Syntax of Cross Join

The syntax for a Cross Join is straightforward:

SELECT * FROM table1
CROSS JOIN table2;

FROM table1: Specifies the first table involved in the cross join operation. Replace table1 with the actual name of your first table.
CROSS JOIN: This clause is where the magic happens. It instructs the database to combine every row from table1 with every row from table2. The result is a Cartesian product, meaning every row from the first table is paired with every row from the second table.
table2: Specifies the second table involved in the cross join operation. Replace table2 with the actual name of your second table

Let’s set up a simple database to work with throughout this tutorial.

Employees Table:

EmployeeID	EmployeeName	Department
1	John Doe	IT
2	Jane Smith	HR
3	Bob Johnson	Marketing

Projects Table:

ProjectID	ProjectName
101	Database System
102	Web Development
103	Marketing Campaign

Example 1: Simple CROSS JOIN

Let’s begin with a basic example using our Employees and Projects tables:

SELECT * FROM Employees CROSS JOIN Projects;

Output:

EmployeeID	EmployeeName	Department	ProjectID	ProjectName
1	John Doe	IT	101	Database System
1	John Doe	IT	102	Web Development
1	John Doe	IT	103	Marketing Campaign
2	Jane Smith	HR	101	Database System
2	Jane Smith	HR	102	Web Development
2	Jane Smith	HR	103	Marketing Campaign
3	Bob Johnson	Marketing	101	Database System
3	Bob Johnson	Marketing	102	Web Development
3	Bob Johnson	Marketing	103	Marketing Campaign

Example 2: Adding WHERE Clause

We can further refine our results by adding a WHERE clause to filter the combinations. Let’s find employees working on the ‘Web Development’ project:

SELECT * FROM Employees CROSS JOIN Projects WHERE Employees.Department = 'IT' AND Projects.ProjectName = 'Web Development';

Output:

EmployeeID	EmployeeName	Department	ProjectID	ProjectName
1	John Doe	IT	102	Web Development

Example 3: Combining CROSS JOIN with Other Joins

We can use CROSS JOIN in combination with other join types to create more complex queries. Let’s combine it with an INNER JOIN to find employees assigned to projects:

SELECT * FROM Employees CROSS JOIN Projects INNER JOIN Assignments ON Employees.EmployeeID = Assignments.EmployeeID AND Projects.ProjectID = Assignments.ProjectID;

Output:

EmployeeID	EmployeeName	Department	ProjectID	ProjectName	AssignmentID
1	John Doe	IT	101	Database System	1
2	Jane Smith	HR	102	Web Development	2

This query combines the results of CROSS JOIN with an INNER JOIN, filtering only the rows where there is a matching assignment.

Example 4: Using CROSS JOIN for Cartesian Product

Expanding on the basic concept, let’s create a Cartesian product of three tables - Employees, Projects, and Departments:

SELECT * FROM Employees CROSS JOIN Projects CROSS JOIN Departments;

Output:

EmployeeID	EmployeeName	Department	ProjectID	ProjectName	DepartmentID	DepartmentName
1	John Doe	IT	101	Database System	1	IT
1	John Doe	IT	101	Database System	2	HR
1	John Doe	IT	101	Database System	3	Marketing
…	…	…	…	…	…	…

This query creates a Cartesian product of Employees, Projects, and Departments, resulting in every possible combination.

Example 5: CROSS JOIN with Aggregation

We can also use CROSS JOIN in conjunction with aggregation functions. Let’s find the total number of projects each employee could be assigned to:

SELECT Employees.EmployeeID, Employees.EmployeeName, COUNT(Projects.ProjectID) AS TotalProjects
FROM Employees
CROSS JOIN Projects
GROUP BY Employees.EmployeeID, Employees.EmployeeName:

Output:

EmployeeID	EmployeeName	TotalProjects
1	John Doe	9
2	Jane Smith	9
3	Bob Johnson	9

This query counts the total number of projects each employee could be assigned to using CROSS JOIN and grouping.