We can use Cross Join with WHERE clause and Without WHERE clause, When we use the Cross Join without WHERE clause,produces the Cartesian results from the tables. The resultset contains records that are multiplication of record number from both the tables.
see the Following example:
Suppose we have two tables:
tbl_Employee
Employee Id | EmpName | Age | Salary | Department |
1 | Ankur | 28 | 1200 | Sales |
2 | David | 32 | 3400 | Sales |
3 | John | 30 | 1500 | HR |
4 | James | 45 | 1200 | Production |
5 | Mohan | 35 | 4500 | Production |
6 | Ram | 40 | 2300 | HR |
7 | McDonald | 30 | 2600 | HR |
8 | Rohan | 23 | 1600 | Salse |
tbl_EmployeeDetails
Employee Id | Adres |
1 | Noida |
5 | Delhi |
See the query:
SELECT dbo.tbl_Employee.EmployeeId, dbo.tbl_Employee.EmpName, dbo.tbl_EmployeeDetails.Addres FROM dbo.tbl_Employee CROSS JOIN dbo.tbl_EmployeeDetails
The result set contains 16 rows (tbl_employee has 8 rows and tbl_EmployeeDetails has 2; 8 multiplied by 2 equals 16).
See the following picture:
However, if a WHERE clause is added, the cross join behaves as an inner join. For example, the following Transact-SQL queries produce the same result set.
SELECT tbl_Employee.EmployeeId, tbl_Employee.EmpName, tbl_EmployeeDetails.Addres FROM tbl_Employee INNER JOIN tbl_EmployeeDetails ON tbl_Employee.EmployeeId = tbl_EmployeeDetails.EmployeeId
— Or
SELECT tbl_Employee.EmployeeId, tbl_Employee.EmpName, tbl_EmployeeDetails.Addres FROM tbl_Employee CROSS JOIN tbl_EmployeeDetails WHERE tbl_Employee.EmployeeId = tbl_EmployeeDetails.EmployeeId
Result would be:
Employee Id | EmpName | Adres |
1 | Ankur | Noida |
5 | David | Delhi |