Using Cross Joins

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:

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


Employee Id Adres
1 Noida
5 Delhi

See the query:

SELECT     dbo.tbl_Employee.EmployeeId, dbo.tbl_Employee.EmpName,
FROM         dbo.tbl_Employee CROSS JOIN

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:
Cross Join without WHERE clause between sql tables

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, 
FROM  tbl_Employee INNER JOIN tbl_EmployeeDetails 
ON tbl_Employee.EmployeeId = tbl_EmployeeDetails.EmployeeId

— Or

SELECT  tbl_Employee.EmployeeId, tbl_Employee.EmpName,
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