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

Employee IdEmpNameAgeSalaryDepartment
1Ankur281200Sales
2David323400Sales
3John301500HR
4James451200Production
5Mohan354500Production
6Ram402300HR
7McDonald302600HR
8Rohan231600Salse

 
tbl_EmployeeDetails

Employee IdAdres
1Noida
5Delhi

 
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:
 
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, 
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 IdEmpNameAdres
1AnkurNoida
5DavidDelhi