A CROSS JOIN returns the Cartesian product of two tables.

  • That means every row from the first table is combined with every row from the second table.
  • Use cases: generating combinations, testing, or creating reference datasets.
  • Unlike other joins, no ON condition is used.

Basic syntax:

SELECT columns
FROM table1
CROSS JOIN table2;

Step 1: Example tables

Employees Table

EmployeeID Name
1 John
2 Jane

Departments Table

DepartmentID DepartmentName
101 IT
102 HR

Step 2: Simple CROSS JOIN

SELECT e.Name AS Employee, d.DepartmentName AS Department
FROM Employees AS e
CROSS JOIN Departments AS d;

Result: (2 employees × 2 departments = 4 rows)

Employee Department
John IT
John HR
Jane IT
Jane HR

Every employee is paired with every department.

Step 3: CROSS JOIN with table aliases

SELECT e.Name, d.DepartmentName
FROM Employees e
CROSS JOIN Departments d;
  • e → Employees
  • d → Departments

Result: Same as Step 3.

Aliases make queries cleaner, especially with larger tables.

Step 4: CROSS JOIN with WHERE clause

You can filter the results after the join:

SELECT e.Name, d.DepartmentName
FROM Employees e
CROSS JOIN Departments d
WHERE d.DepartmentName = 'IT';

Result: Only rows where department is IT

Name DepartmentName
John IT
Jane IT

Note: Using a WHERE clause after a CROSS JOIN acts like a filter, but the CROSS JOIN itself still generates the Cartesian product first.

Categorized in:

SQL Server,