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:
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
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: